Return 2 most recent records

[froggiebeckie]

I've seen some threads on this, but I can't make the answers work for me.

I have a db where I track PMs; completion date per equipment, with calculated due dates based on established frequency compared to last completed date.

No problem.

Now, my overlords have decided that we need to keep track of whether or not the PMs were completed on time. So I need to return the most recent 2 completion dates for each piece of equipment.
I can then do the calculations to determine if the latest PM was completed on time or not.

Here's the SQL of my last attempt at a query for this, but it returns all the records, not just the last 2 for each piece of equipment. When I try to add a sub query to the Criteria line, I get syntax error messages.

SELECT [REQUIRED PM TBL].EQUIPMENT, [COMPLETED PMS TBL].[DATE COMPLETED], [REQUIRED PM TBL].FREQ, [COMPLETED PMS TBL].[REQUIRED PM ID]
FROM [COMPLETED PMS TBL] RIGHT JOIN [REQUIRED PM TBL] ON [COMPLETED PMS TBL].[REQUIRED PM ID] = [REQUIRED PM TBL].[REQUIRED PM ID]
WHERE ((([REQUIRED PM TBL].[OUT OF SERVICE])=False))
GROUP BY [REQUIRED PM TBL].EQUIPMENT, [COMPLETED PMS TBL].[DATE COMPLETED], [REQUIRED PM TBL].FREQ, [COMPLETED PMS TBL].[REQUIRED PM ID]
ORDER BY [REQUIRED PM TBL].EQUIPMENT, [COMPLETED PMS TBL].[REQUIRED PM ID] DESC , [COMPLETED PMS TBL].[DATE COMPLETED] DESC;


Can you aim me in the right direction, please.

BeckieO:banghead:

 

[bastanu]

Hi Beckie,
You need to do a top (2) query on the [completed pms tbl] grouping by the PM ID and sorting the completed date desc(ending. Then use this query in your final one, linking by both the id and the completion dates.

Cheers,
Vlad

 

[CJ_London]

all those caps and spaces, makes you code difficult to understand but you should not need a groupby query, but will need a subquery to select the top 2 for each. The query will be something like

SELECT DISTINCT *
FROM myTable
WHERE myTable.PK IN (SELECT TOP 2 PK FROM myTable T WHERE Equipment=myTable.Equipment ORDER BY compDate Desc)
ORDER BY Equipment, compDate

If you do not have a PK (primary key) field, you will need to create one

 

[arnelgp]

SELECT [REQUIRED PM TBL].EQUIPMENT, [COMPLETED PMS TBL].[DATE COMPLETED], [REQUIRED PM TBL].FREQ, [COMPLETED PMS TBL].[REQUIRED PM ID]
FROM [COMPLETED PMS TBL] RIGHT JOIN [REQUIRED PM TBL] ON [COMPLETED PMS TBL].[REQUIRED PM ID] = [REQUIRED PM TBL].[REQUIRED PM ID]
WHERE ((([REQUIRED PM TBL].[OUT OF SERVICE])=False))
GROUP BY [REQUIRED PM TBL].EQUIPMENT, [COMPLETED PMS TBL].[DATE COMPLETED], [REQUIRED PM TBL].FREQ, [COMPLETED PMS TBL].[REQUIRED PM ID]
ORDER BY [REQUIRED PM TBL].EQUIPMENT, [COMPLETED PMS TBL].[REQUIRED PM ID] DESC , [COMPLETED PMS TBL].[DATE COMPLETED] DESC;




if you have saved the query you posed (say Query1),
you used it to create new query:

SELECT * FROM Query1 WHERE Query1.[DATE COMPLETED] IN (SELECT TOP 2 T1.[DATE COMPLETED] FROM Query1 WHERE T1.[EQUIPMENT]=Query1.[Equipment] ORDER BY T1.[DATE COMPLETED] DESC)

 

[froggiebeckie]

Reading through all 3 responses led me to fixing some of the design errors I committed when creating this db and its innards.

With your input, I believe I can move forward from here, now.

Thanks so much, I've been at a dead stop for several days on this thing and am glad to be moving forward again.


Again, thanks all.

BeckieO