Help with last transaction time per day

[The Thing]

Hi There,

I have a table in an Access '97 database called Transactions, which includes the following fields:

TransactionNumber,
VehicleRegistration,
TransactionDate, - for example [02/01/2003 09:58:06]
Waste,
NetWeight.

Multiple transactions may occur for the same vehicle each day at different times of the day.

I need to create a parameterised query - [Start Date] + [End Date] for the parameters, that returns only the last transaction time per day for each vehicle in the table along with the rest of the above mentioned fields for that particular record.

I have tried the suggestions given in this thread
Click Me, but I just can't get it to work. Any help given is much appreciated. Thank you.

 

[namliam]

I asume you know how to use parameters, but try this on for size -- keep in mind i typed it from scratch on the forum, so there might just be a typo or something in here....

SELECT YourTable.*
FROM (
SELECT YourTable.VehicleRegistration, Max(YourTable.TransactionDate) AS MaxTransactionDate
FROM YourTable
GROUP BY YourTable.VehicleRegistration) as MaxTime INNER JOIN YourTable ON MaxTime.MaxTransactionDate = YourTable.TransactionDate AND MaxTime.VehicleRegistration = YourTable.VehicleRegistration;

This will retrieve the last date/time over in the table. To get the last for each day in the period use:
SELECT YourTable.*
FROM (SELECT YourTable.VehicleRegistration, Max(YourTable.TransactionDate) AS MaxTransactionDate
FROM YourTable
GROUP BY YourTable.VehicleRegistration, format(transactiondate,"ddmmyyy")) as MaxTime INNER JOIN YourTable ON MaxTime.MaxTransactionDate = YourTable.TransactionDate AND MaxTime.VehicleRegistration = YourTable.VehicleRegistration;

Hope that helps....

Regards

P.S. Sorry but i didnt take the time to read the other thread.. didnt feel it was necessary (sp?)

 

[The Thing]

Thanks

Thanks namliam, I'll see how I get on with implementing your suggestion.

 

[The Thing]

Syntax error

I've tried running the SQL in the first part of your post , but I'm getting an error which says "Syntax Error In From Clause"

Below is the query copied from Access

SELECT Transaction.*
FROM (
SELECT Transaction.Registration,
Max(Transaction.TransactionDate) AS MaxTransactionDate
FROM Transaction
GROUP BY Transaction.Registration) AS MaxTime INNER JOIN
Transaction ON MaxTime.MaxTransactionDate =
Transaction.TransactionDate AND MaxTime.Registration =
Transaction.Registration;

 

[namliam]

Try:
SELECT Transaction.Registration, Max(Transaction.TransactionDate) AS MaxTransactionDate
FROM [Transaction]
GROUP BY Transaction.Registration;
save that in a query and call it qryMaxTime
then use this
SELECT Transaction.*
FROM qryMaxtime INNER JOIN [Transaction] ON (qryMaxtime.MaxTransactionDate = Transaction.TransactionDate) AND (qryMaxtime.Registration = Transaction.Registration);
Tested and all, did your SQL as well but didnt get an error must be the version or something, i am using AXP at the moment....

Regards