Grouped duplicates showing as >1 entry

[BigAl]

Which probably doesn't say what I want it to :--)

A variant on this was posted onto the Reports group a week ago but. so far,
no-one has been able to respond.


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

That was the original post - now minus the SQL from the original query.

What I'm now trying to do is run a grouped duplicates query so that only a
single record per reg_no pr date is picked up but I've obviously got some
thing wrong as it lists each module entry as a separate record, even though
the records are duplicates.

The SQL for the duplicates query is:

SELECT [Query - NCCQ Exam Confirmation].reg_no, [Query - NCCQ Exam
Confirmation].Membership_Commenced, [Query - NCCQ Exam Confirmation].title,
[Query - NCCQ Exam Confirmation].surname, [Query - NCCQ Exam
Confirmation].initials, [Query - NCCQ Exam Confirmation].first_names,
[Query - NCCQ Exam Confirmation].address1, [Query - NCCQ Exam
Confirmation].address2, [Query - NCCQ Exam Confirmation].address3, [Query -
NCCQ Exam Confirmation].address4, [Query - NCCQ Exam Confirmation].County,
[Query - NCCQ Exam Confirmation].postcode, [Query - NCCQ Exam
Confirmation].Country, [Query - NCCQ Exam Confirmation].[Date Entered]
FROM [Query - NCCQ Exam Confirmation]
WHERE ((([Query - NCCQ Exam Confirmation].reg_no) In (SELECT [reg_no] FROM
[Query - NCCQ Exam Confirmation] As Tmp GROUP BY [reg_no] HAVING
Count(*)>1 )))
ORDER BY [Query - NCCQ Exam Confirmation].reg_no;

Any and all help gratefully received - even that which highlights what an
idiot I probably am :--)

BigAl

 

[Carl Rapson]

Which probably doesn't say what I want it to :--)

A variant on this was posted onto the Reports group a week ago but. so
far, no-one has been able to respond.


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

That was the original post - now minus the SQL from the original query.

What I'm now trying to do is run a grouped duplicates query so that only a
single record per reg_no pr date is picked up but I've obviously got some
thing wrong as it lists each module entry as a separate record, even
though the records are duplicates.

The SQL for the duplicates query is:

SELECT [Query - NCCQ Exam Confirmation].reg_no, [Query - NCCQ Exam
Confirmation].Membership_Commenced, [Query - NCCQ Exam
Confirmation].title, [Query - NCCQ Exam Confirmation].surname, [Query -
NCCQ Exam Confirmation].initials, [Query - NCCQ Exam
Confirmation].first_names, [Query - NCCQ Exam Confirmation].address1,
[Query - NCCQ Exam Confirmation].address2, [Query - NCCQ Exam
Confirmation].address3, [Query - NCCQ Exam Confirmation].address4,
[Query - NCCQ Exam Confirmation].County, [Query - NCCQ Exam
Confirmation].postcode, [Query - NCCQ Exam Confirmation].Country, [Query -
NCCQ Exam Confirmation].[Date Entered]
FROM [Query - NCCQ Exam Confirmation]
WHERE ((([Query - NCCQ Exam Confirmation].reg_no) In (SELECT [reg_no] FROM
[Query - NCCQ Exam Confirmation] As Tmp GROUP BY [reg_no] HAVING
Count(*)>1 )))
ORDER BY [Query - NCCQ Exam Confirmation].reg_no;

Any and all help gratefully received - even that which highlights what an
idiot I probably am :--)

BigAl

You're getting separate records for each module because you're not grouping
your records in the main query. You're grouping them in the subquery, but
all that does is remove duplicates from your IN clause. You would need to do
your grouping the main query, and group on the reg_no and date to get a
single record per reg_no/date pair.

Carl Rapson

 

[BigAl]

Which probably doesn't say what I want it to :--)

A variant on this was posted onto the Reports group a week ago but. so
far, no-one has been able to respond.


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

That was the original post - now minus the SQL from the original query.

What I'm now trying to do is run a grouped duplicates query so that only
a single record per reg_no pr date is picked up but I've obviously got
some thing wrong as it lists each module entry as a separate record, even
though the records are duplicates.

The SQL for the duplicates query is:

SELECT [Query - NCCQ Exam Confirmation].reg_no, [Query - NCCQ Exam
Confirmation].Membership_Commenced, [Query - NCCQ Exam
Confirmation].title, [Query - NCCQ Exam Confirmation].surname, [Query -
NCCQ Exam Confirmation].initials, [Query - NCCQ Exam
Confirmation].first_names, [Query - NCCQ Exam Confirmation].address1,
[Query - NCCQ Exam Confirmation].address2, [Query - NCCQ Exam
Confirmation].address3, [Query - NCCQ Exam Confirmation].address4,
[Query - NCCQ Exam Confirmation].County, [Query - NCCQ Exam
Confirmation].postcode, [Query - NCCQ Exam Confirmation].Country,
[Query - NCCQ Exam Confirmation].[Date Entered]
FROM [Query - NCCQ Exam Confirmation]
WHERE ((([Query - NCCQ Exam Confirmation].reg_no) In (SELECT [reg_no]
FROM [Query - NCCQ Exam Confirmation] As Tmp GROUP BY [reg_no] HAVING
Count(*)>1 )))
ORDER BY [Query - NCCQ Exam Confirmation].reg_no;

Any and all help gratefully received - even that which highlights what an
idiot I probably am :--)

BigAl

You're getting separate records for each module because you're not
grouping your records in the main query. You're grouping them in the
subquery, but all that does is remove duplicates from your IN clause. You
would need to do your grouping the main query, and group on the reg_no and
date to get a single record per reg_no/date pair.

Carl Rapson

Thanks Carl.

Working it the other way round gets the desired result. I (obviously) was
unaware that the duplicates check didn't work as a subquery.

Thanks again.

BigAl