Forum Discussion

LD527's avatar
LD527
Copper Contributor
Dec 13, 2022

Microsoft Access - Max and Min Values

Hello, 

 

I currently have a table with 14 entries.  In the "TAG" field there are 7 unique entries that each appear twice (7x2=14 entries).  The reason there are two of each is because each TAG appears on two different drawings (each having a different drawing number).

 

How can I create an automated table such that it only has 7 entries and both drawings that the TAG appears on are combined into on record, for example, TAG: INST-0"-ELEC-999 || Drawings: 12345-01-E-PP-PID-01-00.DWG, 12345-01-PP-PID-02-00.DWG.

 

I am guessing there is a query I can write using the builder function, but I haven't been able to figure it out.  I would greatly appreciate any help.

 

Thank you!

  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor

    LD527 

     

    Hi, "Unique" means "one of a kind", so you can't have both "unique" tags and "duplicate" tags, by definition. In fact, the heart of this question is an implicit recognition of that fact.

     

    You can do this with TWO tables, not one.

     

    The first table has ONLY the 7 unique tags in it. You should designate this field as a Primary Key.

     

    In the second table, the 7 tags are repeated twice, once with each of its potential drawing names. In this table, however, the Tags are Foreign Keys related back to the main table. This is a one-to-many relationship: One Tag applies to one or more Drawings.

     

    You do NOT want to combine two drawings into a single field in a table. That's a problem waiting to break your application. 

     

    You could, for display purposes in a form, or for display purposes in a report, concatenate the two Drawing titles into a single field to show to a user. However, trying to permanently store them that way is a non-starter for a relational database application.

     

    This relational design has one other advantage that may come into play later. Adding a third drawing to a tag, or a fourth, or a fifth, is as simple as inserting new records into the Many side table with the appropriate Foreign Key for its Tag.

    Plus, if you need new tags, a new record in the One side table is all that is required. Any associated drawings can then be added to the Many side table as appropriate.

     

    I've seen several methods of concatenating child records FOR DISPLAY as you require. One of the best, IMO,  is here. There are, no doubt, others.

Resources