Semi Duplicate records to be exluded from a report

B

BigAl

Which probably doesn't make much sense.

I'm writing a membership database which also includes exam registrations and
results.
When members enter an exam they will actually be entering several modules
that comprise that exam. What I'm trying to do is create a letter saying
thanks, money received and you're entered into the examination modules but,
because there is more than one module which, in turn, creates more than one
record, I'm creating more than one letter per examinee.

Two tables:

1st
reg_no plus address details etc.

2nd
reg_no
Exam_Module
Date_Entered

are the relevant fields.

SQL:

SELECT [Personal Details].reg_no, [Personal Details].Membership_Commenced,
[Personal Details].title, [Personal Details].surname, [Personal
Details].initials, [Personal Details].first_names, [Personal
Details].address1, [Personal Details].address2, [Personal Details].address3,
[Personal Details].address4, [Personal Details].County, [Personal
Details].postcode, [Personal Details].Country, [Table - Exam Results].[Exam
Module], [Personal Details].Membership_Status, [Table - Exam Results].[Exam
Result], [Table - Exam Results].[Date Entered]
FROM [Personal Details] RIGHT JOIN [Table - Exam Results] ON [Personal
Details].reg_no = [Table - Exam Results].reg_no
WHERE ((([Table - Exam Results].[Exam Module]) Is Not Null) AND (([Personal
Details].Membership_Status)="nccq") AND (([Table - Exam Results].[Exam
Result]) Is Null) AND (([Table - Exam Results].[Date Entered])=[Enter
Date]));

Removing Exam_Module which allows a true duplicate record to be created from
the query doesn't work even with the property setting "Unique Records" set
to yes I still +1 returned.

Can any one tell me how I can only create one record please?

TIA

BigAl
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top