Query to show last two instances meeting criteria

[scottb]

Hello all,
I have a table (tblCORServiceHistory) with three fields I am using in a query: AssetID, CORServiceDate, and CORServiceTypeID. I am trying to write a query that will show AssetID and CORServiceDates for the last 30 days where an AssetID had consecutive CORServiceTypeIDs = 8 (8 equals no service performed). Functionally trying to identify the assets that have not been serviced for the last two service cycles to ensure they can be serviced in the next cycle. I have started with:

SELECT tblCORServiceHistory.AssetID, tblCORServiceHistory.CORServiceDate, tblCORServiceHistory.CORServiceTypeID
FROM tblCORServiceHistory INNER JOIN tblCORServiceType ON tblCORServiceHistory.CORServiceTypeID = tblCORServiceType.CORServiceTypeID
GROUP BY tblCORServiceHistory.AssetID, tblCORServiceHistory.CORServiceDate, tblCORServiceHistory.CORServiceTypeID
HAVING (((tblCORServiceHistory.CORServiceDate) Between Date() And Date()-30) AND ((tblCORServiceHistory.CORServiceTypeID)=8))
ORDER BY tblCORServiceHistory.CORServiceDate;

And now I’m stuck. Any help would be greatly appreciated. Thank you. -Scott
(Using Access 2010.)

[HansV]

I changed Date()-30 to Date()-29 because "Between Date() And Date()-30" includes 31 days, not 30.
You can use the following SQL to return all fields:

SELECT tblCORServiceHistory.AssetID, tblCORServiceHistory.CORServiceDate, tblCORServiceHistory.CORServiceTypeID
FROM tblCORServiceHistory
WHERE (((tblCORServiceHistory.CORServiceDate) Between Date()-29 And Date()) AND (((SELECT Count(*) FROM tblCORServiceHistory AS T WHERE T.AssetID=tblCORServiceHistory.AssetID AND T.CORServiceDate Between Date()-29 And Date() AND T.CORServiceTypeID=8))=30));

To show only the unique AssetIDs:

SELECT DISTINCT tblCORServiceHistory.AssetID
FROM tblCORServiceHistory
WHERE (((tblCORServiceHistory.CORServiceDate) Between Date()-29 And Date()) AND (((SELECT Count(*) FROM tblCORServiceHistory AS T WHERE T.AssetID=tblCORServiceHistory.AssetID AND T.CORServiceDate Between Date()-29 And Date() AND T.CORServiceTypeID=8))=30));

[scottb]

Thank you Hans. Working perfectly. I appreciate it.

[scottb]

I think I spoke too soon. Not I I have records that I would like for the query to return but it does not seem to be working. Not sure what I did wrong. I attached the query and table with data that I believe it should be returning with consecutive records with no service. Thank you for any help.

[HansV]

I had assumed that

"the last 30 days where an AssetID had consecutive CORServiceTypeIDs = 8"

meant that there should be 30 consecutive records with service type = 8. Since your sample table has far fewer records, my assumption must have been incorrect.

Does this do what you want?

SELECT DISTINCT tblCORServiceHistory.AssetID
FROM tblCORServiceHistory
WHERE (((tblCORServiceHistory.CORServiceDate) Between Date()-29 And Date())
AND (((SELECT Count(*) FROM tblCORServiceHistory AS T WHERE
T.AssetID=tblCORServiceHistory.AssetID AND T.CORServiceDate Between
Date()-29 And Date() AND T.CORServiceTypeID=8))=(SELECT Count(*) FROM tblCORServiceHistory AS T WHERE
T.AssetID=tblCORServiceHistory.AssetID AND T.CORServiceDate Between
Date()-29 And Date())));

Or should an AssetID also be included if there are NO records for the last 30 days?

[HansV]

If you meant the latter, the SQL is actually easier:

SELECT DISTINCT tblCORServiceHistory.AssetID
FROM tblCORServiceHistory
WHERE ((((SELECT Count(*) FROM tblCORServiceHistory AS T WHERE
T.AssetID=tblCORServiceHistory.AssetID AND T.CORServiceDate Between
Date()-29 And Date() AND T.CORServiceTypeID<>8))=0));

or

SELECT DISTINCT tblCORServiceHistory.AssetID
FROM tblCORServiceHistory
WHERE (((tblCORServiceHistory.AssetID) Not In (SELECT T.AssetID FROM tblCORServiceHistory AS T WHERE
T.AssetID=tblCORServiceHistory.AssetID AND T.CORServiceDate Between
Date()-29 And Date() AND T.CORServiceTypeID=8)));

[scottb]

This first sql does what I wanted. Thank you Hans. As always I appreciate your help.