Hi there,

I would really appreciate some help with a query that I am trying to build. I thought I was able to solove this but it's baffled me!

Basically I am creating a database which will record IT inventory details. Therefore I have the following tables:

Building (Building ID is the primary key)
PC (PC ID is the primary key and Building ID is the foreign key)
Monitor (Monitor ID is the primary key and Building ID is the foreign key)
Printer (Printer ID is the primary key and Building ID is the foreign key)

I have set them up with one-many relationships. I want to create a query which will join all the tables so that I can see exactly what equipment belongs to which building.

At the moment it works perfectly when I run the query with just the Building and the PC tables. But as soon as I add the Monitor and/or Printer table(s) then it duplicates each Monitor/Printer to match the PC from that building.

For example if I have two HP Compaq PC at BuildingA and one Brother printer at Building A then the result of the query shows the two PCs but shows two Brother printers instead of just the one.

Hopefully someone will have the expertise to solve this one! Thanks very much. :)

Paul.

Try a union... Use the query you have for the PC's
then create a second for the Desktops
then create a third for the printers
Now:
Select * from PCs
union
select * from desktops
union
select * from printers

Thats your solution... You have to make sure that all 3 querys for pc/desktop/printer contain the same fields eg:
Building Type Name
A PC PC1
A PC PC2
A Printer P1


Regards

Superb namliam - a true pro!!! Thanks very much indeed!!