In the past, I have been able to use this expression for a Sum field in an Access query. I have two tables in a query for a simple timesheet application. The two Tables are tblTimesheet (fields: TimesheetID(PK), WeekEnding, Day, Hours, Date, and Paid) and tblPaid (WeekEndingID(PK), Paid). If I remember correctly, I should be able to make a new field by entering: TotalHours: Sum([tblTimesheet].[Hours]) When I do that it says that I'm not including WeekEnding in the aggregate function. If I delete the WeekEnding field then it gives me the same error with whatever the first field displayed is.
Access 2007 Sum Field (1 Viewer)
- Thread starter mjreiman
- Start date
Since this question was not answered, I'll chip in that you are likely not using the Totals feature and "Group By" whatever field you want the hours totalled. If all you require is a total for all fields that have a WeekEnding of a particular date, use that field with "Where" in its Total row; and put the criteria for the date. Don't include any fields that aren't being Grouped or totalled.
I wanted to basically make the query contain the following fields: WeekEnding, TotalHours, and Paid. I prefer not to use things like the totals feature, just a quirk of mine. I discovered that if I go into SQL View and under my SELECT statement and simply include "Sum([Hours]) AS TotalHours" which essentially is the same exact thing, it works perfectly fine.
