Creating a listing from multiple tables in Access 2007

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob H - 30 Nov 2008 17:33 GMT

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

Reply to this Message

Duane Hookom - 30 Nov 2008 18:14 GMT

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?

Signature

Duane Hookom
Microsoft Access MVP

> 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
[quoted text clipped - 9 lines]
>
> Thanks

Reply to this Message

Bob H - 30 Nov 2008 20:18 GMT

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

> 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?

Reply to this Message

Douglas J. Steele - 30 Nov 2008 21:32 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Well as there are 9 different types of tools, as in Hydraulic,
> Pneumatic, Eectrical, Welding, Precision, Safety Equipment, Lifting
[quoted text clipped - 21 lines]
>> are normalized. Can you share your reasons for separate tables as well as
>> your current table structures?

Reply to this Message