Querying multiple tables at once

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

Can I query multiple tables at once? I have similar, but not identical tables with different equipment. I have seperate tables for CPUs, Latptops, etc. I want to be able to query multiple tables to find such info as OS, Service tag #, etc.

Thanks,
Tom
 
Hey Tom, sounds like you need a UNION query like this:

Select ServiceTag from table1
UNION Select ServiceTag From table2

To do this, you'll need to edit the query in SQL view.

But if lots of fields are common, you might think about
making this all one table with an equipmenttype field the
typefield would be a foriegn key to a table of the
various "types" each of which is currently a separate
table. That would eliminate a lot of union queries that
are in your future with your current set up. I've done
similar inventory databases this way and found it worked
well.

- Anne

-----Original Message-----
Hi,

Can I query multiple tables at once? I have similar,
Click to expand...
but not identical tables with different equipment. I have
seperate tables for CPUs, Latptops, etc. I want to be able
to query multiple tables to find such info as OS, Service
tag #, etc.
 
Hi Tom. When looking at a query in design view, choose
the view menu, and one of the options will be SQL. But if
you are relatively new to all this, I would definitely go
with the single table and equipmenttype field. Are you
clear on how to do that? If not, let me know, I'll be
online for the next 10 mins or so. - Anne
-----Original Message-----
Hi Anne,

Thanks for the help. I don't believe I have SQL view. I'm using
Access 2000. Is there a way to get SQL views in Access 2000? I'm fairly new
to Access, but I think I can implement your other idea about the equipment
type field.

Thanks again,

Tom :^)
Click to expand...
 
Hi Anne,

I think I'm clear on how to do it. I'm a little confused about the 3 different join types that you have to choose from in Relationships. I think I can figure out the rest though.

Thanks,

Tom
 
If you are not clear on joins then I'd recommend making
sure the EquipmentTypeID field in the Equipment table is
REQUIRED. So long as you don't have a record in the
equipment table that's that has no EquipmentType, the
INNER JOIN will work fine, and that's the default.

Good luck on your project!
- Anne

-----Original Message-----
Hi Anne,

I think I'm clear on how to do it. I'm a little
Click to expand...
confused about the 3 different join types that you have to
choose from in Relationships. I think I can figure out the
rest though.
 
Back
Top