Calculated fields and aggregate functions

[Leebo86]

I'm extremely new to Access, so if you need clarification on something I'd be happy to do that. I'm still learning the lingo.

I have a table with the following fields:
-ID number
-Function
-Date
-Start Time
-Stop Time
-Number Processed

I want to create a query that will calculate the hourly rate of units produced.

This is very easy to do when it's just one record at a time. I created a query to calculate the time spent and used that to determine the hourly rate.

But when I try to turn it into a totals query, it just doesn't work, and I get error messages about not including my functions as aggregate functions.

I want to be able to get the hourly rate for individuals, for specific functions, and on specific dates, but I can't seem to make the calculated fields work in a totals query.

Can someone see where I'm going wrong and assist?

Thanks in advance.

 

[plog]

What have you tried? Also, 'Date' and 'Function' are bad names for a fields. They are reserved word and can cause issues when writing statements.

 

[Leebo86]

Oh, the names of the fields are actually ProductionDate and FunctionName, but thanks for the warning on that.

The query I've tried has a place for all the fields that the table I described has. It then has 2 calculated fields. One uses the start time and stop time to calculate the minutes elapsed. The other divides the number of units processed by the time elapsed to get an hourly rate.

Like I said, it works fine when it's run and shows the minutes elapsed and hourly rate for each record.

But I want it to calculate on groups of data, like the overall hourly rate for an individual worker on a specific date.

When I add the totals row, I selected "Expression" for the two calculated fields from the dropdown box, but I get errors when I try to run the query with those settings.

 

[plog]

'Expression' only works if you are using an aggregate function (Sum(), Max(), Count(), etc) in the top part. What do you have in the top part of the field that has 'Expression'? What are you trying to do with those fields?

 

[Leebo86]

Those fields are calculated fields with the simple expressions I described.

One is calculates the time elapsed and the other calculates the hourly rate.

What I want to happen is for the other fields, which are static and come from the table, to get grouped together and for the calculated fields to perform their calculations on those groups.

So let's say you have 2 users each with 4 records.

When I run the query, I don't want the time elapsed and the hourly rate for each individual record, I want the time elapsed and hourly rate for each user whose 4 records have been combined together.

What do I need to do to make that happen?

 

[plog]

Post your SQL.

 

[Leebo86]

Sorry, not sure what is meant by that.

Do you mean the expressions that are present in the calculated fields?

The time spent field is Round([StopTime]-[StartTime])*1440,0)
And the hourly rate field is Round([NumProduced]/([TimeSpent]/60),0)

 

[plog]

In the Total row of the query, what do you have for those fields? 'Expression'? Or 'Sum'?

 

[Leebo86]

I had Expression, but I understand why it won't work.

I tried changing it to sum and I got the error "Subqueries cannot be used in the expression Round([NumProduced]/([TimeSpent]/60),0)"

 

[Leebo86]

I really feel like starting a new thread, because I feel like if no one knows how to answer my question, it must be because I'm just terrible at explaining it and all of the stuff above is just going to be unnecessary baggage for getting to the core of the issue.

I'll try explaining it from scratch.

Let's say I have a table of this data

ID | Function | Processed | StartTime | StopTime
---|----------|-----------|-----------|---------
001|   Widgets|        100|    10:00AM|  11:00AM
001|   Widgets|         50|     1:00PM|   2:00PM
002|   Widgets|        200|    10:00AM|  11:00AM
002|   Widgets|        100|     1:00PM|   2:00PM

In this example, worker 001 worked for 2 hours and produced 150 widgets.
Worker 002 worked for 2 hours and produced 300 widgets.

Worker 001 is working at a rate of 75 widgets per hour.
Worker 002 is working at a rate of 150 widgets per hour.

How can I make a query that gives me those numbers, 75 widgets per hour and 150 widgets per hour?

 

[plog]

This SQL should give you what you want:

SELECT YourTableNameHere.ID, YourTableNameHere.Function, Sum([Processed])/Sum(DateDiff("n",[STartTime],[StopTime])/60) AS ProcessRatePerHour
FROM YourTableNameHere
GROUP BY YourTableNameHere.ID, YourTableNameHere.Function;

Be sure to replace every instance of 'YourTableNameHere' with the name of your actual table.