Multiple Records - Need Max of one Field and Corresponding of Anot

[Tim]

I have a table with the following records:

1. ID
2. Event Code
2. Transaction Create Date/Time (date/time record was created)
3. Event Date/Time (date that an event took place)

There can be multiple records with the same ID and Event Code but with
differing Trasnaction Dates and Event Dates.

I am trying to write a query where I pull out the record that has the most
recent transaction create date and pull the corresponding event date.

I've tried to sort the Transaction Create Date (desc) and use Max. That
gives me the max but when I pull the corresponding Event Date I either get
all of them (if I use first or last then I seem to get a random value instead
of always the one in the corresponding record).

Can anyone help me figure out what I need to do to consistently pull the
matching value from the Event Date when I pull out the Max of the Create Date?

Thanks in advance and I'll provide more detail if necessary.

Tim

 

[Jeff Boyce]

Tim

We can't see "how" you are "trying to write a query" ... please post the SQL
statement.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jerry Whittle]

Select Top 1 [ID],
[Event Code],
[Transaction Create Date/Time],
[Event Date/Time]
FROM TheTable
Order By [Transaction Create Date/Time] DESC,
[Event Date/Time] DESC ;

Note: More than one record can be returned if there are ties.

 

[Tim]

Sorry that would help wouldn't it.

Okay, here is the SQL where I am just trying to pull the corresponding of
the max but unfortunately it gives me back all records and I just want the
record with the max value and it's correspdoning event date.

SELECT TMMGR_SHIPMENT_STATUS.BOL, TMMGR_SHIPMENT_STATUS.STATUS_CODE,
TMMGR_SHIPMENT_STATUS.OCCASION_DATE,
Max(TMMGR_SHIPMENT_STATUS.APPLICATION_CREATE_DATETIME) AS
MaxOfAPPLICATION_CREATE_DATETIME
FROM TMMGR_SHIPMENT_STATUS
GROUP BY TMMGR_SHIPMENT_STATUS.BOL, TMMGR_SHIPMENT_STATUS.STATUS_CODE,
TMMGR_SHIPMENT_STATUS.OCCASION_DATE
HAVING (((TMMGR_SHIPMENT_STATUS.BOL)="00003699681M") AND
((TMMGR_SHIPMENT_STATUS.STATUS_CODE)="AB"))
ORDER BY Max(TMMGR_SHIPMENT_STATUS.APPLICATION_CREATE_DATETIME) DESC;