Hello Karl,

I'm afraid that I don't understand your suggestion. How would this allow me
eliminate the duplicate tables? Perhaps my original post didn't describe the
problem clearly enough. I've described the DB structure in somewhat more
detail in my response to Evan, does that help any?

Pete

Hello Evan,

the data is inventory control of a museum's paleontological collection. The
main table is a rough sort of initial documentation of its acquisition by
the museum and may apply to entire collections acquired as a whole, for
instance, from the estate of a private collector. It may also be a single
rock. The subtables then have more detailed descriptions of the contents,
consisting mostly of links to other tables, like taxonomic description,
location where found, location where currently stored, name of initial
collector, lithographic units, stratigraphic units and so on. In fact, there
are so many attached tables that I'm running up against the Access limit of
32 indexes, which means I'm also looking into a migration to SQL server (and
for other reasons, not germane to this discussion.)

The ID field of the two subtables is the set of two columns I listed in my
original post. The table with duplicates not allowed describes generally
single rocks, or occasionally two halves of a rock split along a fossil,
with the positive and negative imprint of the fossil on the two halves. No
problem there.

The table with duplicates generally applies to a box of rocks, or several
boxes of rocks which share the same number. For instance, a collector may
have chipped out twenty tiny identical snail shell fossils, which are not
significant enough to inventory individually, share the same properties,
(genus, species, location found, etc.), then later brought in another box of
the same stuff. These properties are the same as the properties for the
individual fossils, so the two tables link to the same set of description
tables: genus, species, location and so on. Some of the duplication is
error, where two people mistakenly used the same ID, but some is real and in
both cases, I do -NOT- have the option of changing any of it. Much of it is
historical data, now being transferred to machine form and it must match the
actual records in the written archives. The museum is a government-sponsored
institution and must follow regulations regarding inventory of material
dictated by the Ministry of Culture. Not always do regulations make sense,
but that is out of my hands - I must deal with the data as it is.

Is this enough information? I can list all the fields in the tables if you'd
like, as well as the relationships, but most of it is not relevant to the
topic. The only thing giving me a rash is the two slightly differing ID
fields, in the two subtables.

Pete