Help grouping and checking the max record

[scc]

I have the following SQL statement and there may be multiple records for a TrackingNumber. The TrackingNumber and the DeliveryDate (which has a time stamp in it) make up the unique key. I need to look at the most recent DeliveryDate and then check the criteria, but I'm not sure how to do this.

TIA!!!

SELECT dbo_vw_Stor_Del_2.TrackingNumber, dbo_vw_Stor_Del_2.InboundDate, dbo_vw_Stor_Del_2.InboundUser, dbo_vw_Stor_Del_2.DeliveryDate, dbo_vw_Stor_Del_2.ScannedBy, dbo_vw_Stor_Del_2.Recipient, dbo_vw_Stor_Del_2.Signature, dbo_vw_Stor_Del_2.Carrier, dbo_vw_Stor_Del_2.FirstStop, dbo_vw_Stor_Del_2.InboundComments, dbo_vw_Stor_Del_2.DeliveryBldg, dbo_vw_Stor_Del_2.DeliveryComments, [Enter Date as mm/dd/yyyy] AS Inbound
FROM dbo_vw_Stor_Del_2
WHERE (((dbo_vw_Stor_Del_2.Recipient) Is Null) AND ((dbo_vw_Stor_Del_2.Signature) Is Null) AND ((Format([dbo_vw_Stor_Del_2.InboundDate],'mm/dd/yyyy'))=[Enter Date as mm/dd/yyyy])) OR (((dbo_vw_Stor_Del_2.Signature) Is Null) AND ((Format([dbo_vw_Stor_Del_2.InboundDate],'mm/dd/yyyy'))=[Enter Date as mm/dd/yyyy]) AND ((UCase([Recipient])) Not In ("DRIVER RELEASE","HARD COPY SIGNATURE")))
ORDER BY FirstStop;


By the way, this is an Access link to SQL Server therefore all the parens!

 

[rac2]

SELECT a.TrackingNumber, b.LastDate
...
FROM dbo_vw_Stor_Del_2 a
JOIN (
      SELECT TrackingNumber,
             MAX( InboundDate ) AS "LastDate"
      FROM dbo_vw_Stor_Del_2
      GROUP BY TrackingNumber
     ) b ON b.TrackingNumber = a.TrackingNumber
...

The subquery gives one row for each tracking number. The row has two columns, the tracking number and the most recent inbound date. It can be treated like a table and JOINed to the detail table using the tracking number. The JOIN is accomplished by assigning the alias "b" to the subquery.

 

[scc]

I tried this, but get an error message that says "syntax error in FROM clause" and then highlights the word JOIN in the statement. Remember this is MS Access with a link to a SQL Server table.


SELECT a.TrackingNumber, a.InboundDate, a.InboundUser, b.DeliveryDateTime, a.ScannedBy, a.Recipient, a.Signature, a.Carrier, a.FirstStop, a.InboundComments, a.DeliveryBldg, a.DeliveryComments, [Enter Date as mm/dd/yyyy] AS Inbound
FROM dbo_vw_Stor_Del_2 as a
JOIN (SELECT TrackingNumber, Max(InboundDate) as "DeliveryDateTime"
FROM dbo_vw_Stor_Del_2
GROUP BY TrackingNumber) as b on b.TrackingNumber = a.TrackingNumber
WHERE (((a.Recipient) Is Null) AND ((a.Signature) Is Null) AND ((Format([a.InboundDate],'mm/dd/yyyy'))=[Enter Date as mm/dd/yyyy])) OR (((a.Signature) Is Null) AND ((Format([a.InboundDate],'mm/dd/yyyy'))=[Enter Date as mm/dd/yyyy]) AND ((UCase([Recipient])) Not In ("DRIVER RELEASE","HARD COPY SIGNATURE")));

 

[scc]

As I'm reading back through my post, I realize I didn't state this correctly, so I will correct it. I really need some help, so hopefully someone will come to the rescue...

I have the following SQL statement, however there may be multiple records for a TrackingNumber. The TrackingNumber and the DeliveryDate (which has a time stamp in it) make up the unique key. I need to look at the most recent record for each TrackingNumber and then check the criteria in my where clause, but I'm not sure how to do this.

Somehow I've got to Group the TrackingNumbers and select the most recent TrackingNumber.

I've also simplified the statement below to make it a bit more readable...

PLEASE HELP!

SELECT
a.TrackingNumber, a.InboundDate, a.InboundUser, a.DeliveryDate, a.Recipient, a.Signature, [Enter Date as mm/dd/yyyy] AS Inbound
FROM dbo_vw_Stor_Del_2 AS a
WHERE (((a.Recipient) Is Null) AND ((a.Signature) Is Null) AND ((Format([a.InboundDate],'mm/dd/yyyy'))=[Enter Date as mm/dd/yyyy])) OR (((a.Signature) Is Null) AND ((Format([a.InboundDate],'mm/dd/yyyy'))=[Enter Date as mm/dd/yyyy]) AND ((UCase([Recipient])) Not In ("DRIVER RELEASE","HARD COPY SIGNATURE")));

By the way, this is an Access link to SQL Server therefore all the parens plus need to use SQL that is MS Access friendly.

 

[ESquared]

You need to post this question in the Access forum, if you are actually using Access SQL.

-------------------------------------
• Every joy is beyond all others. The fruit we are eating is always the best fruit of all.
• It is waking that understands sleep and not sleep that understands waking. There is an ignorance of evil that comes from being young: there is a darker ignorance that comes from doing it, as men by sleeping lose the k

 

[cmmrfrds]

MS Access uses a little different syntax for derived tables. [derived table]. The period is part of the syntax.

SELECT a.TrackingNumber, b.LastDate
...
FROM dbo_vw_Stor_Del_2 a
JOIN [
SELECT TrackingNumber,
MAX( InboundDate ) AS "LastDate"
FROM dbo_vw_Stor_Del_2
GROUP BY TrackingNumber
]. b ON b.TrackingNumber = a.TrackingNumber
...

 

[ESquared]

er, um, Access CAN do derived tables?!?!? WOW!!! Fat lot I know.