Creating a listing from multiple tables in Access 2007

[Bob H]

I have built a realational database in Access 2007 with 9 tables,
queries and forms for the 9 different types of tools we use. Each type
of tool is or can be in many different locations, so I have a field
called 'Location' in the tables for the tools, as well as 6/7 other fields.

What I would now like to do is to have access2007 create me a list if
all the tools in a specified location.

Could someone please direct me on the best way of doing this, bearing in
mind that I am still learning Access 2007 and am using a book: Access
2007 Inside Out, where I am about a quarter way through it so far.

Thanks

 

[Duane Hookom]

You might be able to use a union query. I question why you need different
tables for different tools. This doesn't sound like the table structures are
normalized.

Can you share your reasons for separate tables as well as your current table
structures?

 

[Bob H]

Well as there are 9 different types of tools, as in Hydraulic,
Pneumatic, Eectrical, Welding, Precision, Safety Equipment, Lifting
equipment and Fire Extinguishers, I thought it best to have a different
table for each type.

Basiclly each table consists of 10/9 fields:
ID
Manufacturer
Product
Size
Asset No
Serial No
Last Test Date
Next Test date
Location
Notes

I don't know if they are normanlised or not but they are related.

Thanks

 

[Douglas J. Steele]

If all of the tables have the same fields, then there's no reason to have
separate tables for them. Just add another field that identifies the type of
tool, and use one table for all of them.