I need help,
I have a 2 tables, one has autonumber for all the units i have, as well as colour and brand. The other table is a service history of the units i have serviced. I need a query that will display all the units i HAVE NOT serviced. PLEASE HELP.
The service isnt a yes / no, the service is a table, everytime i service something i record yes/no for things i do (painting, weed eating for ex.)
in table 1 i have the fire hydrant number (autonumber & address)
in table 2 (service) i have the hydrant number, date serviced, and what i did (yes/no stuff)
if i never serviced hydrant #7, it is not in the service table because no data entry was done.
I need to get a list of every hydrant # that does not appear in table 2
This involves what's called an "Outer Join" query. When you have joined two tables in the query view, click on the connecting line. You'll see that you have three choices. When either the second or third choice is selected, the line becomes an arrow.
A so-called "inner join" query is the usual type. In two tables U and S, the query considers only rows that match in both tables. This would find only units that you have serviced: only IDs that occur in both U and S.
A so-called "outer join" query causes all of the rows in one table (left join) or the other (right join) to be included whether or not there is a matching record in the other.
In your example, you probably want "U LEFT JOIN S," an arrow pointing from U to S, indicating that you want to consider all rows in U even if there are no matching records in S.
So how do you see if there is a matching record? Include the key field from S, and then check for NULL. A key value can never be null (a unit can never lack an ID), so if the joined column is NULL it means that there is no record there at all.
So... add the tables...join them...click on the join line...select a left-join...add the fields from "Units" you want to see...then add the key-field from "Service"...enter criteria "Is Null"...uncheck the display box on that column (don't need to see it).