Find Duplicates with a Twist

[koolwaters]

I am trying to create a query that finds duplicates in a particular column but only if the date column is on or after the current date.

I used the Wizard which correctly returns the duplicated records. However, when I add my date criteria I am not getting the results I want.

For example, let's say I am trying to find a duplicated CustomerJobID but only if the ServiceDate is scheduled for today or later. So I have two JobIDs of 23. The find duplicates query returns those two records. I then need to filter those two records if the ServiceDate is on or after the current date. I created a query based on the find duplicates and for the ServiceDate field I have the criteria >=Date(). This then returns one record because only one job is scheduled for today or after. This is not what I want however. Since only one job meets the criteria, it is no longer a duplicate job.

Is there any way to accomplish this without creating multiple find duplicates queries?

Thanks in advance for any help.

 

[Joe4]

Can you post the SQL code of your query attempt, after you add the Date Criteria, and we'll see if we can help you clean it up to do what you want?

 

[koolwaters]

SELECT tblCustomerJob.[CustomerJobID], tblCustomerJob.[AccountNo], tblCustomerJob.[ServiceDate]
FROM tblCustomerJob
WHERE (((tblCustomerJob.[CustomerJobID]) In (SELECT [CustomerJobID] FROM [tblCustomerJob] As Tmp GROUP BY [CustomerJobID] HAVING Count(*)>1 )) AND ((tblCustomerJob.[ServiceDate])>=Date()))
ORDER BY tblCustomerJob.[CustomerJobID];

The above SQL statement returns all jobs with a duplicate CustomerJobID where the ServiceDate is on or after the current date. Using just the duplicate CustomerJobID there may be multiple records, however, after the records are filtered by the date criteria, there may only be one duplicate job ID. In those instance, it is no longer a duplicate job and I would not want that record. So essentially, after the query is executed I only want data if there is 2 or more records.

 

[Micron]

Rather than attempt to include totals within a find duplicates query (not that I think you can) I'd create a second (totals) query using the first as the table and set the criteria on a Count field to be > 1.

 

[Joe4]

Here is some SQL code that will do it all in a single shot,

SELECT Tab.CustomerJobID, Tab.AccountNo,Tab.ServiceDate
FROM tblCustomerJob as Tab
INNER JOIN
(SELECT tblCustomerJob.CustomerJobID, Count(tblCustomerJob.AccountNo) AS CountOfAccountNo
FROM tblCustomerJob
WHERE tblCustomerJob.ServiceDate>=Date()
GROUP BY tblCustomerJob.CustomerJobID
HAVING Count(tblCustomerJob.AccountNo)>1) as Tot
ON Tab.CustomerJobID=Tot.CustomerJobID
WHERE Tab.ServiceDate>=Date();

Though, it may be easier to understand to do it in two queries.

Going that route, you would use the first query to return all future records that have more than one record (for a particular JobID):

SELECT tblCustomerJob.CustomerJobID, Count(tblCustomerJob.AccountNo) AS CountOfAccountNo
FROM tblCustomerJob
WHERE tblCustomerJob.ServiceDate>=Date()
GROUP BY tblCustomerJob.CustomerJobID
HAVING Count(tblCustomerJob.AccountNo)>1;

and then join that query back to your original table like this:

SELECT tblCustomerJob.CustomerJobID, tblCustomerJob.AccountNo, tblCustomerJob.ServiceDate
FROM tblCustomerJob INNER JOIN Query1 ON tblCustomerJob.CustomerJobID = Query1.CustomerJobID
WHERE tblCustomerJob.ServiceDate>=Date();

Both will get you to the same point.

[/CODE]