Query to list only one record per provider does not work 1

[sjck]

Hello,

I am trying to write a query that lists only the most current record for each provider ID. The query does not work when 2 statuses have the same effective date. In this case I need the Max date and the Max Order.

I have tried several iterations for the query, using a top 1, a subquery for order etc... But nothing seems to work.

Below is the query that selects the most current date record and the results.

SELECT A.ProviderID, A.StatusUpdateID, A.Status, A.StatusDate, A.Notes, B.StatusCategory, B.StatusName, B.Order
FROM tblStatus AS B RIGHT JOIN tblProviderStatus AS A ON B.StatusID = A.Status
WHERE( ((A.StatusDate)=(Select MAX(StatusDate) From ProviderStatus As X
Where X.ProviderID = A.ProviderID)))
ORDER BY A.ProviderID, A.StatusDate DESC , B.Order DESC;


ProviderID StatusUpdateID Status StatusDate Notes StatusCategory StatusName Order
794 1266 Sent to Contractor 3/21/2006 Sent to Contractor to pursue. Open Sent to Contractor 2
794 1265 Form Received 3/21/2006 Form Received Open Form Received 1

What I need is the first record to display (order = 2)

Any help would be appreciated.

 

[PHV]

Doing any test on B.Order may defeat the OUTER join ...
Isn't the Max StatusUpdateID a good candidate ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sjck]

Thank you for your quick response

Initially that is what I tried however if a person forgets to add a status and later goes back to correct (e.g. entered status for 3/26 and then realized the status for 3/22 was not entered) the latest status is incorrect, which is why I added the order column. The order column is the process flow, for example the nomination cannot be sent to the contractor (order 2) before the form is received (order 1) - however as it is now the form received could have a larger StatusUpdateID

 

[PHV]

So, again, why an outer join ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sjck]

It does not matter how I do it -- its just I found an example on the forum that returned the results that I needed (when using the Max StatusUpdateID). I was trying to modify it to order by date and order. I am a novice, so I am limited in my knowledge. I try to use the forum to model my queries before I post.

If there is a better way to do it, I will be glad to try it.

Thanks again

 

[PHV]

One way:
SELECT A.ProviderID, A.StatusUpdateID, A.Status, A.StatusDate, A.Notes, B.StatusCategory, B.StatusName, B.Order
FROM (tblProviderStatus AS A
INNER JOIN tblStatus AS B ON A.Status = B.StatusID)
INNER JOIN (
SELECT X.ProviderID, MAX(Format(X.StatusDate,'yyyymmdd') & Format(Y.Order,'00')) AS LastDateOrder
FROM tblProviderStatus AS X INNER JOIN tblStatus AS Y ON X.Status = Y.StatusID GROUP BY X.ProviderID
) AS L ON A.ProviderID = L.ProviderID
WHERE Format(A.StatusDate,'yyyymmdd') & Format(B.Order,'00') = L.LastDateOrder
ORDER BY A.ProviderID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sjck]

Thank you so much -- It worked like a charm!!!

 

[sjck]

One more question -- when I go to save the query -- I receive the error message Microsoft Access has encountered a problem and needs to close. We are sorry for the inconveience. I have compacted and reparied the database several time -- I also created a new database an imported all my table and queries, and opened a new query for this one?

The query works great until I go to save it. Any Ideas?

 

[PHV]

Create a query named, say, qryLastDateOrder:
SELECT X.ProviderID, MAX(Format(X.StatusDate,'yyyymmdd') & Format(Y.Order,'00')) AS LastDateOrder
FROM tblProviderStatus AS X INNER JOIN tblStatus AS Y ON X.Status = Y.StatusID GROUP BY X.ProviderID

And now try to save this query:
SELECT A.ProviderID, A.StatusUpdateID, A.Status, A.StatusDate, A.Notes, B.StatusCategory, B.StatusName, B.Order
FROM (tblProviderStatus AS A
INNER JOIN tblStatus AS B ON A.Status = B.StatusID)
INNER JOIN qryLastDateOrder AS L ON A.ProviderID = L.ProviderID
WHERE Format(A.StatusDate,'yyyymmdd') & Format(B.Order,'00') = L.LastDateOrder
ORDER BY A.ProviderID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886