Dear Jim:
I have altered your query a bit for my own reading, hopefully without
changing its function:
SELECT First([Child's Last Name]) AS [Child's Last Name Field],
First(ID) AS [ID Field],
First([Date Checked Out]) AS [Date Checked Out Field],
Count(ID) AS NumberOfDups
FROM [Client Data Query5]
GROUP BY [ISIS #], [Child's Last Name], ID, [Date Checked Out]
HAVING Count([ISIS #]) > 0
AND Count([Date Checked Out]) > 0;
It is unusual to GROUP BY a column and also report that column using an
aggregate. You have done this with [Child Last Name], [ID] and [Date
Checked Out]. I'm going to drop the grouping on these.
I'm also changing Count(ID) and Count([Date Checked Out]) and Count([ISIS
#]) to Count(*). This may or may not make a difference. If it does, it may
be an improvement. More on this later if you require it.
SELECT First([Child's Last Name]) AS [Child's Last Name Field],
First(ID) AS [ID Field],
First([Date Checked Out]) AS [Date Checked Out Field],
Count(*) AS NumberOfDups
FROM [Client Data Query5]
GROUP BY [ISIS #]
HAVING Count(*) > 0;
The above will not count the number of occurrances of each ISIS #. I'm
guessing that's what you were after all along. Is it the case that all the
members of a family have the same ISIS #? If it is duplicates of just this
you want to show, then you must be sure to GROUP BY only this!
May I make a recommendation? Try this query:
SELECT [ISIS #]
FROM [Client Data Query5]
GROUP BY [ISIS #]
HAVING Count(*) > 1;
With the assumptions I have already stated, this would give the ISIS # of
each family with more than one recorded member. (I think you may have meant
1 all along! That's typical for finding duplicates.)
The above query could then be used to find all the members of each such
family:
SELECT Q1.*, Q2.*
FROM [Client Data Query5] Q2
INNER JOIN
(SELECT [ISIS #], Count(*) AS DupCount
FROM [Client Data Query5]
GROUP BY [ISIS #]
HAVING Count(*) > 1) Q1
ORDER BY Q2.[ISIS #], Q2.ID
One warning about using First() - it will pick one of the family members,
seemingly at random. However, at least it will pick the same one for each
of the columns shown. I thought the last query I gave might be more useful,
as it shows all the family members together.
Did this help? Please let me know!
Tom Ellison
JimT said:
I'm having issues removing be able to filter duplicates. I'm trying to
determine the number of families using services. Here's my SQL.
SELECT First([Client Data Query5].[Child's Last Name]) AS [Child's Last
Name
Field], First([Client Data Query5].ID) AS [ID Field], First([Client Data
Query5].[Date Checked Out]) AS [Date Checked Out Field], Count([Client
Data
Query5].ID) AS NumberOfDups
FROM [Client Data Query5]
GROUP BY [Client Data Query5].[ISIS #], [Client Data Query5].[Child's Last
Name], [Client Data Query5].ID, [Client Data Query5].[Date Checked Out]
HAVING (((Count([Client Data Query5].[ISIS #]))>0) AND ((Count([Client
Data
Query5].[Date Checked Out]))>0));
Can anyone help?
