Grouped duplicates showing as >1 entry

B

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
 
C

Carl Rapson

BigAl said:
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
Click to expand...

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
 
B

BigAl

Carl Rapson said:
BigAl said:
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
Click to expand...

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
Click to expand...

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
 

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

Similar Threads

Semi Duplicate records to be exluded from a report 0
saved queries based on other saved queries vs. one big query in a form's module using DAO 5
TransferSpreadsheet fails (error 3027) if 1 record returned? 0
Create Excel Spreadsheet from Access with 1 to many relationship 1
Query Records Showing Null AND Count = 1 2
Normalizing an older table through maketable queries 2
Forms - Displaying Multiple Records in one form 3
Preventing Duplicates in Query 1

Top