Did you try the query I posted? Did it fail to give you the desired
results? Or did it generate an error?
I believe that it should return the records you are interested in.
Alternate Two query approach.
Build a query to select all the records for the time frame you are
interested in. Now use the duplicates query against that query.
SELECT [Prospect Students].[Date of Contact]
, [Prospect Students].[Last Name], [Prospect Students].[First Name]
, [Prospect Students].SSN, [Prospect Students].Address
, [Prospect Students].[Lead Source]
, [Prospect Students].[Staff Code]
FROM [Prospect Students]
WHERE [Prospect Students].[Date of Contact]>=#1/1/2007#
Save the above as qCurProspect
Now use the duplicates query wizard to build a query returning the
duplicates in the above query
SELECT *
FROM QCurProspects
WHERE [Last Name] IN (
SELECT [Last Name]
FROM QCurProspects As Tmp
GROUP BY [Last Name], [First Name]
HAVING Count(*)>1
and [First Name] = [QCurProspects].[First Name])
ORDER BY [Last Name], [First Name]
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Ricoy-Chicago said:
Jerry's and Karl's SQL statement did not work. Thank you to both but
Karl's
is closer to what I want but still is not the correct output. I am going
to
try an example:
Case A. Joe Doe's information is entered on 9/9/05 and then it is
re-entered
on 7/7/07. This is not a duplicated record on 2007.
Case B. Joe Doe's infromation is entered on 2/2/07 and then it is
re-entered
on 10/31/07. This is a duplicated record for 2007.
For case B there is no problem.
For case A, the query should not show Joe Doe' records; however, the query
shows Joe doe once, because his data was entered on 9/9/05 (previous
calndar
year) it is not a duplicated record in 2007. He should not appear at all.
That's what my problem is, I end with a lot of "single" records.
John Spencer said:
I might try the following. Note that I changed the date comparison to >=
and calculated the date as January 1 of the current year using the
DateSerial function
SELECT [Prospect Students].[Date of Contact]
, [Prospect Students].[Last Name], [Prospect Students].[First Name]
, [Prospect Students].SSN, [Prospect Students].Address
, [Prospect Students].[Lead Source]
, [Prospect Students].[Staff Code]
FROM [Prospect Students]
WHERE [Prospect Students].[Date of Contact]>=DateSerial(Year(Date()),1,1)
AND [Prospect Students].[Last Name] In
(SELECT [Last Name]
FROM [Prospect Students] As Tmp
WHERE TMP.[Date of Contact] >=DateSerial(Year(Date()),1,1)
GROUP BY [Last Name], [First Name]
HAVING Count(*)>1
and [First Name] = [Prospect Students].[First Name])
ORDER BY [Prospect Students].[Date of Contact] DESC
, [Prospect Students].[Last Name], [Prospect Students].[First Name];
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
sorry about the delay... had to grab something to eat...
SQL:
SELECT [Prospect Students].[Date of Contact], [Prospect Students].[Last
Name], [Prospect Students].[First Name], [Prospect Students].SSN,
[Prospect
Students].Address, [Prospect Students].[Lead Source], [Prospect
Students].[Staff Code]
FROM [Prospect Students]
WHERE ((([Prospect Students].[Date of Contact])>#1/1/2007#) AND
(([Prospect
Students].[Last Name]) In (SELECT [Last Name] FROM [Prospect Students]
As
Tmp
GROUP BY [Last Name],[First Name] HAVING Count(*)>1 And [First Name] =
[Prospect Students].[First Name])))
ORDER BY [Prospect Students].[Date of Contact] DESC , [Prospect
Students].[Last Name], [Prospect Students].[First Name];
when I run the query, all the records have a 2007 date, there is no
problem
with that but I have records that show only once because a duplicated
record
exist back, let say on September 2006. So i don't want so see those.
:
Show us the SQL. Open the query in design view. Next go to View, SQL
View
and copy and past it here.
Also provide examples of what you do and do not want to see returned
by
the
query.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
Access XP. I have created a query to find duplicate records, no
problem
here.
Management wants to see duplicated records ONLY for the current
calendar
year. I have a date field and used a criteria to restrict the fields
to
the
required dates, no problem here.
Problem: some records show only once because the data may exist in a
previuos year. For example if data was entered in June 2004 and then
the same
data is entered in December 12, 2007 this is NOT considered a
duplicated
record. However, if the data is entered on 1/1/07 and then again
today,
this
IS a duplicated record.
This query is used to generate a report, as you can imagine the
report
has a
lot of non-duplicated records.
Any help will be greatly appreciated.
