query more than 1 table or form

[Bob H]

I have built a database consisting of 9 tables and forms for all the
various types of tooling equipment a company uses.
Each table has a similar field to each other, i.e. Seriel No, Asset No,
Location and a few other fields.
I would like to know if it would be possible to create a query from all
the tables to see where certain tools were located, or more so what
tools were in a specific location.
The database is not purposly relational, so I don't know if that would
create any problem in what I want to do.

Thanks

 

[KARL DEWEY]

You might try a union query to pull the tables together and then a select
query with criteria.

 

[John W. Vinson]

I have built a database consisting of 9 tables and forms for all the
various types of tooling equipment a company uses.
Each table has a similar field to each other, i.e. Seriel No, Asset No,
Location and a few other fields.

Then I'd say your database structure is incorrect.

I would like to know if it would be possible to create a query from all
the tables to see where certain tools were located, or more so what
tools were in a specific location.

A UNION query would work. See the online help for UNION.

The database is not purposly relational, so I don't know if that would
create any problem in what I want to do.

I would strongly suggest using Access as if it were a relational database
(which it is) rather than as a spreadsheet (which it isn't!)

A better design would be *ONE* equipment table with fields like what you have
now, and an additional Type field. You can create a Query selecting just one
type if that's what's needed, or without a criterion on the Type field if you
want to query (say) locations.

 

[John W. Vinson]

Hi John,
I realise my database may look more like a spreadsheet than a
real database, but as I am not yet knowledgeable yet to create a real
database as you suggest.

The reason for many tables is because there are many types of tools, which
have upto 12 fields for each type of tool. I.E.
Manufacturer, Product, Manufacture Serial No, Asset No, Lenght, Drive size,
SWL, Last Test Date, Certificate No, Next Test Date, Location, and Notes,
etc. Not all fields are used for each tool type.
I will to using the UNION query as suggested.

If the different tool types have different attributes (e.g. a drill bit would
have a diameter but a sawblade wouldn't), you may be getting into the somewhat
advanced topic of "Subclassing". This is one of the rare cases where one to
one relationships are appropriate.

You would have a common Tools table with those fields that are common to all
tool types - Manufacturer, Product, etc. This table would be related one to
one to specific ToolType tables; your Tool table would need a Primary Key
ToolID, perhaps an autonumber, or perhaps the Asset No if it is unique and
stable. The related tables would have the ToolID (or AssetNo) as a linking
field and additional fields specific to that type of tool. You could then more
easily (say) search for all tools at a specific location, without having to
search multiple tables or use UNION queries.

 

[Bob H]

If the different tool types have different attributes (e.g. a drill bit would
have a diameter but a sawblade wouldn't), you may be getting into the somewhat
advanced topic of "Subclassing". This is one of the rare cases where one to
one relationships are appropriate.

You would have a common Tools table with those fields that are common to all
tool types - Manufacturer, Product, etc. This table would be related one to
one to specific ToolType tables; your Tool table would need a Primary Key
ToolID, perhaps an autonumber, or perhaps the Asset No if it is unique and
stable. The related tables would have the ToolID (or AssetNo) as a linking
field and additional fields specific to that type of tool. You could then more
easily (say) search for all tools at a specific location, without having to
search multiple tables or use UNION queries.

I do have a ToolType table with 10 different types of tools etc., I have
only listed them under Field Catagory Name, then created tables for each
catagory with all the various fields which are applicable to each type.

Most tools do have an Asset No., but not all as it was not possible to
stick or engrave one on the smaller precision equipment
Only some tools have a Manufacture Name or a Manufacturer Serial No, so
its a bit hit and miss there.
Every tool has a Location, so that could be the Primary Key, the only
problem there is Duplicates!, so it maybe would be best to have or use
Autonumber.

Thanks

 

[John W. Vinson]

Every tool has a Location, so that could be the Primary Key, the only
problem there is Duplicates!, so it maybe would be best to have or use
Autonumber.

If duplicates are even a future possibility then Location would be
inappropriate... even more so, if there is ever a possibility that a tool
would move from one location to another location, that rules it out. You will
indeed want some sort of programmatically assigned ToolID, and an autonumber
may be your best bet (if it can stay inside Access for linking; autonumbers
are not usually suitable for human consumption).

 

[Bob H]

If duplicates are even a future possibility then Location would be
inappropriate... even more so, if there is ever a possibility that a tool
would move from one location to another location, that rules it out. You will
indeed want some sort of programmatically assigned ToolID, and an autonumber
may be your best bet (if it can stay inside Access for linking; autonumbers
are not usually suitable for human consumption).

Yes ok, thanks, but I'm not sure what you mean by your last/closing
ststement in brackets.

I have just bought a book on Access; Access 2007 Inside Out, so I'll do
a bit of reading of that to see what way is best to develop the current
database into a relational on.

Thanks

 

[John W. Vinson]

an autonumber

Yes ok, thanks, but I'm not sure what you mean by your last/closing
ststement in brackets.

Just that autonumbers are messy. People looking at them expect them to be
"record numbers", sequential, without gaps; autonumbers WILL have gaps in the
sequence (e.g. if you start a record, change your mind and hit <Esc> to
cancel, that autonumber gets used up and will never be seen again). They can
even become random.

I have just bought a book on Access; Access 2007 Inside Out, so I'll do
a bit of reading of that to see what way is best to develop the current
database into a relational on.

Good book! Some other resources are at:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials