Forum Discussion
Microsoft Access - Max and Min Values
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.