Using a Make Table query to get MAX Date??

[msiqbal]

I am having some major issues with this component of my db. I will try to be as detailed as possible, sorry if it is confusing.

I have two Tables -

Transaction Log (Transaction ID, Serial Number, Model, Date, Time, Total Prints, Total Copies, Total Scans, and Total Count) and

Main (Serial Number, Model, Location, Date, Time, Total Print, Total Copies, Total Scans, and Total Count)

Transaction ID is an Auto Number. Data is coming into this table constantly. I want to take only the most recent data and rewrite the Main table with that data. The Main table is my working table.

Currently I am using a Make Table Query. I use Between Date()-6 And Date() for the criteria but that only gives me current for a week. If I have more than one entry for that week or no entry at all, it doesn't work. Is there a function that will only give me the most current data? I only want the Main Table to display the most recent data from the Transaction Log table.

Thanks in advance for your input!

 

[Pat Hartman]

You need to do this in two parts - first find the max date/time and second find the rest of the info. You will also need to merge date and time (I hope that is not really their column names) into a single date/time column. Query1 will not work as you expect if date and time are in separate columns.

query1:
Select SerialNumber, Model, Max(TranDate) as MaxTranDate
From tblTransactionLog
Group By SerialNumber, Model;

query2:
Select t.*
From tblTransactionLog as t Inner Join query1 as q on t.SerialNumber = q.SerialNumber AND t.Model = q.Model AND t.TranDate = q.MaxTranDate;

You can then turn query2 into a make table query if you want.

BTW, avoid names with embedded spaces or special characters.

 

[msiqbal]

I am getting a missing operator error on query 2. Here is my code -
query 1
SELECT [Transaction Log].[Serial Number], [Transaction Log].Model, Max([Transaction Log].Date) AS MaxOfDate
FROM [Transaction Log]
GROUP BY [Transaction Log].[Serial Number], [Transaction Log].Model;


Query 2
SELECT t.*
From [Transaction Log] as t Inner Join query1 as q on t.[Serial Number] = q.[Serial Number] AND t.[Model] = q.[Model] AND t.Date = q.Max[Date];