SQL Inner Join Identical Field Names 1

[MattSTech]

I have been up a while working on this and just can't see my mistake. I am ultra new to using innerjoin, but I feel my sql statement is ok. I need to pull a bunch of fields together and it just so happens there are two different dates (SOMAST.fduedate) and (SOITEM.fduedate). I now need to retrieve both. How do I go about doing this? I have tried objRS.Fields("SOMAST.fduedate").Value but that was just a shot from the hip. Any thoughts?

strSQL = "SELECT SOMAST.fsono,SOMAST.fcustno,SOMAST.Fcompany,SOMAST.fcustpono,SOMAST.fstatus,SOITEM.finumber," & _
"SOITEM.fgroup,SOITEM.finumber,SOITEM.fpartno,SOITEM.fdesc,SOITEM.fduedate,SOITEM.FQUANTITY,SOITEM.fprodcl," & _
"SORELS.Finvqty,SORELS.Forderqty,SORELS.funetprice,SOMAST.fduedate " & _
"FROM(sorels INNER JOIN ((SOITEM_EXT INNER JOIN soitem ON SOITEM_EXT.FKey_ID = SOITEM.identity_column) " & _
"INNER JOIN SOMAST ON SOITEM.fsono = SOMAST.fsono) ON (SOITEM.finumber = SORELS.finumber) " & _
"AND (SORELS.fsono = SOITEM.fsono))" & _
" WHERE SOMAST.fsono = '" & strSO & "'"
Else
strSQL = "SELECT * FROM soitem WHERE fsono = '" & strSO & "' AND fprodcl = " & strProd
End If
objRS.Open strSQL, objCon, adOpenKeyset, adLockReadOnly
Do While Not objRS.EOF
If objRS.Fields("finvqty").Value <> objRS.Fields("forderqty").Value Then
lstvwBreakdown.ListItems.Add , , lstvwSchedule.ListItems(i).Text
lstvwBreakdown.ListItems(lstvwBreakdown.ListItems.Count).SubItems(1) = objRS.Fields("finumber").Value
lstvwBreakdown.ListItems(lstvwBreakdown.ListItems.Count).SubItems(2) = objRS.Fields("fcompany").Value
lstvwBreakdown.ListItems(lstvwBreakdown.ListItems.Count).SubItems(3) = lstvwSchedule.ListItems(i).SubItems(2)
lstvwBreakdown.ListItems(lstvwBreakdown.ListItems.Count).SubItems(4) = objRS.Fields("fduedate").Value

 

[ChewDoggie]

strSQL = "SELECT SOMAST.fsono,SOMAST.fcustno,SOMAST.Fcompany,SOMAST.fcustpono,SOMAST.fstatus,SOITEM.finumber," & _"SOITEM.fgroup,SOITEM.finumber,SOITEM.fpartno,SOITEM.fdesc,SOITEM.fduedate,SOITEM.FQUANTITY,SOITEM.fprodcl," & _"SORELS.Finvqty,SORELS.Forderqty,SORELS.funetprice,SOMAST.fduedate AS SOMAST_DUEDATE, SOITEM.fduedate AS SOITEM_DUEDATE " & _"FROM(sorels INNER JOIN ((SOITEM_EXT INNER JOIN soitem ON SOITEM_EXT.FKey_ID = SOITEM.identity_column) " & _"INNER JOIN SOMAST ON SOITEM.fsono = SOMAST.fsono) ON (SOITEM.finumber = SORELS.finumber) " & _"AND (SORELS.fsono = SOITEM.fsono))" & _" WHERE SOMAST.fsono = '" & strSO & "'"ElsestrSQL = "SELECT * FROM soitem WHERE fsono = '" & strSO & "' AND fprodcl = " & strProd

HTH

Chew


10% of your life is what happens to you. 90% of your life is how you deal with it.

 

[MattSTech]

Brilliant!! Thank you so much!!

I didn't know you could rename the fields like that. Great to know.

Matt

 

[gmmastros]

The technical term is "Alias". You can also alias tables, which comes in handy if you need to join to the same table multiple times in the same query.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom