Query to pull most recent of several dated fields 1

[kentwoodjean]

Have a question on how best to set up a query. Have a table with information imported from one of our mainframe systems. The primary fields I am using on my query are:

Recvd Date
Contract Number
Name
Group Num
DeskCd
CaseCd
Status1
StatusDt1
Status2
StatusDt2
Status3
StatusDt3
Status4
StatusDt4
ETC
The status and StatusDt number all the way to 12. The pupose of the Status information is to show the current location of the case and the date it was sent. Sometimes the case can be sent 1 place (status1, StatusDt1) and returned (status2, StatusDt2) and sent to another location (status3, StatusDt3) etc.

I need to indicate on my query the most recent, or the last status and StatusDt of each open case. With that information I will also identify how long it has been in that location by using the current date minus the StatusDt.

Currently my query has all the status and StatusDt fields listed up to 12. Many of them are Null since the number of entries will depend on the number of times case was sent out and received back. I know that I cannot show Max for each of them to pull the most recently dated one. Does anyone have any ideas on how to set this up so my query will pull correctly?

 

[dhookom]

You can normalize your data with a union query.

SELECT PrimaryKeyField, Status1 as Status, StatusDt1 as StatusDate, 1 as Stat
FROM tblMainFrame
WHERE Status1 is not Null
UNION
SELECT PrimaryKeyField, Status2 , StatusDt2, 2
FROM tblMainFrame
WHERE Status2 is not Null
UNION
SELECT PrimaryKeyField, Status3 , StatusDt3, 3
FROM tblMainFrame
WHERE Status3 is not Null
UNION
...etc...

You can then create a query search for the Max(StatusDate) from the union query.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[kentwoodjean]

Sorry didn't get back to you sooner but was out of town on travel. Your suggestion worked well and does just what I want it to. Thanks for the advise and direction.