select data from more than 1 table

Question

I am using access as my db and am having trouble trying to get my query to select the correct data. At the moment my query seems to be returning all records in the tables when really for this particular value in the WHERE clause, it should be returning only 1.

The idea is to use the 'Requests Boxes' table based on the value in the WHERE clause and use those value to pull data from the 'Boxes' table. I would be grateful if someone could point out my newbie error. I would like to add that I inherited this db and have no option to change it. Many thanks

My db setup is as follows.

Requests Boxes

Request no

Box

Boxes

Box

CustRef

sql = "SELECT [Request Boxes].[Request no], [Request Boxes].Box, Boxes.Box, Boxes.CustRef " &
                  "FROM [Request Boxes], Boxes WHERE [Request Boxes].[Request no] = '" & item & "'"

UPDATED CODE:

sql = "SELECT [Request Boxes].[Request no], [Request Boxes].Box, Boxes.Box, Boxes.CustRef " &
                  "FROM [Request Boxes] INNER JOIN Boxes ON [Request Boxes].Box = Boxes.Box " &
                  "WHERE ((([Request Boxes].[Request no]) = '" & item & "' )" &
                  "AND ([Request Boxes].[Customer] = '" & customer2 & "') AND (Boxes.Status = '" & status & "'))"

Answer 1

You are joining the two tables with your FROM clause...

FROM [Request Boxes], Boxes

This means you will get all the Boxes rows, attached to the Request Boxes row you are correctly selecting. To pick out the Boxes row matching, add an ON clause to the FROM...

FROM [Request Boxes], Boxes ON [Request Boxes].[Box] = [Boxes].[Box]

This will ensure that only the matching Boxes row is attached to the [Request Boxes] row.