Producing report from 2 tables

M

Mark

I've been trying for a week now, I have a table of box numbers and related
info and a table of box numbers that have been checked. I'm trying to produce
a listing of the boxes remaining to be checked. I've tried appending the list
and choose hide dupes but that just eliminates the box number, all the other
info is still there. I'm getting nowhere with this. It should be the same
thing as inventory, you have all inventory, eliminate all items already
inventoried and get listing of items still needing to be inventoried.

Any help?
 
T

Tony

Is the box number in both tables the primary key? If so, link this two field
together in a query and for the box numbers in the table that have been
checked put "is null" as the criteria to filter on.

-Tony
 
S

Steve Schapel

Mark,

Make a query which includes both your tables, and join them on the Box
Number field. In query design view, double-click on the join line
between the tables, and set it to option 2 or 3, whichever one says
something like "All boxes and only those related checked boxes". Put
the required fields from the Boxes table into the query design grid, and
also the Box Number field from the checked boxes table, and in the
criteria of this field put Is Null. This should then return the boxes
that have not yet been checked. The SQL view of such a query will look
something like this...
SELECT Boxes.[Box Number], Boxes.[OtherStuff]
FROM Boxes LEFT JOIN CheckedBoxes ON Boxes.[Box Number] =
CheckedBoxes.[Box Number]
WHERE CheckedBoxes.[Box Number] Is Null
 
Top