common ID key for many tables

[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.

 

[Larry Daugherty]

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

 

[Jeff Boyce]

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

 

[Allen Browne]

Side-comment:

Can I encourage you not to use dashes in the field if you want it to sort
correctly:
http://support.microsoft.com/?id=236952
http://support.microsoft.com/kb/271661

 

[John W. Vinson]

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.

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]