Multiple Line Query

[airtabaki]

I have an Acces Databank where I store all the installations of our products.
So I have a table named SITE (Primary key: SITE ID) where I store all the info of all the different locations where I have to install my products.
Then for each site there could be some problems. This I store in my table MAINTENANCE. (Primary keys: SITE ID and DATE; connection with SITE: 1 - Many relationship) Now I want to create a query to look when I had to make more than 2 repairs in one month time. How can I do this

 

[rac2]

SELECT site_id,
       DatePart("yyyy", date_service_call) AS Year,
       DatePart("m", date_service_call) AS Month
FROM Maintenance
GROUP BY site_id, DatePart("yyyy", date_service_call), DatePart("m", date_service_call)
HAVING COUNT(*) > 1

 

[PHV]

look when I had to make more than 2 repairs in one month time
SELECT [SITE ID], Format([DATE], 'yyyy-mm') AS [Month time], Count(*) AS CountOfRepairs
FROM MAINTENANCE
GROUP BY [SITE ID], Format([DATE], 'yyyy-mm')
HAVING Count(*) > 2;

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

 

[airtabaki]

RAC2,

your solution works perfectly. Thank you very much.

PHV,

although the dates on the screen look the same, the data behind isn't the same. The counter will always see different records; this function won't work. But it was nice to learn.

Thank you both very much