Re: DateDiff

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Joanne,

The purpose of the GROUP BY clause is to perform some sort of aggregation on some field. Your SELECT clause has no aggregation, hence the error. Alternatively, you can add the other fields from your SELECT clause to the GROUP BY clause.

I seem to recall from your earlier issue that there might be multiple rows for each employee, right? If so, then this might work:

SELECT tblOLPDays.[Employee ID], tblOLPDays.[OLP Begin Date],
tblOLPDays.[OLP End Date], DateDiff("d",[OLP Begin Date],[OLP End Date]) AS
[Total Days]
FROM tblOLPDays
WHERE (((tblOLPDays.[Type of Day])<>"carryover"))
GROUP BY tblOLPDays.[Employee ID], tblOLPDays.[OLP Begin Date],
tblOLPDays.[OLP End Date], DateDiff("d",[OLP Begin Date],[OLP End Date])
ORDER BY tblOLPDays.[Employee ID];

If not, could you post a few lines of sample data and the result you are looking for?



Joanne wrote:
Hello,
Here is the query:
SELECT tblOLPDays.[Employee ID], tblOLPDays.[OLP Begin Date], tblOLPDays.[OLP End Date], DateDiff("d",[OLP Begin Date],[OLP End Date]) AS [Total Days]
FROM tblOLPDays
WHERE (((tblOLPDays.[Type of Day])<>"carryover"))
GROUP BY tblOLPDays.[Employee ID];

When I take out the group by Employee id, the query runs fine. But I would like to have each employee listed separately. Isn't that possible with this query? Thank you.

"Bruce Meneghin" wrote:

Can you list the whole query? you get this message when you have a GROUP BY clause in the query done incorrectly.
"Joanne" wrote:

Hello,
I have set up a query that runs against a table where I've listed vacation time for each employee. The query contains a new field with the following expression:

Total Days: DateDiff("d",[OLP Begin Date],[OLP End Date]) I have also included from my table, the fields Employee ID (group by), OLP Begin Date and OLP end date. OLP begin and end dates are formatted as dates. When I run the query, I get the following error
"You tried to execute a query that does not include the specified expression 'OLP Begin Date' as part of an aggregate function. I have been getting a lot of assistance from Smartin on setting up this database. I hesitate to yet again ask him a stupid question, so I am posting this to anyone who can help. Thanks so much.




--
Smartin
.

Relevant Pages

  • Re: SELECT in a JOIN query results in Syntax Error in FROM clause
    ... perhaps you should add the contracts table into the query in the FROM clause. ... FROM tblEmp INNER JOIN tblContracts ... I've copied your query into my application, changed the table and field names to match the original database and find that it still fires this 'Syntax Error in FROM Clause' ... the query should find which employees have a contract and add a record for each employee ID ...
    (microsoft.public.access.queries)
  • Re: SELECT in a JOIN query results in Syntax Error in FROM clause
    ... You refer to tblArbctr.WerkNemer in the SELECT clause and to tblArbCtr.WrnID in the Join Clause. ... FROM tblEmp INNER JOIN tblContracts ... I've copied your query into my application, changed the table and field names to match the original database and find that it still fires this 'Syntax Error in FROM Clause' ... the query should find which employees have a contract and add a record for each employee ID ...
    (microsoft.public.access.queries)
  • Re: DateDiff
    ... Your SELECT clause has no aggregation, ... When I take out the group by Employee id, the query runs fine. ... OLP end date. ...
    (microsoft.public.access.queries)
  • Re: Sql aggregate function
    ... >clause filters records AFTER they have been grouped. ... >FROM Employee ... >To create the Where clause above in the query builder, ... >TransactionDate, and Amount. ...
    (microsoft.public.access.queries)
  • Re: display problem when query is empty!?
    ... the better your query will perform. ... If the field you wish to filter appears in the GROUP BY clause, ... If you wish to limit the resultset based on the result of an aggregation, ... This query will again return a single row ...
    (microsoft.public.inetserver.asp.general)