Help Please with TOP - I think...

[Mike]

I am assuming that TOP is what I should use for this
query, but I'm having a hard time with it.
I have an equipment history table that represts repair
detail about my equipment. I need, for each
equipmnet_num, the most recent record (equip_num,
workorder, date, hours).
Using Max and min, or first and last has not returned
consistent results. If i could sort by date and group by
equip_num, and return ONLY one record (the latest one
where the date is greatest) for each equip_num, I'd have
my results, but how do I do this? My result needs to
include the fieldsp; equip_num, workorder, date, hours
and one row for each equip_num in the history table.
Can anyone help with this?
much thanks,
mike

 

[Matthew DeAngelis]

I am assuming that TOP is what I should use for this
query, but I'm having a hard time with it.
I have an equipment history table that represts repair
detail about my equipment. I need, for each
equipmnet_num, the most recent record (equip_num,
workorder, date, hours).
Using Max and min, or first and last has not returned
consistent results. If i could sort by date and group by
equip_num, and return ONLY one record (the latest one
where the date is greatest) for each equip_num, I'd have
my results, but how do I do this? My result needs to
include the fieldsp; equip_num, workorder, date, hours
and one row for each equip_num in the history table.
Can anyone help with this?
much thanks,
mike

First, you will create a SELECT Query with the fields you want (I would
just go directly to Design View). Sort the Date field in Descending
order, so that your most recent record will return on the top. Then,
switch over to SQL view and type " TOP 1" after SELECT. It should do
what you like. (Note: it will return more than one record if there is
more than one of the most recent date)


HTH,
Matt

 

[Michael Keating]

Hi,

If you are trying to create this query on just the repair details table,
it'll be a bugger

If you join your repair details table to your equipment list table on
equip_num, then set MAX for the date and group by for the others, you should
get what you're after.

MFK.

 

[mike]

That doesn't work. It only gives me the top record in
the the table, not the top record for each Equipment in
the table.

 

[Matthew DeAngelis]

That doesn't work. It only gives me the top record in
the the table, not the top record for each Equipment in
the table.

My apologies...I misread your question.

While I do not know of a way to just get a list of all the top records,
if you are displaying your equipment records in a form, you can set the
equipment_num criteria in the query to the equipment_num field on the
form (if you are not currently displaying it, you can put an invisible,
disabled textbox anywhere for the query to reference). This will only
display the most recent workorder for the record you have open.

Alternately, you can use the query I helped you build and use it as the
source for a subform; if you use the wizard, you will get an option to
display only those work orders that correspond with the equipment_num
on the main form.


I hope this response is more helpful,
Matt

 

[Roger Carlson]

MAX will work for this, but not in the way you might think. The aggregate
functions aggregate each colums SEPARATELY, so you can't return the Max of
one value and all the fields that correspond to that record.

There are two solution to this 1) use a subquery that returns all the Max
values or 2) create a query that returns the Max values and then JOIN that
back to the original table.

On my website is a small sample database called "MaxQueryProblem.mdb" which
illustrates the problem and both solutions.

 

[Michael Keating]

See how wrong I can be ?? <G>

Having read Roger Carlson's reply, and doing a little testing, I came up
with some SQL that looks like this

SELECT Repair_Details.Repair_id,
Repair_Details.Equip_ID,
Repair_Details.Repair_Date,
Repair_Details.Hours
FROM [SELECT Repair_Details.Equip_ID,
Max(Repair_Details.Repair_Date) AS MaxOfRepair_Date
FROM Equipment_List
INNER JOIN Repair_Details
ON Equipment_List.Equip_Id = Repair_Details.Equip_ID
GROUP BY Repair_Details.Equip_ID]. AS MaxDates
INNER JOIN Repair_Details
ON MaxDates.MaxOfRepair_Date = Repair_Details.Repair_Date;

Hope it hasn't got too mashed to get the idea .. this is a sub-query, as
suggested by Roger .. and in my tests gave correct results.

maybe this WILL help .. this time

MFK.