Help with a last 5 record query

[chrisl1471]

Hello all, I am in need of some help building what I believe is a complex query for a DB I am building.

I have a list of "TV/SHIP" being built in a plant, these "items" go through various "AreaGroups" on various dates "complete". All of this data exists in a single table within the DB.

What I am attempting to do is build a query that does the following.

User inputs their "areagroup", the query runs filtering the LAST 5 "tv/ship" that have a date "Complete" < Date() and = to the user input areagroup.

I would then use that query data, to filter some other various data records to show a report/chart showing how long project x took on "items" 5; 4; 3 and so on for comparison sorted by the oldest one to the most recent "item" to have completed the "Station". In theory this would show if the "areagroups" are they improving? Sustaining? etc.

I do not believe I can simply use LAST in my query because each item goes through each station, so the last TV/Ship may or may not be through the last station at the time the query is ran. I have added an attachment that shows what I mean (I hope).

In the attachment the 1st column "TV/SHIP" shows 2 different ships, as you can see both go through various areagroups and complete on a date....

So if I entered 475-1A, I would want the query to go find the last 5 "Ships" where the complete date < Date() and = areagroup entered.

 

[arnelgp]

Is there only ine tyoe if ship or many?
If many do you mean 5 type if ship that enters a grroup which is less than today?

 

[chrisl1471]

Many tv/ship

As in the attachment 2AF:0028 is ALL 1 single TV/SHIP

You have a 1 to Many relationship

1 areagroup per TV/SHIP

each areagroup & TV/Ship combination has 1 "complete" date

 

[Mark_]

So your asking how to do

SELECT TOP 5 FROM <FILE> WHERE FILE.[areagroup] = Me.areagroup

in effect?


Edit the queries definition, but use SQL view. Put " TOP 5 " after the "SELECT" and save.

 

[arnelgp]

Yes on yiur sample only one, but are there many and you want 5 diff ship that last entered/completed on a particular area with date less than today?

 

[chrisl1471]

arnelgp: CORRECT!

 

[arnelgp]

here is a sample copy and paste in Query Desing (SQL View).
Substitute the name of your table to "yourTable".


SELECT TOP 5 [TV/SHIP], [COMPLETE], [AREAGROUP] FROM (SELECT yourTable.AREAGROUP, yourTable.[TV/SHIP], (SELECT TOP 1 T1.COMPLETE FROM yourTable AS T1 WHERE T1.AREAGROUP=yourTable.AREAGROUP AND T1.[TV/SHIP]=yourTable.[TV/SHIP] AND T1.COMPLETE < DATE() ORDER BY T1.COMPLETE DESC) AS COMPLETE
FROM yourTable
GROUP BY yourTable.AREAGROUP, yourTable.[TV/SHIP]) AS T2 WHERE T2.AREAGROUP=[WHICH AREA GROUP];

 

[arnelgp]

Correction!

SELECT TOP 5 [TV/SHIP], [COMPLETE], [AREAGROUP] FROM (SELECT yourTable.AREAGROUP, yourTable.[TV/SHIP], (SELECT TOP 1 T1.COMPLETE FROM yourTable AS T1 WHERE T1.AREAGROUP=yourTable.AREAGROUP AND T1.[TV/SHIP]=yourTable.[TV/SHIP] AND T1.COMPLETE < DATE() ORDER BY T1.COMPLETE DESC) AS COMPLETE
FROM yourTable
GROUP BY yourTable.AREAGROUP, yourTable.[TV/SHIP]) AS T2 WHERE T2.COMPLETE < DATE() AND T2.AREAGROUP=[WHICH AREA GROUP];

 

[chrisl1471]

This works but does not return the most recent 5, as I thought it wouldn't....The result goes back to 2014 which is not what I am looking for....I am looking for the LAST (most recent) 5 aircraft that completed the input areagroup

 

[arnelgp]

no, try this:

SELECT TOP 5 T3.[TV/SHIP],T3.[AREAGROUP],T3.MXCOMPLETE AS COMPLETE FROM (SELECT T1.AREAGROUP, T1.[TV/SHIP], MAX(COMPLETE) AS MXCOMPLETE FROM yourTable AS T1 WHERE T1.COMPLETE < DATE() GROUP BY T1.[TV/SHIP],T1.[AREAGROUP] ORDER BY 3,2,1 DESC) AS T3 WHERE T3.[MXCOMPLETE] < DATE() AND T3.[AREAGROUP] = [WHICH AREAGROUP]