finding records within a 30 day period

[Kelly]

I have a database of faults. Each fault has a Reference number to identify its location. I need a query to tell me when 3 faults have occurred within a 30 day period (rolling 30 days) and only show those records. The individual fault also has a reference number which will need tb shown on the table also. Can someone help me with a query for this please



Thanks in advance

 

[namliam]

You could try and do this in 3 steps
1) Make a new query to get all faults over the last 30 days
criteria would be on the date and >Date - 30

2) Make a query on top of the first one
"Group by" and Count then put >=3 on the counted column

3) Join the query in 2 to your original table and report it back.

 

[gemma-the-husky]

this sort of stuff is notoriously tricky though, although in your case, it probably isn't so hard.

the problem is that when you use a totals query (same with any aggregate function - max, min etc) - you can find a value of the item you want - but you can't show some other (eg expression) data in the same query.

so if you do a dmax, say, - you find that the highest date is #18/11/2010# - but then you have to re-lookup based on that date value to find the record id, corresponding to the max value you just had!

it's SO irritating.