Find Duplicate Records by Date

[Molasses26]

I have a database where several people enter work items every day and I need
to check for duplicate records after a given but not before.
Item EntryDate
1234 1/5/06
1234 4/12/06 ~
2345 2/9/06
2345 5/9/06 ~
3456 3/15/06
3456 4/22/06 * ~
3456 4/25/06 * ~
Given this data and using a date of 4/1/06 I want the query to only return
the items marked with * however, everthing I try returns all of the rows with
~ because they have duplicates prior to 4/1.

In (SELECT [Item] FROM [qryData] As Tmp GROUP BY [Item] HAVING Count(*)>1
And [EntryDate]>[Start Date])

Any help will be greatly appreciated!! Thanks!

 

[Jerry Whittle]

Does the table have a primary key? Looks like you are building a query upon
another query. Therefore the SQL for both queries would help us to help you.

 

[John Spencer]

I would try the following untested SQL statement

SELECT Item, EntryDate
FROM QryData
WHERE Item In (
SELECT [Item]
FROM [qryData] As Tmp
WHERE [EntryDate]>[Start Date]
GROUP BY [Item] HAVING Count(*)>1 )
AND EntryDate > [Start Date]

 

[Molasses26]

Worked like a charm John!! Thanks a bunch!

I would try the following untested SQL statement

SELECT Item, EntryDate
FROM QryData
WHERE Item In (
SELECT [Item]
FROM [qryData] As Tmp
WHERE [EntryDate]>[Start Date]
GROUP BY [Item] HAVING Count(*)>1 )
AND EntryDate > [Start Date]

I have a database where several people enter work items every day and I
need
to check for duplicate records after a given but not before.
Item EntryDate
1234 1/5/06
1234 4/12/06 ~
2345 2/9/06
2345 5/9/06 ~
3456 3/15/06
3456 4/22/06 * ~
3456 4/25/06 * ~
Given this data and using a date of 4/1/06 I want the query to only return
the items marked with * however, everthing I try returns all of the rows
with
~ because they have duplicates prior to 4/1.

In (SELECT [Item] FROM [qryData] As Tmp GROUP BY [Item] HAVING Count(*)>1
And [EntryDate]>[Start Date])

Any help will be greatly appreciated!! Thanks!