Question Need results from 3 Tables

[rdskill]

I have spent the day on this and still can't get it to give me the results I want.

Here is my data:

InvLocation (30,000 recs):
[B]ID      Location         InvAvail[/B]
9005	22BULK    	1
9004	22BULK    	1
9004	22BULK    	2
9004	22BULK    	1
9006	22BULK    	1


Items  (80,000 recs):
[B]ID      Sku             Desc[/B]
9004	X23243BA	AAAAAAAAAAAAA
9005	X1234443	BBBBBBBBBBBBBBB
9006	X2222223X	CCCCCCC

Bins (700 records):
[B]BinID   ItemID[/B]
22BULK	9004
22BULK	9005
22BULK	9006

Here is my sql statement:

                sql = "SELECT i.Sku, i.IDesc, il.InvAvail" & _
                      "  FROM Bins b" & _
                      "    JOIN InvLocation il  ON (b.BinID = il.ID)" & _
                      "    JOIN Items i         ON (il.ID = i.ID)" & _
                      "  WHERE il.Location = '22BULK'"

I am expecting 5 records to result from the query, but so far I can't get it to return any records using a single query statement. Just 0 records returned without errors.

Any help would be appreciated!

David

 

[InertiaM]

Is it generating an error, or just not returning any records? My reason for asking is that your field names at the top do not appear to match with the fields used in the query (eg Sku and ItemSku, ID and InvItemID).

 

[rdskill]

Is it generating an error, or just not returning any records? My reason for asking is that your field names at the top do not appear to match with the fields used in the query (eg Sku and ItemSku, ID and InvItemID).

I just forgot to make the statement match my quickly done tables. I was trying to make it more clear. Amazing how many errors I spotted after a good nights sleep. lol

The statement does work! Turns out I picked a bulk location that was missing from the data. Arrrrrgggghhhhh...

So half a day shot. Ah, the life of a programmer.

Thanks for the reply InertiaM.