bentheimmigrant
Lost & confused
- Local time
- Today, 17:33
- Joined
- Aug 21, 2015
- Messages
- 60
So, I'm trying to learn SQL a little better, to avoid abuse Dlookup, and generally use good practice. Now, the table setup probably isn't quite ideal, but it is what it is at this stage, and there's no going back.
I'm trying to figure out how to join 4 tables, but with two of the tables being part of the Join to the last one. The structure is as follows:
tblA, with key ID, and field B_ID
tblB with non-unique field B_ID, non-unique field B_Position (the position is unique for each B_ID), and field C_ID
tblC with key ID, and field C_Position (the position is not unique, but it is for
tblD, with fields A_ID and D_Position (which should match C_position. This is where the structure probably isn't ideal)
I'm hoping build a string to join all 4 tables, based on the criteria of tblA.ID = vbID (an integer in the routine)
I had a go (well, a few more than one), looking generally like this:
I end up with error 3296, "JOIN expression not supported." Which I think is because of the AND? But I've tried it without the AND, just to see what would happen, and it still has a JOIN error. If I use INNER JOIN for all of them, I get no results (but there definitely should be).
Can I ORDER BY the two position fields, to get rid of that AND?
Now, to the untrained eye, it may appear that I have half a clue. But to everyone else, it should be apparent that I do not.
I'm trying to figure out how to join 4 tables, but with two of the tables being part of the Join to the last one. The structure is as follows:
tblA, with key ID, and field B_ID
tblB with non-unique field B_ID, non-unique field B_Position (the position is unique for each B_ID), and field C_ID
tblC with key ID, and field C_Position (the position is not unique, but it is for
tblD, with fields A_ID and D_Position (which should match C_position. This is where the structure probably isn't ideal)
I'm hoping build a string to join all 4 tables, based on the criteria of tblA.ID = vbID (an integer in the routine)
I had a go (well, a few more than one), looking generally like this:
Code:
SQLstr = "SELECT * FROM ((tblA" & _
"LEFT JOIN tblB ON tblA.B_ID = tblB.B_ID) " & _
"INNER JOIN tblC ON tblsB.C_ID = tblC.ID) " & _
"LEFT JOIN (tblD ON tblA.ID = tblD.A_ID " & _
"AND tblC.C_Position = tbl_D.D_position) " & _
"WHERE tblA.ID = " & vbID
Can I ORDER BY the two position fields, to get rid of that AND?
Now, to the untrained eye, it may appear that I have half a clue. But to everyone else, it should be apparent that I do not.