As stated in a previous post, I am redesigning a nonconformance database. Currently I am working on the best way to store tag information.

Any tag is either an IN-House tag (Material has not left our facility it is still in house), A ship tag (meaning the tag that appears on the packing list, or a return tag (returned from a customer).

Now a return tag is connected to the ship tag, thus maintaining product traceability.

Currently I have the tag info table set up as follows:

tagID -pk, Autonumber
RetTagNum- Text
RetTagWt- num
RetTagWtUOM- fk, num
ShipTagNum- Text
ShipTagWt- num
ShipTagWtUOM- fk, num
InHouseTagNum- Text
InHouseTagWt- num
InHouseTagWtUOM- fk, num

so that When I enter a return tag I can also enter its ship tag information.
Now a ship tag can be entered without there needing to be a return tag, A return tag SHOULD have a ship tag, but this is not always the case, as sometimes the material cannot be traced back for various reasons. so I thought that this design is best as it affords every combination, as I can leave fields blank as I need to. However, This seems unnormalized to me, but if I normalize, I don't know how to maintain traceability between ship tags and return tags.

Can anybody suggest a way to normalize this and maintain the relationship between them? Or is the way I have it the best way to do this?

Something like:

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=32057&stc=1&d=1273511749

???

Oh, if I understand this idea then this might work:

tblTagHeaders:
pkTagHeaderID - Autonumber
fkPOID - Number

tblTagDetails
pkTagID - Autonumber
TagNumber - Text
fkTagType - Number (Return, Ship or In House)
fkTagHeaderID - Number

Then for each tag I am dealing with it would have one header, and one or more tag details, but always at least one, and the tags relate to each other through the headerID. Also I could use validation at the form level that if a headerID can have either an InHouse tag type, or a return and ship tag types, but not more than one. Fantastic! Thank you!

If you don't mind post back what you come up with - :)

Hey, I just finished my table design. I ended up using a "relationship" table and used that table to connect to the tags other identifying information. Thank you so much for the idea!

Cool - Glad you have it working. :)