Duplicate Records Created

[DrStephen]

OK...weird problem (aren't they all?)...

I have a data set with 4 fields: TestName, Group, Division and Grade. I have 400 staff taking exams. The dataset returned by the online test-taking application gives me a table of every user by every exam. I have a total of 11 exams. Here's the first rub: Not all staff take all of the exams. For discussion sake, let's say Group A Exams 1 -4, Group B takes Exams 4 - 8 and (remember this part) everyone takes exams 9 - 11.

My task within Access is to create a table or query that contains only the exams a staff person is supposed to take.

Here's how I did it:

I created a query joining 'T Staff' (which contains the field 'Group') with 'Exam Results' (which has 'TestName'). I then added a set of criteria such as the followin:

TestName Group
"Exam 1" "A"
"Exam 2" "A"
"Exam 3" "A"
"Exam 4" "A"
"Exam 5" "B"
"Exam 6" "B"
"Exam 7" "B"
"Exam 8" "B"
"Exam 9"
"Exam 10"
"Exam 11"

Note that there were no criterion set for Group for Exams 9 -11 (because everyone takes them). The query runs fine with one exception. The records for Exams 1 - 8 are correct (1 record per staff). However, I get duplicate records for Exams 9 - 10.

I know it's going to end up being something simple, but it's driving me crazy. Any ideas, anyone?

 

[lespaul]

are you joining into any other tables? What's the actual SQL?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi