Design flaw?

[anc24]

I am working on a chemical inventory database - there are 12 labs that
contain about 4,000 chemicals between them, many of them with the same
chemicals. Each chemical has a unique number.

It seemed easier to create 12 separate tables, one for each lab, rather than
having one table with 36 fields (a lab number, chemical location and chemical
amount for each lab); however, I run into a problem when I want to find out
whether a chemical is in more than one lab, i.e., which lab(s) contains the
chemical methanol?

Is there a query that will carry across all tables and collect all matches?

Thanks

 

[RobFMS]

One immediate thought I have is doing a UNION ALL query... but this will
work if the table structure is the same for each of the tables. I might
suggest looking at the help file for several examples.

HTH

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Sylvain Lafontaine]

Yes, it's a design flaw to have created 12 separate tables.

You should have created a table for chemicals, another for the labs and a
third describing the location and quantity of each chemical for each lab.
It's even possible, if you need so, to have more than one entry per chemical
per lab if necessary; for example if you want to track individual bottle or
peremption dates.

Your design of having a single big table or 12 different big tables (one for
each lab) has the merit of simplicity; however it is doomed. The more you
will work with it, the more trouble you will have doing new queries or
generating reports.

You should buy a good book on Access, SQL and database design.

As the other answer say; doing an Union will give you the answer you are
searching; however; the simple fact of doing an Union on 12 tables indicates
a design flaw.

S. L.