09-01-09, 11:50 #1Registered User
- Join Date
- Sep 2009
Unanswered: Problem creating relationships, which type? Suggestions?
Hi there guys... relatively new to Access and trying to create the following and having trouble figuring out how to lay out the following:
I am creating a quality control logging system. Ive created a table for red tag rejected items. Each red tag contains generates EITHER an NCMR(nonconforming material report) or a CAR (corrective action), depending on whether it is the vendors fault or our fault that the part was rejected.
In any case, ive created a table called tblRedTag with a primary key being the red tag number. In this red tag table, ive created a field (fldReport) that I want to correspond to EITHER an NCMR number (primary key on tblNcmrs) or a CAR (primary key on tblCars).
Every NCMR or CAR could contain MANY red tags, but each red tag has only ONE ncmr OR car...
how do you think is the best way to set this up? I tried two one-to-many with referential integrity with different JOIN properites, but every time i try and enter in a number into the fldReport (i have a corresponding number in ncmr) it pops up with a message that i have to have a corresponding number in the tblCars table...
I hope this isnt too confusing... PLEASE HELP! =)
09-02-09, 07:25 #2Grumpy old man (training)
Provided Answers: 8
- Join Date
- Sep 2006
- Surrey, UK
In order for this design to work, you'll need to break normal forms. Your desired end result is to have one record in tblRedTag linked to a record in one of two other tables, without knowing which one at the time.
The closest way around this that I can see it to remove the report field from tblRedTag and create an intersection table with three fields:
The three fields form a compound key on that table. The two that relate to reports have default values that fill the available characters with spaces or some other character to avoid Null issues. You also need to create a dummy record in both of the report tables mimicking the default value. The intersection table then provides the link between the red tag and whichever report is required.
This isn't an ideal solution, though. Would it be possible to put both of the report types into the same table, and have a field that indicates which one they are?10% of magic is knowing something that no-one else does. The rest is misdirection.
Beers earned: 1
09-02-09, 07:53 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
you have a table for the items tested
you have columns within that indicating what tests
you have to sub tables containing the corrective action and NCMR outcomes (assuming you need to record those as separate outcomes, with their own unique data).
if your application requires to follow the actions on CAR or NCMR then I'd suggest you have a primary key which is the ID of the test and something which makes each CAR/NCMR row unique... something similar to a item number on an order... if you go down that road then you will need to develop a method to get the next available ID number
you shouldn't need to create dummy rows
you should be able to extract what you want by judicious use of joins.
when you create your form then you enter rows into either NCMR or CAR sub forms.I'd rather be riding on the Tiger 800 or the Norton