Max Date under total brings back all records still

[Madmax]

Trying to get this query to give me the last date[Test Date] Record for each person [DBPTID] from a PTweb Table with a primary key for [Table Key]. I have tried Max and last both and can't get them to work. Ideas? Please let me know this is due today at 1300.

This query is being made so I can make an update query from it to update the TBL_PTID table from the TBL_PTweb to reflect the last time they took a PT test. But I still want all the times they took it in the TBL_PTweb for historical and other reports.

SQL below

SELECT [TBL-PTWEB].[Test Date], Max([TBL-PTWEB].[Test Date]) AS [MaxOfTest Date], [TBL-PTWEB].DBPTID, [TBL-PTWEB].[Body Mass Index], [TBL-PTWEB].Weight, [TBL-PTWEB].Height, [TBL-PTWEB].Cardio, [TBL-PTWEB].[Abdominal Circumference], [TBL-PTWEB].Pushups, [TBL-PTWEB].[Sit-Ups], [TBL-PTWEB].Mode, [TBL-PTWEB].Score, [TBL-PTWEB].[Test Due], [TBL-PTWEB].[Fitness Level], [TBL-PTWEB].[Table Key]
FROM [TBL-PTWEB]
GROUP BY [TBL-PTWEB].[Test Date], [TBL-PTWEB].DBPTID, [TBL-PTWEB].[Body Mass Index], [TBL-PTWEB].Weight, [TBL-PTWEB].Height, [TBL-PTWEB].Cardio, [TBL-PTWEB].[Abdominal Circumference], [TBL-PTWEB].Pushups, [TBL-PTWEB].[Sit-Ups], [TBL-PTWEB].Mode, [TBL-PTWEB].Score, [TBL-PTWEB].[Test Due], [TBL-PTWEB].[Fitness Level], [TBL-PTWEB].[Table Key]
ORDER BY [TBL-PTWEB].DBPTID;

[orange]

To get the last date[Test Date] Record for each person [DBPTID] try

SELECT
[TBL-PTWEB].DBPTID
,Max([TBL-PTWEB].[Test Date]) AS [MaxOfTest Date]
FROM [TBL-PTWEB]
GROUP BY [TBL-PTWEB].DBPTID
ORDER BY [TBL-PTWEB].DBPTID;

I think you are including too many fields and are making the issue more complex than necessary. You are forcing a query to group at levels that are "outside" your initial statement.

[Madmax]

Okay! This is great except, I dont have any of the other information displayed. However, I should be able to run a query off this query to pull the rest of the information. Going to try it thank you so far!

[Madmax]

SELECT [TBL-PTWEB].DBPTID, Max([TBL-PTWEB].[Test Date]) AS [MaxOfTest Date], [TBL-PTWEB].[Fitness Level]
FROM [TBL-PTWEB]
GROUP BY [TBL-PTWEB].DBPTID, [TBL-PTWEB].[Fitness Level]
ORDER BY [TBL-PTWEB].DBPTID;

added one field, Next trying to add an update for the following info

Fitness level needs to update on [TBL_PTID].[Current PT Status]
MaxOfTest Date needs to update on [TBL_PTID].[Last Date of PT Test]

Can that be done inside this or will it botch it up?

[Madmax]

SELECT [TBL-PTWEB].DBPTID, Max([TBL-PTWEB].[Test Date]) AS [MaxOfTest Date], [TBL-PTWEB].[Fitness Level]
UPDATE [TBL_PTID] SET [Last Date of PT Test] =[Update To PT Status 1].[MaxOfTest Date], [Current PT Status]=[Update To PT Status 1].[Fitness Level]
FROM [TBL-PTWEB]
GROUP BY [TBL-PTWEB].DBPTID, [TBL-PTWEB].[Fitness Level]
ORDER BY [TBL-PTWEB].DBPTID;

Missing an operator in the update field. Dont know what though

[orange]

Are you working with a test/development database?
Update queries can have unintended affects if done incorrectly.

Before the Update query,were you able to get the "other" info you mentioned in Post #3?

[Madmax]

Found my answer finally.

Basically you cant use an update query with a Grouped By query(or Max in this case). The website I found http://www.fmsinc.com/microsoftacces...ble/index.html

Talks about it at length. The answer is to have your Max query make a temp table and create another query from that table to update your needed info. The combine the two in a macro to be run one after the other. alot of clicking yes but its fine for once a month.

Thanks for bouncing my head around this morning. I am down to 3.5 hours and all I have left are reports!

[orange]

Glad you got it working.