Find items listed more than twice in 3 months

[Hfnet]

I have a table with the fields IDNum, DateIn, DateOut.

I want to do a query to show all IDNum that appear 3 or more times in the last 3 months, I have used a query to try to find the data, but I get all items listed 3 or more times, regardless of the date.

My code used was :

SELECT Items.IDNum, Items.datein, Items.dateout
FROM Items
WHERE (((Items.IDNum) In (SELECT [IDNum] FROM [Items] As Tmp GROUP BY [IDNum] HAVING Count(*)>2 )) AND ((Items.datein) Between Date()-90 And Date()))
ORDER BY Items.IDNum;

This was modified from a find duplictaes wizard

 

[KenReay]

You need the date criteria applied to the subquery (I believe), something like:

SELECT Items.IDNum, Items.datein, Items.dateout
FROM Items
WHERE Items.IDNum In (SELECT [IDNum] FROM [Items] As Tmp GROUP BY [IDNum] HAVING Count(*)>2 AND Items.datein Between Date()-90 And Date())
ORDER BY Items.IDNum;


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Hfnet]

No, it is still pulling records from a year ago and more....

 

[lespaul]

How about this:

SELECT Items.IDNum, Items.datein, Items.dateout
FROM Items
WHERE ((Items.datein) Between Date()-90 And Date())
HAVING Count(IDNum)>2
ORDER BY Items.IDNum;

 

[Hfnet]

Cannot have aggregate function in WHERE clause?....

 

[lespaul]

ok, then how does this work:

SELECT Items.IDNum, Items.datein, Items.dateout
FROM Items
WHERE ((Items.datein) Between Date()-90 And Date())
GROUP BY Items.IDNum, Items.datein, Items.dateout
HAVING Count(IDNum)>2
ORDER BY Items.IDNum;


Leslie

 

[PHV]

Something like this ?
SELECT IDNum, datein, dateout
FROM Items
WHERE IDNum In
(SELECT IDNum FROM Items WHERE datein Between Date()-90 And Date() GROUP BY IDNum HAVING Count(*)>2)
AND datein Between Date()-90 And Date()
ORDER BY IDNum;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Hfnet]

Thanks PH, returned just 2 records correctly within a 3 month period!!

The 2 records returned are both listed 3 times, is there a way of stripping out the result so that only one record is shown?

 

[lespaul]

add DISTINCT to the first SELECT clause:

SELECT DISTINCT IDNum, ...

Leslie

 

[Hfnet]

Currently I get:

11234
11234
11234
13456
13456
13456

When using DISTINCT, this still shows

11234
11234
11234
13456
13456
13456

What I want to do is just take the first instance of each IDNum, so that I get a list of items to watch,

11234
13456

 

[lespaul]

Ok, but you have two other fields in your select clause:

SELECT IDNum, datein, dateout

if your returning:

IDNum datein Dateout
1 5/5/04 6/10/04
1 6/15/04 7/11/04
1 8/15/04 9/16/04

then, those are all distinct records. If you only want one of those records, you need to decide what criteria to use to figure out which one you want to keep.

leslie

 

[Hfnet]

Gotcha, thanks!

 

[lespaul]

I guess you could also just remove those other fields from your SELECT

SELECT DISTINCT ItemID ....



Leslie

 

[PHV]

If you're just interested in IDNum, the query is simpler:
SELECT IDNum FROM Items WHERE datein Between Date()-90 And Date() GROUP BY IDNum HAVING Count(*)>2;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244