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.
4 1431
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
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.
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.
Thanks for all your help. I have to group it before getting the max
thanks again,
DM
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Daniel Ng |
last post by:
Thanks for the 2 guys how hep to to solve to count the number of recount
in forum. I'm done wif that. Got 1 more question..
Example
Record 1: ID 2 VALUE 2
Record 2: ID 3 VALUE 3
Record 3: ID 2...
|
by: Alistair |
last post by:
hi peoples and masterful gurus (a little bit of grovelling never hurt
anyone...apart from the sore knees)
I'm after a little bit of advice on how to do something.
basically a history of the...
|
by: Alain Guichaoua |
last post by:
Good evening to all
Here is my problem :
I have a form with a subform. They are linked.
When I open the form I would like the subform to reach its last
record.
I tried the method...
|
by: sixsoccer |
last post by:
I have built a database with a <Mainform> and a <Subform>. My problem is
twofold.
1. My subform is set as a continuos form with AllowAddiotions set to NO (ie.
a list of Issues to the client on...
|
by: Jaap |
last post by:
Hi,
Situation is as follows:
- A report has a subreport
- The subreport has records of which two will fit on a page
- When the complete report is printed, the last record in the subreport...
|
by: Geoff O |
last post by:
I have a try catch block in a web service that executes ADO.NET code that
interfaces with an Oracle database. In addition, I have a try catch block in
the client around the call to the web...
|
by: Derek Schuff |
last post by:
I'm sorry if this is a FAQ or on an easily-accesible "RTFM" style page, but
i couldnt find it.
I have some code like this:
for line in f:
toks = line.split()
try:
if int(toks,16) ==...
|
by: Eric Lilja |
last post by:
Hello, consider this complete program:
#include <iostream>
#include <string>
using std::cout;
using std::endl;
using std::string;
class Hanna
{
|
by: michel.ank |
last post by:
Hi,
I'm using the class PrintLines and my last record of page aren't with
the borders.
Somebody can help me?
Thanks,
|
by: Christo |
last post by:
I have this script for showing news on a page, but i want it to only
show the last 10 records, as in the 10 records that were added to the
database last. the script shows the entries in descending...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |