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
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