Select records with Most Recent Eff Dt and Sort by workday 1

[IngDep]

Having a challenge modifying the following MS Access select query so that I receive:

1) Records with the most recent PROVIDER_ADDR.EFF_DT
2) PROV_TO_HOUR > 5:00 PM
3) Sorted by Day of week - e.g. Sunday, Monday, Tuesday...

Insight greathly appreciated!

SELECT PROVIDER.PROV_ID
, PROVIDER.NAME
, PROVIDER_OFF_HRS.PROV_DAYS
, PROVIDER_OFF_HRS.PROV_FROM_HOUR
, PROVIDER_OFF_HRS.PROV_TO_HOUR

, PROVIDER_ADDR.EFF_DT
, PROVIDER_ADDR.ADDR1

FROM PROVIDER_ADDR
INNER JOIN (PROVIDER_OFF_HRS
INNER JOIN PROVIDER
ON PROVIDER_OFF_HRS.PROV_ID = PROVIDER.PROV_ID)
ON FACETS_CMC_PRAD_ADDRESS.PRAD_ID = PROVIDER_OFF_HRS.PRAD_ID
WHERE (((PROVIDER_OFF_HRS.PRAD_ID)="5412abc"))
ORDER BY PROVIDER_OFF_HRS.PROF_DAYS;

Current Query Results look like this:
PROV_ID---NAME-----DAYS-----FROM------TO-------EFF_DT...
123456----JSMITH---MONDAY---8:30 AM---5:00 PM--10/1/07
123456----JSMITH---TUES-----8:30 AM---5:00 PM--10/1/07
123456----JSMITH---MONDAY---8:30 AM---8:00 PM--12/1/07
123456----JSMITH---TUES-----8:30 AM---8:00 PM--12/1/07

 

[PHV]

Current Query Results look like this
And what is the corresponding expected result ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[IngDep]

For this example, it would be only those records where effective date is "12/1/07."

Even though the display of the current query results above have the records sorted by weekday for the four records, all of the 9000+ records in the entire query result set is not sorted by weekday.

 

[PHV]

Could you please post your real SQL code ?
...
FROM PROVIDER_ADDR
INNER JOIN (PROVIDER_OFF_HRS
INNER JOIN PROVIDER
ON PROVIDER_OFF_HRS.PROV_ID = PROVIDER.PROV_ID)
ON [!]FACETS_CMC_PRAD_ADDRESS[/!].PRAD_ID = PROVIDER_OFF_HRS.PRAD_ID
...

 

[PHV]

Just a guess:
SELECT P.PROV_ID, P.NAME, H.PROV_DAYS, H.PROV_FROM_HOUR, H.PROV_TO_HOUR, A.EFF_DT, A.ADDR1
FROM ((PROVIDER AS P
INNER JOIN PROVIDER_OFF_HRS AS H ON P.PROV_ID = H.PROV_ID)
INNER JOIN PROVIDER_ADDR AS A ON H.PRAD_ID = A.PRAD_ID)
INNER JOIN (SELECT PRAD_ID, Max(EFF_DT) AS LastDate FROM PROVIDER_ADDR GROUP BY PRAD_ID
) AS L ON A.PRAD_ID = L.PRAD_ID AND A.EFF_DT = L.LastDate
WHERE H.PROV_TO_HOUR > #05:00 PM#
ORDER BY Switch(H.PROV_DAYS='Sunday',1, H.PROV_DAYS='Monday',2, H.PROV_DAYS='Tuesday',3, H.PROV_DAYS='Wednesday',4, H.PROV_DAYS='Thursday',5, H.PROV_DAYS='Friday',6, H.PROV_DAYS='Saturday',7)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[IngDep]

Error message received is that the sql is not properly ended.

Trying as a passthrough query to a Oracle Db.

 

[IngDep]

The error message persists.

Latest iteration is as follows.

(Currently reviewing the line "WHERE H.PROV_TO_HOUR" > "5:00 PM" (The field "H.PROV_TO_HOUR" is a text field.)

Any additional insight is greatly appreciated.

SELECT P.PROV_ID, P.NAME, H.PROV_DAYS, H.PROV_FROM_HOUR, H.PROV_TO_HOUR, A.EFF_DT, A.ADDR1
FROM ((PROVIDER AS P
INNER JOIN PROVIDER_OFF_HRS AS H ON P.PROV_ID = H.PROV_ID)
INNER JOIN PROVIDER_ADDR AS A ON H.PRAD_ID = A.PRAD_ID)
INNER JOIN (SELECT PRAD_ID, Max(EFF_DT) AS LastDate FROM PROVIDER_ADDR GROUP BY PRAD_ID
) AS L ON A.PRAD_ID = L.PRAD_ID AND A.EFF_DT = L.LastDate
WHERE H.PROV_TO_HOUR > "5:00 PM"
ORDER BY
CASE WHEN OFC.PROF_DAYS = 'Sunday' THEN 1
WHEN OFC.PROF_DAYS = 'Monday' THEN 2
WHEN OFC.PROF_DAYS = 'Tuesday' THEN 3
WHEN OFC.PROF_DAYS = 'Wednesday' THEN 4
WHEN OFC.PROF_DAYS = 'Thursday' THEN 5
WHEN OFC.PROF_DAYS = 'Friday' THEN 6
WHEN OFC.PROF_DAYS = 'Saturday' THEN 7
END;

 

[IngDep]

Still receiving the error message: ORA-00933 Sql statement not properly ended.

Any insight?