Say I have three tables (just using Access for this one).
Each for the most part, has identical columns. But, each also has one or two that are unique.
The primary key (unique identifier) in each is set to AutoNumber.
Is there a way to set it up so that the AutoNumber recognizes the numbers from the other tables and doesn't produce numbers identical to records in those tables. I would like to make sure each record in each of these tables has a unique identifier -- unique to all three tables.
There isn't a well defined way to do this, although there are several different approaches that work reasonably well.
I think that the best way to handle this kind of problem is to create a "parent" table that has the common columns, with an autonumber as its primary key. Then create three sub-tables that have the unique columns for each of the sub-types, and create a (foreign key) relationship between these tables and the parent table.
This reduces redundancy, and it makes it easier to be sure that the autonumber values are only used by one of the sub-types. It isn't perfect, but it is probably the best you can do "out of the box".