By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,985 Members | 2,884 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,985 IT Pros & Developers. It's quick & easy.

Catching the Last Record

ddtpmyra
100+
P: 333
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.
Sep 23 '08 #1
Share this Question
Share on Google+
4 Replies


Expert Mod 2.5K+
P: 2,545
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

Expand|Select|Wrap|Line Numbers
  1. SELECT Max([cmr_filestorage]![date_add]) AS date_add, cmr_filestorage.author, cmr_filestorage.requestor, cmr_filestorage.category, cmr_filestorage.description, cmr_filestorage.file
  2. FROM cmr_filestorage
  3. GROUP BY
  4. 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
Sep 23 '08 #2

aas4mis
P: 97
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.
Sep 23 '08 #3

aas4mis
P: 97
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

Expand|Select|Wrap|Line Numbers
  1. SELECT Max([cmr_filestorage]![date_add]) AS date_add, cmr_filestorage.author, cmr_filestorage.requestor, cmr_filestorage.category, cmr_filestorage.description, cmr_filestorage.file
  2. FROM cmr_filestorage
  3. GROUP BY
  4. 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.
Sep 23 '08 #4

ddtpmyra
100+
P: 333
Thanks for all your help. I have to group it before getting the max

thanks again,
DM
Sep 24 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.