Assigning matching IDs from one table to another
Hi all,
I've been struggling with this problem for a few days now...I'm not that experienced at Access, so hopefully I'll be able to explain it properly for everybody.
A while ago I was tasked with creating a compliance testing database for my company's internal processes. For example: Payments, Hiring, Project Management, etc. Right now it's all done through Excel and is structured like this:
Compliance Test 01:
-----|Sample1|Sample2|Sample3|etc...
Item1|GradeA |GradeB |GradeC |etc...
Item2|GradeB |GradeA |GradeC |etc...
Item3|GradeC |GradeC |GradeA |etc...
etc...
In short, a Test has many Items (based on company regulations, like "Has the project been approved by all superiors?"). For each Item you choose a few Samples ("Project A", "Project B" and so on), and for each Sample the user attributes a Grade ("In Compliance", "Not In Compliance", "N/A", etc).
I've uploaded a barebones version of the DB so you guys can see what I mean.
In the Tests mainform, the user fills in the Test details (Tab #1), chooses which available Items he wants to include in his test (Tab #2), details the Samples which will be tested (Tab #3) and finally assigns a Grade to each Item/Sample combination (Tab #4).
Here's what I can't figure out:
In the attached DB, if we go into the "CT - Projects 2013" and check the Samples in Tab#3, we can see they match perfectly with the ones at Tab#4.
Now if we go into "CT - Projects 2014" and do the same thing, we can see that the Samples assigned to the Items in Tab#4 don't match up with the ones in Tab#3 at all. This is because the DB is checking only if the Samples' ItemIDs match with the ItemIDs in the test. But it also needs to make sure that the TestID, the Item's TestID *and* the Sample's TestID match, or it just won't work!
As always I appreciate any help, sorry for the huge post but I wanted to make sure I explained everything correctly. Please let me know if I can clarify anything for you guys.
Edit: I went ahead and attached a 2003 version of the database as the first upload used Access 2010.
Hi all,
I've been struggling with this problem for a few days now...I'm not that experienced at Access, so hopefully I'll be able to explain it properly for everybody.
A while ago I was tasked with creating a compliance testing database for my company's internal processes. For example: Payments, Hiring, Project Management, etc. Right now it's all done through Excel and is structured like this:
Compliance Test 01:
-----|Sample1|Sample2|Sample3|etc...
Item1|GradeA |GradeB |GradeC |etc...
Item2|GradeB |GradeA |GradeC |etc...
Item3|GradeC |GradeC |GradeA |etc...
etc...
In short, a Test has many Items (based on company regulations, like "Has the project been approved by all superiors?"). For each Item you choose a few Samples ("Project A", "Project B" and so on), and for each Sample the user attributes a Grade ("In Compliance", "Not In Compliance", "N/A", etc).
I've uploaded a barebones version of the DB so you guys can see what I mean.
In the Tests mainform, the user fills in the Test details (Tab #1), chooses which available Items he wants to include in his test (Tab #2), details the Samples which will be tested (Tab #3) and finally assigns a Grade to each Item/Sample combination (Tab #4).
Here's what I can't figure out:
In the attached DB, if we go into the "CT - Projects 2013" and check the Samples in Tab#3, we can see they match perfectly with the ones at Tab#4.
Now if we go into "CT - Projects 2014" and do the same thing, we can see that the Samples assigned to the Items in Tab#4 don't match up with the ones in Tab#3 at all. This is because the DB is checking only if the Samples' ItemIDs match with the ItemIDs in the test. But it also needs to make sure that the TestID, the Item's TestID *and* the Sample's TestID match, or it just won't work!
As always I appreciate any help, sorry for the huge post but I wanted to make sure I explained everything correctly. Please let me know if I can clarify anything for you guys.
Edit: I went ahead and attached a 2003 version of the database as the first upload used Access 2010.
Last edited:
