Query Limiting data shown between two databases..

[millsk]

I have two data bases. The first one has a list of equipment. This list comprises of 17,000 unique objects. (primary key)



The second data base is a smaller list (11K) of the same equipment. It is smaller because not all the equipment has maintenance performed to it. It shows the equipment number and a number corrisponding to the type of maintenance performed on the equipment. This list has duplicates in it because sometimes equipment has more then one type of maintenance performed to it.

I am attempting to show the first long list of equipment, and include it it any maintenance type that is performed on it. I would like this list to include all 17k objects even if they do not have maintenance performed.

Currently when I run the query only the 11k show.

[pbaldy]

Sounds like you need to change the join between the tables. With the query in design view, right click on the join line to edit it. You should see the correct option at that point.

[jzwp11]

oops, I see Paul beat me to the punch

I assume that when you say you have two databases, you actually mean you have 2 tables.

You will need to change the join type between the two tables. When you get only the 11k records you are only matching those records of the larger table that have related records in the smaller table. If you look at the query in SQL View you will see an INNER JOIN between the two tables. What you need is a left join.

FROM largertable LEFT JOIN smallertable ON largertable.IDfield=smallertable.IDField

If you are using the design grid view, right click on the join line between the two tables and select the option to include ALL records from the larger table as well as those records from the smaller table that match

[millsk]

Problem solved. Thank you much!

[jzwp11]

You are welcome. Good luck with your project.