Problem with summing fields in query

[Tamira]

Hi, I'm a new user since last week.

I made a query that calculates wages based on the pay rate and hours that
are entered in the original table.

This calculates the motnhly wages for each employee, but I need a Totals row
that calculates the sum of all employees' monthly wages to see how much the
company pays out in wages each months.

At first I tried to add columns in the design view with Sum([July Pay]), but
I got a bunch of error messages. Then I discovered the "Totals" row in the
design view. I click "Sum" under each month's pay. But I don't know what to
enter in the Totals row in the columns of "Employee Name" "Employment type"
"Hours worked" ...

Anyway when I don't fill in anything in their Totals row, I get the error
message "You tried to execute a query that does not include the specified
expression "Employee Name" as part of an aggregate function."

When I choose "Group by" or "Where" (I have no clue what these do) for
Employee Name or Hours Worked then it prompts me for a numeric value for
number of Hours Worked before running the query. I don't know the total hours
worked, the query is supposed to calculate that!

Anyway now I'm stuck because I saved changes and now when I want to open my
query to tinker with the calculations in the design view, it won't open at
all anymore, saying "You tried to execute a query that does not include the
specified expression "Employee Name" as part of an aggregate function."

HEEELP!

Thanks!

 

[Michel Walsh]

When you use a TOTAL query, you have either GROUP, either aggregated fields.
As example, if you SUM(pay) and GROUP BY employeeID, you will get the sum of
the column pay, for each employeeID. Doing so, what happen, say, of the
field DateOfThePay? Well, since you SUM the pay, per employee, the date
where each pay did occur become irrelevant. If you want to re-included it in
the SELECT statement, you must either include it in the GROUP BY list of
fields, and then the SUM will occur by employee, by date of pay; or,
aggregate it, such as MIN(dateOfThePay) to return the first time the
employee did receive a pay, as example.


The grid also allow you to use WHERE, that is, the criteria is then applied
BEFORE the aggregate is computed. As example, you can add a WHERE on the
dateOfPay, to only include the pay that occurred between 1/1/2001 and
12/31/2001, as example. But if this field is not, in another column, used as
GROUP BY, or with an AGGREGATE, that field will not be eligible to appear in
the SELECT statement.


You should be able to open your query directly in SQL view, or in design
view WITHOUT first going through a data view (which generates the error, as
you described). If you have problem to modify the statement in SQL view and
cannot open in design view, I suggest that

- you remove the GROUP BY clause (including the list of fields that
follow it)
- you remove all the MIN( ), MAX( ), SUM( ) , while keeping what is
inside the keyword.


So

SELECT f1, MAX(f2), SUM(f3), f4
FROM myTable
GROUP BY f1, f5
ORDER BY f1


become


SELECT f1, f2, f3, f4
FROM myTable
ORDER BY f1



Sure, that only allows you to get the editor back... it does not solve the
first problem you were trying to solve. To solve you problem, I suggest
something like (it is NOT available through a DESIGN view, you have to be in
SQL view):


SELECT employeeID, SUM(pay)
FROM myTable
GROUP BY employeeID

UNION ALL

SELECT Null, SUM(pay)
FROM myTable



The line having nothing under the first column is the line giving the total,
over all employee.



Hoping it may help,
Vanderghast, Access MVP

Hi, I'm a new user since last week.

I made a query that calculates wages based on the pay rate and hours that
are entered in the original table.

This calculates the motnhly wages for each employee, but I need a Totals
row
that calculates the sum of all employees' monthly wages to see how much
the
company pays out in wages each months.

At first I tried to add columns in the design view with Sum([July Pay]),
but
I got a bunch of error messages. Then I discovered the "Totals" row in the
design view. I click "Sum" under each month's pay. But I don't know what
to
enter in the Totals row in the columns of "Employee Name" "Employment
type"
"Hours worked" ...

Anyway when I don't fill in anything in their Totals row, I get the error
message "You tried to execute a query that does not include the specified
expression "Employee Name" as part of an aggregate function."

When I choose "Group by" or "Where" (I have no clue what these do) for
Employee Name or Hours Worked then it prompts me for a numeric value for
number of Hours Worked before running the query. I don't know the total
hours
worked, the query is supposed to calculate that!

Anyway now I'm stuck because I saved changes and now when I want to open
my
query to tinker with the calculations in the design view, it won't open at
all anymore, saying "You tried to execute a query that does not include
the
specified expression "Employee Name" as part of an aggregate function."

HEEELP!

Thanks!

 

[Tamira]

Thanks!

When you use a TOTAL query, you have either GROUP, either aggregated fields.
As example, if you SUM(pay) and GROUP BY employeeID, you will get the sum of
the column pay, for each employeeID. Doing so, what happen, say, of the
field DateOfThePay? Well, since you SUM the pay, per employee, the date
where each pay did occur become irrelevant. If you want to re-included it in
the SELECT statement, you must either include it in the GROUP BY list of
fields, and then the SUM will occur by employee, by date of pay; or,
aggregate it, such as MIN(dateOfThePay) to return the first time the
employee did receive a pay, as example.


The grid also allow you to use WHERE, that is, the criteria is then applied
BEFORE the aggregate is computed. As example, you can add a WHERE on the
dateOfPay, to only include the pay that occurred between 1/1/2001 and
12/31/2001, as example. But if this field is not, in another column, used as
GROUP BY, or with an AGGREGATE, that field will not be eligible to appear in
the SELECT statement.


You should be able to open your query directly in SQL view, or in design
view WITHOUT first going through a data view (which generates the error, as
you described). If you have problem to modify the statement in SQL view and
cannot open in design view, I suggest that

- you remove the GROUP BY clause (including the list of fields that
follow it)
- you remove all the MIN( ), MAX( ), SUM( ) , while keeping what is
inside the keyword.


So

SELECT f1, MAX(f2), SUM(f3), f4
FROM myTable
GROUP BY f1, f5
ORDER BY f1


become


SELECT f1, f2, f3, f4
FROM myTable
ORDER BY f1



Sure, that only allows you to get the editor back... it does not solve the
first problem you were trying to solve. To solve you problem, I suggest
something like (it is NOT available through a DESIGN view, you have to be in
SQL view):


SELECT employeeID, SUM(pay)
FROM myTable
GROUP BY employeeID

UNION ALL

SELECT Null, SUM(pay)
FROM myTable



The line having nothing under the first column is the line giving the total,
over all employee.



Hoping it may help,
Vanderghast, Access MVP

Hi, I'm a new user since last week.

I made a query that calculates wages based on the pay rate and hours that
are entered in the original table.

This calculates the motnhly wages for each employee, but I need a Totals
row
that calculates the sum of all employees' monthly wages to see how much
the
company pays out in wages each months.

At first I tried to add columns in the design view with Sum([July Pay]),
but
I got a bunch of error messages. Then I discovered the "Totals" row in the
design view. I click "Sum" under each month's pay. But I don't know what
to
enter in the Totals row in the columns of "Employee Name" "Employment
type"
"Hours worked" ...

Anyway when I don't fill in anything in their Totals row, I get the error
message "You tried to execute a query that does not include the specified
expression "Employee Name" as part of an aggregate function."

When I choose "Group by" or "Where" (I have no clue what these do) for
Employee Name or Hours Worked then it prompts me for a numeric value for
number of Hours Worked before running the query. I don't know the total
hours
worked, the query is supposed to calculate that!

Anyway now I'm stuck because I saved changes and now when I want to open
my
query to tinker with the calculations in the design view, it won't open at
all anymore, saying "You tried to execute a query that does not include
the
specified expression "Employee Name" as part of an aggregate function."

HEEELP!

Thanks!