Almost identical sub tables

[Petr Danes]

I have two datasets linked in a one-to-many relationship. The many side of
the dataset comprises two identical layouts, differing in only one key
field. I have so far dealt with the issue by splitting the many side of the
dataset into two tables, each served by a separate subform off the main form
and a set of toggle buttons to switch between identical overlaid displays.
It works, but the duplication of everything is starting to get under my
skin. It seems there should be a better way to handle this, but I have been
unable to think of one.

The core of the problem lies in the fact that the key fields are not quite
identical in behavior. There are two fields, letter(s) and number to make up
the key. They have data like:
K 23
K 34
K 199
L 100
L 150
L 179
T 331
T 452
T 453

and

Ls 56
Ls 56
Ls 57
Ls 57
Ls 57
Ms 120
Ms 135
Vs 22
Vs 22
Vs 76
Vs 938
Vs 938

and so on. Sharp guessers have probably spotted the difference already: one
subdataset has duplicates in the key field, the other does not, so I have
two different indexes on the two tables, one allowing duplicates, the other
unique. (The "s" stands for skupina, or group.) I would love to combine
these datasets into one table, but the thought of VBA coding to handle the
two different types of data constraint in the same column makes my skin
crawl, not to mention that it would be dependent on the code, rather than an
attribute of the table directly.

Other than this one peccadillo, the tables are -completely- identical, which
makes all the duplication very irritating, but a better approach has so far
escaped me. Does anyone have an idea how this might be better designed?

Pete

 

[KARL DEWEY]

Have you considered using three tables in a one-to-many-to-many relationship?
Like this --
Commodity Type Variety
Fruit
Apple
Granny
Macintosh
Banana
Nut
Pecan
Walnut
English
Black

 

[Evan Keel]

What information are you working with that differs by one column? What do
your tables look like?

Evan

 

[Petr Danes]

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

 

[Petr Danes]

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

 

[Evan Keel]

Is this a top-post group or a bottom-post group? For now I will just top
post. Not knowing much about the museum biz I googled MUSEUM SOFTWARE. I
found this: www.museumsoftware.com/pastperfect4.htm

Look at the screen shots. Is paleontology that much different?

Evan

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

 

[Evan Keel]

Look at www.museumsoftware.com/pastperfect4.htm
Look at the screen shots and you will see the db design.

Evan

 

[Petr Danes]

Hello Evan,

Is this a top-post group or a bottom-post group? For now I will just top
post.

I've seen people do it both ways, I don't know that there is an accepted
standard here. Nothing of the sort was mentioned in the FAQs last time I
looked there. I use top-reply and inline-reply, depending on the nature of
the communication.

Not knowing much about the museum biz I googled MUSEUM SOFTWARE. I
found this: www.museumsoftware.com/pastperfect4.htm

Look at the screen shots. Is paleontology that much different?

Some of it looks fairly similar to what I'm doing, although mine is much
simpler. The screens appear to be very nicely laid out - obviously the
designers devoted some thought to the user interface. Even the price seems
quite reasonable, if the software does in fact do everything the website
promises. But how does it help me? You say (in your other post) the
screenshots will show me the DB design, but I don't see anything in them
that would allow me to deduce that. Did I miss something?

Pete