Selecting all records with ID in same format from multiple tables

[Mark909]

I've got several seperate tables recording information about different assets.

Each table has similar information but there is a PROJECT_ID column
indicating different projects.

I simply want to be able to select a specific PROJECT_ID and then be able to
display every asset relating to that ID in the same window from all the
seperate tables.

I've tried to do a query but seem to get the same record repeated four times
for the same asset.

Any ideas??

Thanks

 

[Brendan Reynolds]

I've got several seperate tables recording information about different
assets.

Each table has similar information but there is a PROJECT_ID column
indicating different projects.

I simply want to be able to select a specific PROJECT_ID and then be able
to
display every asset relating to that ID in the same window from all the
seperate tables.

I've tried to do a query but seem to get the same record repeated four
times
for the same asset.

Any ideas??

Thanks

You may need a union query ...

SELECT SomeField, SomeOtherField FROM SomeTable
UNION SELECT SomeField, SomeOtherField FROM SomeOtherTable

You can't create a union query using the graphical query designer, you'll
need to switch to SQL view.

Try searching help using "union" as the keyword, or switch to SQL view, type
the word union into the SQL pane, and press F1, that should bring you
straight to the relevant help topic.

 

[a a r o n . k e m p f]

If you used SQL Server, you could write a UNION statement to stack
multiple results on top of each other.

I just dont' think that this runs fast enough using MS Access.

Good luck finding a better database than SQL Server

-Aaron

 

[John W. Vinson]

I've got several seperate tables recording information about different assets.

Then you probably have an incorrectly structured database!

Each table has similar information but there is a PROJECT_ID column
indicating different projects.

A better design would be ONE table with an Asset_ID field to distinguish
different assets.

I simply want to be able to select a specific PROJECT_ID and then be able to
display every asset relating to that ID in the same window from all the
seperate tables.

The UNION query suggested elsethread is your best bet; you may even want to
use it as the basis of an Append query to populate a new single table.