EXCEL USTASI

Access Tablolar Arası İlişkiler Detaylı Anlatımı #7

Access Eğitimi

Access Eğitim serimizin bu bölümde birden fazla tablodaki verileri tek bir tabloda nasıl birleştireceğimizi diğer bir ifade ile Tablolar Arası İlişkiler konusunu işleyeceğiz. İlk bölümde Giyim Mağazası senaryomuz vardı ve burada 4 tablodan bahsetmiştik. Bu bölümde Müşteri ve Satış tablolarında nasıl ilişkilendireceğimizi anlatmaya çalışacağım.

 

Access Tablo Arası İlişkiler Yapısı

Anlatıma geçmeden önce bu tabloları neden ekledik. Ne işe yarayacaklar, Access formundaki yeri neresidir önce ondan bahsedelim. Çünkü önce projedeki işi anlamamız yukardaki ilişkileri daha kolay kavramamızı sağlayacaktır.

Projedeki Tabloları Oluşturma

Müşteri Tablosu

İlk Bölümlerde Müşteri kartı işlemiştik. Mağazaya gelen Müşterilerin kaydının tutulduğu bölümdür.  Adı, Soyadı, Telefon, Adres ve Kayıt edilen tarihi Otomatik kaydettiğimiz tablodur.

Projedeki Müşteri Tablosu

Adı Soyadı

Müşterinin Adı Soyadı. Tabloda ayrı kolonlara eklendi. Ancak ilerde istersek bu iki alanı Sorgu bölümünde Adı&” ”&Soyad formülü ile birleştirerek kullanacağız.

Telefon Numarası

Müşterinin Telefon Numarası. Borç durumu için aramak veya ilerle Programı geliştirip Toplu SMS gibi işlemler için kayıt oluşturmamı sağlar.

Müşteri Yaşı

Müşterin Yaşını öğrenip satın alan müşteri yaş profilini öğrenip ona göre analiz yapıp alacağım ürünleri bu yaş gruplarına göre karar vermemi sağlayabilirim.

Sadık Müşteri mi?

Müşteri davranışları, ödeme düzeni gibi sizler için favori müşteri tespit ettiğiniz müşterilere bu alanı seçebiliriz. İlerde böyle müşteriler için indirimler jestler hediyeler gibi işlemler yapmak için kullanabilirsiniz.

Adres

Müşteri Adresini alarak Borcu olduğunda hukuki adresine Noter kanalı ile ihbar göndermek için, ya da Özel günlerde adresine toplu olarak kampanya ya da indirimlerde mektup göndermek için kullanabilirsiniz.

Kayıt Tarihi

Müşteri ne zaman kaydedildiyse o tarihi otomatik kaydetmek için kullanacağız. Böylelikle müşterinin ilk ne zaman kaydettiğimizi tespit etmiş olacağız.

Sonuç olarak Müşteri kayıtlarını Form olarak aşağıdaki şekilde oluşturmayı planlıyoruz.

Müşteri Kartı

Müşteri kartının altındaki alanlar ise başka tablolardan çekmektedir.

Satış Tablosu

Müşteri mağazadan ürün alıp kasaya geldiğinde yapılan işlemde SATIS tablosuna kaydedecektir.

Satış Tablosu

Form tasarlamayı planladığımız Ürün Satış formu doldurulduğunda TABLO_SATISISLEMI tablosuna kaydedecektir.

FK_MUSTERI_ID

Bu alan Müşteri tablosundaki veriler ile bağlantı kuracağımız alandır. Bu alan sayesinde projemizde kolaylıklar sağlayacağız. Bu bölümde zaten bu konuya değineceğiz.

AÇIKLAMA

Mağaza sahibi stoklu bir proje istemediği için satılan ürün ile ilgili veya satış işlemi açıklamasını bu alana kaydedilmek üzere oluşturdum.

ÜRÜN TÜR

Her ne kadar açıklama alanında ürün bilgisi yazılsa da mağaza da kategorik şekilde ürün grupları vardır. Kaydetme esnasında kategorik olarak kaydedildiğinde ilerde raporlarlarken kolaylıklar sağlayacaktır.

İŞLEM TÜR

Bu kısım müşterinin borç mu alacaklı mı veya kredi kartı ile mi ödeme yaptı tespit edilen alandır. Form kısmında bu alanlar üzerinde kontroller yapacağız.

ÖDEME TARİHİ

Eğer müşteri birkaç taksit istediyse bu alana ileri tarihli borç açabilmek için kullanılmaktadır. Normal durumlarda işlem tarihi ile aynı işlevi görecektir.

TUTAR

Borçlandırılan alanlar -100 gibi eksi değeri alarak kaydedilir. Kredi Kartı ve Nakit işlemlerde ise artı olarak 100 şeklinde kaydedilir. Hesaplama da artı eksi değerlerinin toplamı şeklinde gösterilir.

İŞLEM TARİHİ

İşlem yapılan anı =Now() fonksiyonundan faydalanarak kayıt ekleme anının zamanını kaydetmektedir. Her tabloda böyle bir alan olmalı. Verinin ne zaman eklendiği yanlış işlem gibi problemli durumlarda o anın verisi her zaman işinize yarayacaktır.

Tablodaki Verilerin Birbirleri ile İlişkiler

Yukarda bahsettiğimiz 2 tablo ile aşağıdaki şekilde bir database yapısı çıkmaktadır. Yapılacak işi anladığımıza göre burada yabancı gelen kavramlar üzerinde biraz duralım.

MUSTERI tablosunda 1 ve çizgi giderek SATIS tablosunun FK_MUSTERI alanına gitmiş ve yanında ∞  işareti görülüyor. Bunun anlamı Müşteri bilgilerinin ID (kayıt) numarasından 1 tane kayıt olabilir.  Buna karşılık gelen SATIS tablosundaki FK_MUSTERI alanında aynı kayıt numarasından birden fazla olabilir. Diğer bir ifade ile SATIŞ tablosuna sonsuza kadar kayıt eklenebilir demektir.

Şimdi bunu bir de senaryo üzerinde anlatalım. Müşteri tablosuna aynı müşteriyi iki defa yani birden fazla kaydetmeyiz bu sebeple Müşteri tablosunda bir müşteri için bir tane kayıt açılmalıdır mantık olarak.

Müşteri bir kere kaydedildi. Bir tane kaydedilen müşteri mağazadan istediği kadar ürün alabilir. O zaman FK_MUSTERI alanında birden fazla aynı numara kaydedilebilir.

 

Müşteri ve Satış Tablosu İlişkiler

Bu kısımla işin mantık kısmına biraz girdik.  Ama hala net olmadığının farkındayım. Biraz sabır. Adım adım mantığı anlatıyorum.

Yukarda bahsettiğim bir kayıt çok kayıt gibi kavramları gelin veriler üzerinde görelim.

TABLO_MUSTERI Tablosundaki Sıra No (ID) alanı

Müşteri kaydı olduğu anda atama numaradır. Müşteri kaydettiği hareketinin kayıt numarasıdır.

TABLO_MUSTERI Tablosu Sıra No ile TABLO_SATIS Tablosu FK_MUSTERI İlişkileri

Örneğin 47. Sırada olan Ahmet GÜNDÜZ müşterisinin bilgileri TABLO_MÜŞTERİ tablosunda tutulmaktadır. TABLO_SATIŞ tablosunda ise o müşteriye ait para hareketleri görünmektedir. BORÇ olanlar ürün aldığı, Nakit alanlar ise Ödemeler olmak üzere 6 adet para hareketi olmuştur.

Bu kaydın ilişkisini FK_MUSTERI alanında Ahmet Gündüz’ün sıra numarası ile aynı olduğu için tablolar birbirleri ile eşleşmiş oluyor.

Müşteri ve Satış Tablo İlişkiler Veri Görünümü

Tablolar Arası İlişkiler

TABLO_MUSTERI tablosunda 47 ile 60 arasında kayıt var. Bu bağlı olan TABLO_SATIS tablosundaki FK_MUSTERI alanı ile ilişkili kayıtlar var. Ancak TABLO_SATIS tablosunun son satırda 100 nolu kayıt TABLO_MUSTERI tablosunda kayıtlı değil. Yani herhangi bir satışa bağlı olmayan ortada bir kayıt görünüyor. Yani kime satıldığı belli olmayan kayıt diyebiliriz. Böyle bir durumda ise hazırladığımız program istediğimiz düzende çalışmamış olur. Birbirinden kopuk kayıtlar meydana gelir.

Bire Çok Tablo İlişkiler

Eğer Kod ile program yazıyor olsaydık. bu sorunu kontrol edebilecek bir mekanizma geliştirmemiz gerekiyordu. Ama sadece kod yeterli gelmez. Bu işlemi veritabanında da kontrollerini yapmak gerekiyor. Yani örnekte gösterdiğimiz 100 nolu kaydı gördüğü anda program yapısından dolayı ilişkili tabloda böyle bir kayıt yok diye hata vermesini sağlamalıyız. Diğer bir ifade ile böyle bir kayda asla izin vermemesi gerekiyor. Veri tabanı olarak ta bu sorunun önüne geçilecek yöntemler var.

Bu ilişkiyi kurmanın 2 yöntemi vardır.

I. Yöntem – Veritabanı Araçları – İlişkiler

Veritabanı Araçları sekmesinden İlişkileri seçiyoruz.

Access İlişkiler Menüsü

Karşımızda hangi tablolar ile ilişkilendireceksek o tabloları açmamızı istemektedir.

Access İlişkiler Menüsü Tabloyu Göster Ekranı

Yukarıda şekilde seçili olan bu bölümde çalıştığımız tabloları seçip Ekle diyoruz. Sonra da Kapat diyoruz.  (İlişkiler bölümündeyken Tablo Göster alanını seçerek te tablo Ekle çıkarma işlemi yapabiliriz.)

TABLO_MUSTERI’nın ID kolonundan sürüklemeye başlayarak TABLO_SATISI tablosundaki FK_MUSTERI tablosunda doğru sürükleyip bırakıyoruz.

Access İlişkiler Ekranı

İlişkileri Düzenle

Access İlişkiler Düzenle

Tablolardaki alanları sürüklediğimizde yukardaki şekilde bir ekran gelecek. Bu kısım seçilen kolonlar arasında nasıl bir ilişki olacağını belirleyecektir.

Tablo sorgu

İlk seçimi TABLO_MUSTERI alanındaki ID ile başlayarak sürükleme işlemi yaptığım için Tablo/Sorgu alanında bu alanı göstermiştir.

İlişkili Tablo/Sorgu

İlk alanı seçip sürüklediğimde hangi kolon üzerinde bıraktıysam İlişkili Tablo/Sorgu alanında o tablo ve kolonunu göstermiştir.

Bilgi Tutarlılığına Zorla

Bu alanı seçtiğimde TABLO_SATIS tablosunda 100 nolu kayıt girdiğimizde bu kayda izin vermemiş olacağız. Zaten ilişkili tablo yapmamızın da amacı buydu. Bu sebeple bu alanın seçimi önemli.

İlişkili Alanları Art arda Güncelleştir

Örneğin bir müşterinin numarasını değiştirdiğimizde eğer başka bir tabloda da bunla ilişkili bir tablo varsa otomatik olarak onun numarası da değişecektir. Bu da bizim işimizi kolaylaştıracaktır. Bu avantajdan yararlanmak için de bu alanı da seçiyoruz.

İlişkili Alanları Art arda Sil

Örneğin müşterinin kaydını sildiğimizde yaptığı satış aldığı ve buna bağlı aldığı ürünlerin de silinmesini istediğimizde bu alanı seçeriz. Ancak bu örneğimiz de böyle bir işlem mantıksızdır. Bu sebeple bu alanı seçmiyoruz.

Birleştirme Türü Özellikleri

Access Birleştirme Özellikleri

Birleştirme özellikleri alanında 3 çeşit seçenek görülmektedir. Bu seçenekler veri tabanı dilinde 1’e çok, Çok’a Çok ilişkiler olarak adlandırırız. Bu açıklamadan anlaşılacağı üzere seçeneğe göre bazı tablonun tüm değerleri diğer tablonun birleşeni gibi seçeneğe göre verilerin getirmesini sağlarız. Örneğimizi yaparken MÜŞTERİ alanından sürüklemeye başladığım için MÜŞTERİ KARTI 1 kayıtdır. Hedef olarak ta SATIŞ tablosundaki FK_MUSTERI alanında sonlandırdığımda ise çok kayıt işareti olan ∞ (sonsuz) işareti ile temsil etmekteyiz.

Bunun anlamı ise MÜŞTERİ KARTI 1 tane kayıt getir. SATIŞ İŞLEMİ tablosunda ise çok tane kayıt getir demiş oluyoruz.

Senaryomuzdan yola çıkarsak bir tane müşteri birden fazla kez satış işlemi yapabilir demiş oluyoruz.

Seçenekler

1: Yalnızca, her iki tablodan birleştirilmiş alanların eşit olduğu satırları içer.

2: ‘TABLO_MUSTERI’ içindeki TÜM kayıtları ve ‘TABLO_SATIS’ içinde ise yalnızca birleşen alanların eşit olduğu kayıtları içer.

3: ‘TABLO_SATIS’ içindeki TÜM kayıtları ve ‘TABLO_MUSTERI’ içinden ise yalnızca birleşen alanların eşit olduğu kayıtları içer.

 

Örneğimizde devam edecek olursak ilişkilendirme sonunda aşağıdaki gibi görsele sahip olacağız.

Not : Bu ilişkileri yapmadan önce SATIŞ tablosunda örnek olarak kullandığımız 100 nolu kaydı silmeniz gerekiyor. Aksi takdire ilişki kuramadığı için hata verecektir.

Access Tablo İlişkiler

İlişkileri belirledik artık İlişkiler bu alanı kapatabiliriz.

II. Yöntem – Sihir İle Sorgu Üreterek

İlişki tablo kullanımdaki anlatacağımız bu yöntem daha kolay ve kullanışlı yöntemdir. Genelde bu yöntemi kullanacağız. Bu yöntem de önceki gibi ilişkileri göz önünde bulunduruyor. İlaveten görsel olarak ta ilişkileri anlamamızı sağlıyor.

Önceki İlişkilendirmenin Bağlantısının Koparıyoruz

Bu işleme geçmeden önce ilişkilendirdiğimiz tabloların ilişkilerini kaldırıyoruz. Bunun için Veritabanı araçları – İlişkiler seçiyoruz.

Açılan ilişki ekranından aşağıdaki gibi ilişki çizgisinin üzerine gelip seçtikten sonra DELETE tuşuna basıyoruz.

Access Tablo İlişkiler Bağlantı Koparma

İlişkiler tablosu aşağıdaki şekilde ilişkiler kapatıldıktan sonra aşağıdaki şekilde olmalıdır.

Access Tablo İlişkiler Bağlantı Koparma Sonuç

İlişkiler dosyasını kapatabiliriz. Artık tabloların birbiri ile bağı kalmamıştır. Müşteri Numarasında olmayan bir kaydı Satış işlemi tablosunda veri girişi yapılmaktadır. Bu da iki tablo arası ilişki kurulmamış demektir.

Arama Sihirbazı İle FK_MUSTERI alanını ilişkilendirme

FK_MUSTERI kolonunun tablosu olan TABLO_SATIS tablosunu tasarım görünümünde açıyoruz. Satış Kaydı girerken TABLO_MUSTERI alanının ID alanındaki verilerden alıyorduk. Şimdi yine Müşteri tablosundaki sayı ile ilişkilendireceğim.

Bunun için TABLO_SATIS tablosunu tasarım görünümünde açıyoruz. FK_MUSTERI alanı daha önceden sayı veri tipindeydi. Bunun yerine Arama Sihirbazını seçiyoruz. Bu adımdan karşımıza 3-4 adımlık sorular gelecektir.

Bu yöntemde amacımız yine Müşteri Tablosundaki numara ile ilişki kurmaktır. Tek farkı ilişkiler üzerinden değilde her ilişki kuracağımız yerde Arama Sihirbazı ile verileri ilişkilendireceğiz.

Access Arama Sihirbazı İle Tablo İlişkiler Kurma

İlk çıkan adımda Arama alanı öğesinin değerleri başka bir tablo veya sorgudan almasını istiyorum alanını seçiyoruz.

Access Arama Sihirbazı İle Tablo İlişkiler Kurma 1. Adım

Hangi tablodan almak istediğinizi seçiyoruz. FK_MUSTERI kolonu TABLO_MUSTERI tablosunun ID alanı ile ilişkilendireceğimizden TABLO_MUSTERI tablosunu seçiyoruz. İleri diyoruz.

Access Arama Sihirbazı İle Tablo İlişkiler Kurma 2. Adım

Bu adımda ilişki sonrası hangi verileri görmek istediğimizi göstereceğiz. Ben kolonların tamamını görmek istiyorum. Siz isteğinize göre belirleyebilirsiniz. Tüm kolonlar için >> işaretini seçerek kolonların tamamını seçiyoruz.

Access Arama Sihirbazı İle Tablo İlişkiler Kurma 3. Adım

Kayıtları tabloda nasıl sıralanmasını istediğimi soruyor. ID alanına göre sıralı şekilde gelsin istiyorsunuz. Siz isterseniz örneğin Adı kısmına göre sıralı da seçebilirsiniz. ID alanını seçmemin sebebi hem sayısal hem de her kayıt eklendiğinde otomatik artan sayıda olması kayıt sırasına göre listelememi sağlıyor.

Access Arama Sihirbazı İle Tablo İlişkiler Kurma 4. Adım

Sonraki adımda TABLO_MUSTERI tablosunun listesi gelecektir. Anahtar sütunu gizle (önerilen) alanı seçili olarak geliyor. Ben bu seçeneği kaldırıyorum. Gerek yok bu numara da görünsün istiyorum. İleri diyoruz.

Access Arama Sihirbazı İle Tablo İlişkiler Kurma Anahtar Sütunu Belirleme

Bu adımda ise Satış işlemi yaparken MÜŞTERİ listesi çıkacak. Ama listede birçok alan var. Bu alanlardan hangisini kolondaki seçtiğimiz veriyi tabloya kaydetsin diye sormaktadır. Biz ilişkili tablo mantığına göre ID alanı olmalıdır. Tıpkı ilişkiler bölümünde ID alanından FK_MUSTERI alanına sürüklediğimiz gibi. Buranın ilişkisini de bu adımda yapıyoruz.

Access Arama Sihirbazı İle Tablo İlişkiler Kurulacak Alanı Belirleme

TABLO_MUSTERIKARTI tablosu ile veri bütünlüğünü etkinleştirmek isteyip istemediğimizi soruyor. Amacımız zaten ilişkili tablo birbiri ile bağlantılı olsun ki uyuşmayan veriler girişi olmasın bu sebeple Veri Bütünlüğünü Etkinleştir kutucuğunu ve Kısıtlamalı Silme bölümünü seçiyoruz.

Access Arama Sihirbazı İle Tablo İlişkiler Veri Bütünlüğünü Etkinleştir

Artık Sihirbazımız tamamlanmıştır. Son diyoruz.

Access Arama Sihirbazı Onay

İşlemi Kaydetmek için Evet diyoruz. Artık TABLO_MUSTERI tablosunun verileri TABLO_SATIS tablosundaki verilerle bağlıdır.

İşleme başlarken Sayı alanını Arama Sihirbazı olarak değiştirmiştik. Gene tekrar kontrol ettiğimizde sayı olarak kalmıştır.

Access Arama Sihirbazı Tablodaki Sonuç

Farklı görmek için alt kısımdaki Arama sekmesine geliyoruz.

Access Arama Sihirbazı İle Tablodaki Satır Kaynağı Sorgusu

Bu alan normal şartlarda boş iken yapmış olduğumuz Arama Sihirbazı ile iki kolonu birbiri ile ilişkilendirmiş olduk. Bunu Satır kaynağında yazan SQL kodu ile yapmaktadır.

İkinci Yöntemin Tablo İlişkilerini Test Edelim.

Şimdi test etmek için TABLO_SATIS tablosunu açıyoruz. Yeni kayıt girişi yaparken ilişkilendirdiğimiz alanda aşağı ok çıktığını göreceksiniz. Aşağı Ok’a bastığınızda TABLO_MUSTERI tablosundaki verilerin geldiğini göreceğiz.

Access Arama Sihirbazı İlişkiler Testi

Sadece MÜŞTERİ tablosunda sadece gösterilen yani ID alanından kayıt girebiliriz artık. Kaydettiği alan ise ID alanı olarak geçtiğimiz için en başta görülen sayısal alan kaydedilecektir.

Eğer yine MÜSTERİ tablosunda olmayan kaydı girdiğimizde ise aşağıdaki gibi hata verecektir.

Access Arama Sihirbazı Testi Sonucu

Amacımıza ikinci yöntemdeki ilişkilendirme ile de ulaşmış olduk. Genelde Tablo ilişkilendirmelerde ikinci yöntem kullanabilirsiniz.

Projedeki Diğer İlişkili Alanlar

Artık eğitim ilk başında çizdiğim tablo şeklini yavaş yavaş anlayabildiğinizi umuyorum.

Aşağıda Projemizin devamlılığı açısından tüm ilişkileri bahsettiğimiz ikinci yöntemle yapmanızı rica ediyorum. Bu bölümde sadece yeşil ile bahsedilen alanı yaptık. Diğer tüm ilişkileri kurunuz.

Arama Sihirbazı ile İlişkiler kurarken dikkat etmeniz gereken Sonsuz işaretinin olduğu tablodaki kolonu açıp 1 ile gösterilen alanı çağıracaksınız.

 

Ekli dosyada tüm tablolarda ilişkilendirilmiş şekilde hazılanmıştır. Ancak yeşil ile işaretli ilişkileri siz deneyebilirsiniz.  Toplamda yaptığımız örnek ile birlikte 3 adet Arama Sihirbazı işlemi yapılması gerekiyor.

Access Proje İlişkiler Tablo Yapısı Kalan Tablolar

 

Access Dosya Download Makalede kullandığımız Access Dosyasını buradan indirebilirsiniz.

Eğitime Excelde Access Tablo Veri Aktarımı Yapmak #8 makaleden devam edin.