"INNER JOIN" metodu ile sorgu

[ragnorak]

Excel'de Textbox'a URUN_KODU'nu yazıp Ek'deki Access dosyasından sorgu yapıp Ürün Ağırlığını bulmak istiyorum.
Yani Textbox'daki değere göre gidip "URUN_DOSYALARI" tablosundan "URUN_KODU"nu bulacak, "URUN_KODU"nun karşısında yazan "STOK_KODU"nu "HAMMADDE" tablosunda "STOK_KODU"nda eşleştirip "HAMMADDE" tablosundaki "AGIRLIK" ile "URUN_DOSYALARI" tablosundaki "ADET" ile çarpacak ve toplam ağırlığı verecek. "INNER JOIN" metodu ile yapıldığını biliyorum ama sorguyu bir türlü yazamadım.

Access Dosyası

 

[Haluk]

Merhaba;

Bende Access olmadığı için, eklediğiniz dosyadaki verileri Excel'e alıp bir çalışma yaptım ama sonra çalışmayı veriler Access'ten alınacak şekline çevirmeyi unuttum, sonra da üşendim açıkçası. Çünkü, alınan sonuçların doğruluğunu kontrol etmedim.

Siz dosyayı inceleyin..... eğer doğru sonuçlar geliyorsa, verileri Excel yerine Access dosyasından okuyacak şekle getirirsiniz. Zaten önemli olan SQL sorgusunun mantığıdır.


.

 

[ragnorak]

Fazlasıyla olmuş. Sonuçları da test ettim. Hatalı veri yok. Çok teşekkür ederim. "left join"i anlamadım neden "inner join" değil?

 

[Haluk]

Tam hatırlamıyorum ama, sanki VBA'de "inner join" kullanılmıyor gibi bir şey aklımda kalmış...

.

 

[ragnorak]

Onu da ben biraz araştırayım o zaman

 

[ragnorak]

Haluk hocam başka bir sorum daha olacak ek'li dosyada üç adet tablom var. Bu sefer Ürün ağırlığı değil Toplam fiyatı hesaplamak istiyorum. Sorun şu; yine "URUN_KODU"nun karşısında yazan "STOK_KODU"nu "HAMMADDE" tablosunda "STOK_KODU"nda eşleştirecek, "ISKONTO" tablosunda "FIRMA" alanından "STOK_KODU"nun karşısındaki "FIRMA"yı sorgulayıp "BIRIM_FIYATA" uygulayacak ve son olarakta "PARA_BIRIMI" DOLAR veya EURO ise bu değerleri de sayfadaki textbox'lardan alarak "TOPLAM SATIŞ FİYATINI" oluşturacak.

Call Ado_Baglan
Set rs = CreateObject("adodb.recordset")
Set rsx = CreateObject("adodb.recordset")
Set RSq = CreateObject("adodb.recordset")

sorgu = "Select * from [URUN_DOSYALARI] where [URUN_KODU] ='" & ListBox1.LIST(ListBox1.ListIndex, 0) & "'"
rs.Open sorgu, con, 1, 3

Do Until rs.EOF

sorgux = "Select [STOK_KODU],[FIRMA],[BIRIM_FIYAT],[PARA_BIRIMI] from [HAMMADDE] where [STOK_KODU] ='" & rs("STOK_KODU").Value & "'"
rsx.Open sorgux, con, 1, 3

sorguq = "Select [FIRMA],[ISKONTO_1],[ISKONTO_2],[ISKONTO_3],[ISKONTO_4] from [ISKONTO] where [FIRMA] ='" & rsx("FIRMA").Value & "'"
RSq.Open sorguq, con, 1, 3

X1 = "0"
X2 = "0"
X3 = "0"
X4 = "0"

Do Until RSq.EOF
X1 = Replace(RSq("ISKONTO_1").Value, "%", "")
X2 = Replace(RSq("ISKONTO_2").Value, "%", "")
X3 = Replace(RSq("ISKONTO_3").Value, "%", "")
X4 = Replace(RSq("ISKONTO_4").Value, "%", "")
RSq.MoveNext
Loop

If rsx.Fields("PARA_BIRIMI").Value = "$" Then
rs("MALIYET") = Format(rsx("BIRIM_FIYAT").Value * XED_FORM.DOLAR.Value _
* (1 - X1 / 100) * (1 - X2 / 100) * (1 - X3 / 100) * (1 - X4 / 100) * rs("ADET").Value, "currency")

ElseIf rsx.Fields("PARA_BIRIMI").Value = "€" Then
rs("MALIYET") = Format(rsx("BIRIM_FIYAT").Value * XED_FORM.EURO.Value _
* (1 - X1 / 100) * (1 - X2 / 100) * (1 - X3 / 100) * (1 - X4 / 100) * rs("ADET").Value, "currency")

Else
rs("MALIYET") = Format(rsx("BIRIM_FIYAT").Value * (1 - X1 / 100) * (1 - X2 / 100) _
* (1 - X3 / 100) * (1 - X4 / 100) * rs("ADET").Value, "currency")
End If

RSq.Close
rsx.Close

rs.Update
rs.MoveNext
Loop
rs.Close: sorgu = ""

'<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Sorgut = "SELECT SUM(MALIYET) FROM [URUN_DOSYALARI] where [URUN_KODU] ='" & ListBox1.LIST(ListBox1.ListIndex, 0) & "'"
rs.Open Sorgut, con, 1, 3

MALIYET.Value = rs.GetString
rs.Close: Sorgut = ""

sorgu = "select [URUN_KODU],[MALIYET],[KAR_PAYI],[ISKONTO],[EK_MALIYET],[SATIS_FIYATI],[USER]  from [URUN_DOSYALARI] where [URUN_KODU] ='" & ListBox1.LIST(ListBox1.ListIndex, 0) & "'"
rs.Open sorgu, con, 1, 3

Do Until rs.EOF
rs("MALIYET") = MALIYET.Value

X = Replace(rs("KAR_PAYI").Value, "%", "")
Y = Replace(rs("ISKONTO").Value, "%", "")
rs("SATIS_FIYATI").Value = Format(MALIYET.Value * (1 + X / 100) * (1 - Y / 100) + rs("EK_MALIYET").Value, "currency")

KAR_PAYI.Value = rs("KAR_PAYI").Value
ISKONTO.Value = rs("ISKONTO").Value
EK_MALIYET.Value = rs("EK_MALIYET").Value
SATIS_FIYATI.Value = Format(rs("SATIS_FIYATI"), "currency")

Label2 = rs("USER").Value
rs.Update
rs.MoveNext
Loop

rs.Close: con.Close
Set con = Nothing: Set rs = Nothing: sorgu = ""

Label1 = "İşlem Tamamlandı."

Ben yapabildiğim kadarıyla bu şekilde yazdım. Ama gördüğün gibi çok uzun oldu.
Ekli Dosya

 

[ragnorak]

sorgu = "Select sum([TOPLAM]) as [TOPLAM FIYAT] from (Select HAMMADDE.[STOK_KODU],HAMMADDE.[BIRIM_FIYAT],HAMMADDE.[FIRMA],URUN_DOSYALARI.[ADET],ISKONTO.[FIRMA], ISKONTO.[ISKONTO_1], HAMMADDE.[BIRIM_FIYAT]*URUN_DOSYALARI.[ADET]*ISKONTO.[ISKONTO_1] as [TOPLAM] from [HAMMADDE] as HAMMADDE" & "inner join [ISKONTO] as ISKONTO " & "on HAMMADDE.[FIRMA] = ISKONTO.[FIRMA] " & "inner join [URUN_DOSYALARI] as URUN_DOSYALARI " & "on HAMMADDE.[STOK_KODU] = URUN_DOSYALARI.[STOK_KODU] Where URUN_DOSYALARI.[URUN_KODU]='" & ListBox1.LIST(ListBox1.ListIndex, 0) & "')"

Bu şekilde yazmaya çalıştım fakat söz dizilimi yanlış hatası veriyor! Üç tabloda nasıl yapılacağını bilen var mı?

 

[Zeki Gürsoy]

MS Acces' te ikiden fazla tablonun birleşimi diğer sql server sistemler gibi basit olmuyor. Aslında zor değil de parantez kuralları gerektiriyor. Önce Access'in sorgu sihirbazı ile sorguyu hazırlayın, sonra VBA kodlarınıza adapte edersiniz.

 

[ragnorak]

Access'i çok bilmiyorum. Sadece tabloları orada oluşturdum. Ama anladığım kadarıyla bu tip işler için vba ve Access çok pratik değil. vb.net ve sql server öğrenmek lazım. Teşekkür ederim.

 

[Haluk]

Hemen moral bozmaya gerek yok bence...

Siz ekli dosyayı bir inceleyin..... sonuçlar doğruysa, verileri Access'den alacak şekle getirirsiniz.

.

 

[ragnorak]

Haluk Hocam Kodlar Beyin Yakıyor vallahi Bu haliyle çalışıyor. İlgi ve Alakan çok teşekkür ederim. Sadece birkaç ricam olacak.

Table1.[BIRIM_FIYAT]*(1-[ISKONTO1]/100)*(1-[ISKONTO2]/100)*(1-[ISKONTO3]/100)*(1-[ISKONTO4]/100)

İskontolar bu haliyle uygulanabilir mi? Birim Fiyattan çıkarmasak daha mı kolay olur? Eğer İskonto yoksa sorguyu bozar mı?
Birde "HAMMADDE" listesindeki bazı ürünlerin para birimi Dolar ve Euro! Dolar ve Euro'yu Formda Tetxbox'lardan alıyorum. Bunları sorguda "tl" ye çevirip işlem yapabilir miyiz?

 

[Haluk]

Bence beyin henüz yanmamıştır. Denemeler yaparak uğraşın, bu iş başka türlü öğrenilmiyor çünkü ....

.

 

[Haluk]

Bu arada; bütün iskontoları uygulamak için query'i aşağıdaki gibi kullanın ama, verilen alındığı tabloda değeri olmayan iskontolar için 0 (sıfır) değeri girilmiş olması gerekir.

Query'nin kendisi biraz roman gibi oldu ama, artık idare edin! Gerçi okurken olay takip edilebiliyor....

Bu yüzden, çıkan sonucu kontrol etmenizde fayda var, emin değilim.

     strQuery = "Select sum([NET TOPLAM]) as [GENEL TOPLAM] from " & _
               "(Select Table1.[STOK_KODU],Table1.[FIRMA], Table1.[BIRIM_FIYAT], " & _
               " Table2.[ADET]," & _
               " 1 - Replace(Table3.[ISKONTO_1],'%','')/100 as [ISKONTO1], " & _
               " 1 - Replace(Table3.[ISKONTO_2],'%','')/100 as [ISKONTO2], " & _
               " 1 - Replace(Table3.[ISKONTO_3],'%','')/100 as [ISKONTO3], " & _
               " 1 - Replace(Table3.[ISKONTO_4],'%','')/100 as [ISKONTO4], " & _
               " [ISKONTO1]*[ISKONTO2]*[ISKONTO3]*[ISKONTO4] as [ISKONTO TOPLAMI], " & _
               " Table1.[BIRIM_FIYAT]*Table2.[ADET]*[ISKONTO TOPLAMI] as [NET TOPLAM] from " & _
               "(" & _
                   "[HAMMADDE$] as Table1 " & _
                   "left join " & _
                   "[URUN_DOSYALARI$] as Table2 " & _
                   "on Table1.[STOK_KODU] = Table2.[STOK_KODU] " & _
                ")" & _
                "left join " & _
                "[ISKONTO$] as Table3 " & _
                "on Table1.[FIRMA] = Table3.[FIRMA]" & _
                "where Table2.[URUN_KODU]='" & Range("L3") & "')"

.

 

[Haluk]

@ragnorak;

Sizden bir cevap gelmedi ama, benim gözüme bir şey ters gelince yukarıdaki "romanı" biraz revize ettim. Siz bir ara bakarsınız ....

Not: Ürün Kodunu L3 hücresine yazdıktan sonra kodu çalıştırmayı deneyin.

.

 

[ragnorak]

..."romanı".... Gerçekten Roman oldu. Ama harika oldu
İlk kod'daki [BIRIM_FIYAT]'dan [ISKONTO TOPLAMI]'nı çıkarınca sonuç hatalı oluyordu onu fark ettim. [PARA_BIRIMI] olayını çözmeye çalışıyorum. [PARA_BIRIMI] Dolar ve Euro ise [BIRIM_FIYAT]*EURO.value, [BIRIM_FIYAT]*Dolar.value yazmalı. "when" ile yapılmış örnekler var Nette. Çözüm olabilir diye düşünüyorum. Birde tablodaki her firma için iskonto değerlerine "0" yazmak yerine iskontolara baştan "0" değeri verilebilir mi? Kod baştan "0" değerini alırsa, değer yoksa zaten "0" olarak görecektir diye düşünüyorum.

 

[Haluk]

Para birimi TRY, USD, EUR olarak kurların paritesine göre tüm fiyatların TRY olarak dönüştürülmesine ilişkin "roman" çalışması aşağıda ve ekli dosyada verilmiştir.

Söz konusu "roman", bir zamanların kült sinema filmi "Ağır Roman" a döndü valla ....

Ürün kodu M3 hücresinden, USD ve EUR kurları da M6 ve M7 hücrelerinden okunmaktadır.

    strQuery = "Select CCur(sum([NET TOPLAM])) as [GENEL TOPLAM] from " & _
               "(Select " & _
               " 1 - Replace(Table3.[ISKONTO_1],'%','')/100 as [ISKONTO1], " & _
               " 1 - Replace(Table3.[ISKONTO_2],'%','')/100 as [ISKONTO2], " & _
               " 1 - Replace(Table3.[ISKONTO_3],'%','')/100 as [ISKONTO3], " & _
               " 1 - Replace(Table3.[ISKONTO_4],'%','')/100 as [ISKONTO4], " & _
               " [ISKONTO1]*[ISKONTO2]*[ISKONTO3]*[ISKONTO4] as [ISKONTO TOPLAMI], " & _
                   " IIF(Table1.[PARA_BIRIMI]='" & ChrW(8378) & "', Table1.[BIRIM_FIYAT] * '1'," & _
                   " IIF(Table1.[PARA_BIRIMI]='" & ChrW(36) & "', Table1.[BIRIM_FIYAT] * '" & Range("M6") & "'," & _
                   " IIF(Table1.[PARA_BIRIMI]='" & ChrW(8364) & "', Table1.[BIRIM_FIYAT] * '" & Range("M7") & "', '0')))" & _
                   " * Table2.[ADET] * [ISKONTO TOPLAMI] as [NET TOPLAM] from " & _
               "(" & _
                   "[HAMMADDE$] as Table1 " & _
                   "left join " & _
                   "[URUN_DOSYALARI$] as Table2 " & _
                   "on Table1.[STOK_KODU] = Table2.[STOK_KODU] " & _
                ") " & _
                "left join " & _
                "[ISKONTO$] as Table3 " & _
                "on Table1.[FIRMA] = Table3.[FIRMA]" & _
                "where Table2.[URUN_KODU]='" & Range("M3") & "')"

.

 

[ragnorak]

Haluk Bey ne diyeceğimi, nasıl teşekkür edeceğimi bilemiyorum. Yazdığınız bu sorgu; her kelimesine kadar benim için çok değerli bir ders niteliğinde. Elinize, emeğinize, aklınıza sağlık. Sorgu Sonucu negatif vermesi dışında gayet güzel çalışıyor.

Söz konusu "roman", bir zamanların kült sinema filmi "Ağır Roman" a döndü valla ....

Buna çok güldüm işte. Bu aslında Mobilya İmalatı için yazdığım basit bir ERP'nin küçük ama bir o kadarda önemli bir parçası.
Resimdeki gibi Ürünlerden sonra İş Emirleri, Teklifler, Stok, Satınalma vs. bölümler var. İş Emirleri ; Ürünlerden, Ürünlerde Hammadde'den veya Yarımamuller'den oluşuyor. Yani eğer "Ağır Roman" ı "Ansiklopedi"ye çevirmek isterseniz sorgu için birkaç tablo daha var.

 

[Haluk]

Aslında o sonuçlar neden (-) olarak geliyor, ben de anlamadım ama onunla ilgili panzehiri uyguladım ve ayrıca döviz kurlarında sorguyu yavaşlatan "Replace" komutunun yaptığı işi başka türlü hallettim.

Bu doğrultuda, 16 No'lu mesaj ve ekindeki dosya güncellenmiştir. Oradan tekrar yeni dosyayı indirebilirsiniz.

.

 

[ragnorak]

Evet daha güzel olmuş. Ama şöyle bir sorun fark ettim. Son yaptığınızda Table1.[BIRIM_FIYAT] = 0 olursa "ISKONTO" değerlerini uygulamayı kesiyor!

 

[Haluk]

Öyle bir deneme yapmadım ama; zaten birim fiyatların 0 olmaması gerekir. Yani; Access tablosundaki verilerin doğru eksiksiz olması gerekir diye düşünüyorum.

.