Sum of a sum

  • Thread starter Thread starter Simon Harris
  • Start date Start date
S

Simon Harris

Hi All,

I am trying to build a query which gives me the sum of labour costs for a
specific job.

Stored in TBL_Labour_Log is number of hours, staff_id, job_id
Stored in TBL_Staff is name, rate_of_pay

I have tried the following:

SELECT TBL_order_time_log.order_id, TBL_order_time_log.Idate,
TBL_employees.first_name, TBL_employees.surname,
TBL_order_time_log.hours_spent, TBL_employees.Rate_of_Pay,
Sum([hours_spent]*[Rate_Of_Pay]) AS LabourSubTotal
FROM TBL_employees INNER JOIN TBL_order_time_log ON TBL_employees.ID =
TBL_order_time_log.staff_id
GROUP BY TBL_order_time_log.order_id, TBL_order_time_log.Idate,
TBL_employees.first_name, TBL_employees.surname,
TBL_order_time_log.hours_spent, TBL_employees.Rate_of_Pay
HAVING
(((TBL_order_time_log.order_id)=[forms]![frm_switchboard]![SUBFRM_view_order
s]));

Which works, and gives me a row for each log of hours recorded, along with
the total amount in money (e.g: 2hrs @ £15 p/hr correctly gives me £30)

What I really need is the total amount in money, so I tried amending the
query as follows:

Sum(Sum([hours_spent]*[Rate_Of_Pay]))

When I run the query, Access errors:

'Cannot have aggregate function in expression
(sum(sum([hours_spent]*[rate_of_pay]))).'

Hopefully someone will understand what I'm trying to achieve (Sorry, cant
really explain better than I have!)

Kind regards,

Simon.

PS: Just had an Idea....could I have a query based on the above query, which
will then give me the sum of the column showing total in money???? Any
problems with basing a query on a query??

--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
 
Simon Harris said:
Hi All,

I am trying to build a query which gives me the sum of labour costs
for a specific job.

Stored in TBL_Labour_Log is number of hours, staff_id, job_id
Stored in TBL_Staff is name, rate_of_pay

I have tried the following:

SELECT TBL_order_time_log.order_id, TBL_order_time_log.Idate,
TBL_employees.first_name, TBL_employees.surname,
TBL_order_time_log.hours_spent, TBL_employees.Rate_of_Pay,
Sum([hours_spent]*[Rate_Of_Pay]) AS LabourSubTotal
FROM TBL_employees INNER JOIN TBL_order_time_log ON TBL_employees.ID =
TBL_order_time_log.staff_id
GROUP BY TBL_order_time_log.order_id, TBL_order_time_log.Idate,
TBL_employees.first_name, TBL_employees.surname,
TBL_order_time_log.hours_spent, TBL_employees.Rate_of_Pay
HAVING
(((TBL_order_time_log.order_id)=[forms]![frm_switchboard]![SUBFRM_view_o
rder
s]));

Which works, and gives me a row for each log of hours recorded, along
with the total amount in money (e.g: 2hrs @ £15 p/hr correctly gives
me £30)

What I really need is the total amount in money, so I tried amending
the query as follows:

Sum(Sum([hours_spent]*[Rate_Of_Pay]))

When I run the query, Access errors:

'Cannot have aggregate function in expression
(sum(sum([hours_spent]*[rate_of_pay]))).'

Hopefully someone will understand what I'm trying to achieve (Sorry,
cant really explain better than I have!)

Kind regards,

Simon.

PS: Just had an Idea....could I have a query based on the above
query, which will then give me the sum of the column showing total in
money???? Any problems with basing a query on a query??
Click to expand...

Interpreting your question would be a lot easier if you would correctly
state the names of your tables and fields. You said:
Stored in TBL_Labour_Log is number of hours, staff_id, job_id
Stored in TBL_Staff is name, rate_of_pay
Click to expand...

but these aren't the table and field names used in the SQL statement you
posted. I think what you meant is fairly clear, but please don't post
false or misleading information when asking a question.

You can certainly base a query on a query, with no trouble at all, and
that may do the job for you here. It may be more efficient, on the
other hand, to write a second query that simply extracts what you need
drectly, by not grouping to the same low detail level. I'm not entirely
sure what you mean when you say
What I really need is the total amount in money
Click to expand...

Are you talking about the total amount for the order specified by
[forms]![frm_switchboard]![SUBFRM_view_orders]? If so, I think you
could modify your existing SQL statement like this:

SELECT
TBL_order_time_log.order_id,
Sum(TBL_order_time_log.hours_spent*TBL_employees.Rate_Of_Pay)
AS LabourSubTotal
FROM
TBL_employees
INNER JOIN TBL_order_time_log
ON TBL_employees.ID = TBL_order_time_log.staff_id
WHERE
TBL_order_time_log.order_id =
[forms]![frm_switchboard]![SUBFRM_view_orders]
GROUP BY
TBL_order_time_log.order_id;

That will return one record for the order specified (unless I've made a
mistake in the SQL, of course).
 
Back
Top