How to select the latest revision number record

[Craig]

Hi,

I have a table with fields: Task Name, Revision Number, Effective Date,
Descr, etc

So it looks something like:

Record 1: "Task A",1, 1/1/08,"abc"
Record 2: "Task A",2,1/1/09,"xyz"
Record 3: "Task B",1,1/1/02,"efg"
Record 4: "Task B",2,2/3/01,"aaa"
Record 5: "Task C",1,1/2/99,"xxx"

I want to be able to select records with the latest (highest) revision
number whenever there are two records with the same task name.......

so, based on the above 5 records, I want to only get these 3 in my query
results as follows:

Record 2: "Task A",2,1/1/09,"xyz"
Record 4: "Task B",2,2/3/01,"aaa"
Record 5: "Task C",1,1/2/99,"xxx"

(records 1 and 3 would not be selected as they are not the latest (highest)
revision numbers of the relevant task)


I tried the Totals Row with
"Group by" for Task Name,
"MAX" for Revision Number,
"LAST" for Effective Date
"LAST" for Description

but thats not reliable as when you make changes to effective date and
description fields in the table in those records that should not be selected
in the query, the latest info i entered , ended up at times next to the
latest revision number in the selected records.

Any ideas? Much appreciated!!

Craig

 

[geebee]

hi,

Write a query that simply sorts the records by revision number, from highest
to lowest (in DESC order). Save that query (as lets say... qry_sub). Then
create a new query (called let's say qry_main). The source for qry_main
would be qry_sub AND the table listing all the records. There should be an
inner join or a left join between the qry_sub and the table, on the revision
number. Then you could use your MAX function in a query column.

Hope this helps,
geebee

 

[Craig]

Thank you!!! I will try it...sounds good!

Craig

 

[Craig]

Hi

I tried the proposed solution but it did not work as when I used the Last
aggregate function in conjunction with the max function after creating the
queries suggested, it still gave me inaccurate records...picked up last data
for fields marked last, some of which did not go with the max revision number
record.

Can you look at this again...Any help is much appreciated!! Thank you.

 

[Peter Martin]

Don't use LAST (or FIRST), it will almost always fool you and it's not
supported anywhere else. Try this:

SELECT T.*
FROM Task AS T
WHERE (((T.Eff) In (SELECT TOP 1 [Eff] FROM Task WHERE TaskNo=T.TaskNo ORDER
BY [Eff] DESC)));

It has the advantage of being an updateable query, something the version
with MAX in wont be.

Regards,