Catching the Last Record

[ddtpmyra]

I have a table with dates field and Im creating an email alert everytime the table gets updated. I tried to use the MAX([date]) but its not working is there any other way to do this?

sql command
SELECT Max([cmr_filestorage]![date_add]) AS date_add, cmr_filestorage .author, cmr_filestorage .requestor, cmr_filestorage .category, cmr_filestorage .description, cmr_filestorage .file
FROM cmr_filestorage ;


error
You tried to execute a query that does not include the specified expression 'author' as part of aggregate function.

[Stewart Ross]

Hi. For aggregate queries to work you need to use Group By to group on each of the fields which define the range of records you want to aggregate. In the Access query editor you can select grouping on by using View, Totals, or pressing the summation button on the toolbar (the greek letter sigma).

The SQL goes something like

SELECT Max([cmr_filestorage]![date_add]) AS date_add, cmr_filestorage.author, cmr_filestorage.requestor, cmr_filestorage.category, cmr_filestorage.description, cmr_filestorage.file
FROM cmr_filestorage
GROUP BY
cmr_filestorage.author, cmr_filestorage.requestor, cmr_filestorage.category, cmr_filestorage.description, cmr_filestorage.file;

Be aware that the Max calculated is of the value grouped within the field grouping you specify, and this changes if you add or take away fields from the query. If this is not what you need you may need to come up with a two-query calculation using a subquery or the like to do the Max bit.

Try it out and see how it goes.

-Stewart

[aas4mis]

I have a table with dates field and Im creating an email alert everytime the table gets updated. I tried to use the MAX([date]) but its not working is there any other way to do this?

sql command
SELECT Max([cmr_filestorage]![date_add]) AS date_add, cmr_filestorage .author, cmr_filestorage .requestor, cmr_filestorage .category, cmr_filestorage .description, cmr_filestorage .file
FROM cmr_filestorage ;


error
You tried to execute a query that does not include the specified expression 'author' as part of aggregate function.

I know the error doesn't state it but I don't think access likes the use of the date_add alias because a column with that name already exists. Try changing the name of that alias and see what you get.

[aas4mis]

Hi. For aggregate queries to work you need to use Group By to group on each of the fields which define the range of records you want to aggregate. In the Access query editor you can select grouping on by using View, Totals, or pressing the summation button on the toolbar (the greek letter sigma).

The SQL goes something like

SELECT Max([cmr_filestorage]![date_add]) AS date_add, cmr_filestorage.author, cmr_filestorage.requestor, cmr_filestorage.category, cmr_filestorage.description, cmr_filestorage.file
FROM cmr_filestorage
GROUP BY
cmr_filestorage.author, cmr_filestorage.requestor, cmr_filestorage.category, cmr_filestorage.description, cmr_filestorage.file;

Be aware that the Max calculated is of the value grouped within the field grouping you specify, and this changes if you add or take away fields from the query. If this is not what you need you may need to come up with a two-query calculation using a subquery or the like to do the Max bit.

Try it out and see how it goes.

-Stewart

Good call on that GROUP BY, oversight on my part.

[ddtpmyra]

Thanks for all your help. I have to group it before getting the max

thanks again,
DM