Help with Function to condense records

[adventurous1]

Oh boy...here's the latest problem for the access newbie.

My dataset has records that look like this:

Field RecordID Created User OldValue NewValue
status 6789 3/1/10 1:25:30 AM SVACS In Prog
status 6789 3/1/10 1:25:30 AM SVACS Accepted
status 6789 2/15/10 8:30:30 PM SVACS Reopened
status 6789 2/15/10 8:30:30 PM SVACS Closed
status 5211 1/5/10 7:30:30 AM KHOS Closed
status 5211 1/5/10 7:30:30 AM KHOS Verified


And I need to transform the result to look like this:

Field RecordID Created User OldValue NewValue
status 6789 3/1/10 1:25:30 AM SVACS Accepted In Prog
status 5211 1/5/10 7:30:30 AM KHOS Verified Closed

Can anyone help me by posting an Access function that will do this transformation?

Thanks!

 

[dhookom]

What are the specifications? What are the rules?
Please take the time to tell us the reasons so we don't have to guess.

Duane
Hook'D on Access
MS Access MVP

 

[adventurous1]

Not sure I understand. What do you mean specifications and rules?

I essentially need to condense multiple records with the same date/time but different old & new values to a single record for a given RecordId.

Thanks.

 

[dhookom]

Rules like what happened to:
status 6789 2/15/10 8:30:30 PM SVACS Reopened
status 6789 2/15/10 8:30:30 PM SVACS Closed


Duane
Hook'D on Access
MS Access MVP

 

[adventurous1]

Only the data for the most recent date/timestamp should be returned. Older records should not be returned as part of the results.

 

[adventurous1]

Correction...!

Only the data for the most recent RecordID and date/timestamp should be returned. Older records for a RecordID should not be returned as part of the results.

 

[dhookom]

What do you mean specifications and rules?

You seem to have found them. Are the specifications stable now? If so, I think you want to start by creating a totals query of the Field and RecordID with the max of Created. Then join this query to the orginal dataset and again make it a totals query with max of OldValue and NewValue.

Duane
Hook'D on Access
MS Access MVP

 

[adventurous1]

Thanks...I will try that. One question though...Isnt a function more efficient?

 

[dhookom]

I doubt a function would be more efficient than SQL. You are encouraged to try them both and report back ;-)

Duane
Hook'D on Access
MS Access MVP