Duplicate Value Selection Within Specified Dates

[Terry_C]

I have a query that returns duplicate records based on a selectable threshold (count) from a year’s worth of data. I have modified this to select records from selectable periods (1 week, 1 Month 6 months etc) via a form input to the criteria for date selection. This works except that the query still returns records that have a value equal to or exceeding the threshold (count) setting which fall outside the selected date range.

For example if I ask for records where the count exceeds 25 during the past seven days it will only show details of records within the seven day period but where the count exceeds 25 over a longer period. How should I modify the query to show just those records that are duplicated according to the count criteria and are within the selected date periods only?

I have tried reordering the query fields but with no success, this is probably simple but I can’t fathom it!

 

[David R]

Post the SQL of your query. Sounds like you may have some OR statements where there should be AND statements.

 

[Terry_C]

David,
Here is SQL - many thanks.

SELECT DISTINCTROW [Main Table].CREATE_DATE, [Main Table].CELL_ID__A_END_, [Main Table].REGION, [Main Table].REQUIRED_ACTION, [Main Table].WORK_ORDER_NO, [Main Table].[Category1?], [Main Table].SERVICE_IMPACT, [Main Table].[Monitoring Status]
FROM [Main Table]
WHERE ((([Main Table].CREATE_DATE)>=#4/1/02# And ([Main Table].CREATE_DATE)<#4/8/02#) AND (([Main Table].CELL_ID__A_END_) In (SELECT [CELL_ID__A_END_] FROM [Main Table] As Tmp GROUP BY [CELL_ID__A_END_] HAVING Count(*)>= Forms![RepeatCellIncidentSelect]![Combo10] )) AND (([Main Table].REGION) Like [Forms]![RepeatCellIncidentSelect]![Combo8]))
ORDER BY [Main Table].CELL_ID__A_END_;

 

[David R]

Try
WHERE (Main_Table.CREATE_DATE Between #4/1/2002# And #4/8/2002#) And...

Although that gave me the same results as I got for < and >. Try going back to the Find Duplicates Wizard and setting up your query from scratch again if my suggestion doesn't work. Probably a rogue parenthesis somewhere...

 

[Terry_C]

Thanks david, tried both your suggestions and still the same results. Will persevere on a trial and error basis!

 

[David R]

Go back and recreate your query from the Find Duplicates Query Wizard. I made something that looked almost identical to yours in 45 seconds, and it gave the results you're describing.

If you want after you're done you can spot-check it to see what's different between yours and theirs, but the important thing is to get the data working, right?

 

[Terry_C]

Thanks David
I’ve not been able to look at this for a while but still have the same problem. To try and see where I’m going wrong I’ve done the following basic test with the same results. – and still can’t see what’s happening.

1) Created a simple table with two fields “Name” and “Date” (General Date Format)

2) Entered a number of records some with the same name (A, B, C etc.) but different dates/times

3) Created a Find Duplicates query to search for duplicated names with a count value of >3 after a certain date. SQL is SELECT DISTINCTROW Table1.Date, Table1.Name
FROM Table1
WHERE (((Table1.Date)>#3/31/02#) AND ((Table1.Name) In (SELECT [Name] FROM [Table1] As Tmp GROUP BY [Name] HAVING Count(*)>3)))
ORDER BY Table1.Date DESC;

The query returns names that exceed a count of 3 in the entire table and yet displays those with a date later than that selected. How do I get it to select and display those that have a count of >3 (or any other value) after the date selected?

One other point (as a novice!) I note the SQL shows the date in a different format to that entered in the query criteria (>#31/03/2002#) – is this correct?

Many thanks

 

[David R]

Try naming your fields something more specific than 'Name' and 'Date' - those are reserved words and very likely may be part of the problem.

If that doesn't solve it, then it's possible I still have the example I did for you around here somewhere...post back..

 

[Terry_C]

David,

This is just a test database I created to try out various options (I've just changed Name and Date to Item and Period - same results) In my working database the fields in question are named Create_Date and CELL_ID__A_END_ as posted in original SQL.

Thanks
Terry

 

[David R]

My apologies for not reading your initial posting more carefully. You want those records which have a duplication of more than 3 (25) and ALL later than a certain date?

You can probably do this with one but I know I got it working with two queries.
1st Query: Use the DateField's criteria to narrow down the records to < 31/3/2002 or whatever.
2nd Query: Use the Find Duplicates wizard on the 1st query to find those records counting more than 3 (25).

The reason it was giving you problems is because the In() clause was creating a temporary group that counted them regardless of your DateField criteria.

 

[Pat Hartman]

The problem is that the inner query that does the count does not include the date qualification. As the query is currently written, the count is performed on the entire domain of dates rather than the specific range.

 

[Terry_C]

Thanks David and Pat. I'll use two queries as I can't see how to accomplish this easily with a single query with my current knowledge of Access.