common ID key for many tables

  • Thread starter Thread starter asc4john
  • Start date Start date
A

asc4john

I currently have several databases describing industrial plants,
mostly for maintance and construction.
There are tables for equipment, devices, instruments, cables, piping
lines and so on.
For most of the things I want to do everything seems fine. However I
now want to build more complex queries and tables and I am running
into problems. The items are identified in the tables with their
tagname which is unique within the table but may not be unique within
the database.
i.e. device 123-ABC-1234 and cable 123-ABC-1234. This has not been an
issue since I've been interested only in device+cable or tank+line.
But now I want ot describe entire groups. i.e. tank, line pump, line,
then instruments on the line, kind of like a list. Is there away of
adding an identifier unique in the database to each item.
 
The problem you mention doesn't seem to be related to the cause you
give and the solution you suggest/request has an unreasonable
assumption. Sorry you asked?? :-)

Queries get more complex as you ask them to do more things.

The solution you seek could easily take the form a new table, called a
lookup table. I'd call it tblLine and list within it all of the lines
you intend to recognize. The Primary keys of the items in that table
need only be unique within that table, not the whole database.

Now, a new field (Line) must be added to the tables for the things
that will receive the "Line" attribute. Step through your records
picking the correct "Line" for each one from a combobox whose
recordsource is tblLine.

HTH
 
Another approach might be to use a query to "gather" each of the components
from their respective tables, and in each query, add a text value that
represents the source. That is, for the query against your equipment table,
add "equipment", for the devices query, add "devices", and so on.

Then assemble your queries' fields together, including each query's
"moniker".

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
i.e. device 123-ABC-1234 and cable 123-ABC-1234. This has not been an
issue since I've been interested only in device+cable or tank+line.
But now I want ot describe entire groups. i.e. tank, line pump, line,
then instruments on the line, kind of like a list. Is there away of
adding an identifier unique in the database to each item.
Click to expand...

A unique identifier - or a primary key - need not consist of just *one* field.
It could consist of two (Group and Instrument) - or even of ten, if need be.

John W. Vinson [MVP]
 
Back
Top