Query that performs a calculation on each record in a field and then averages the results

[jmfenn]

Hey guys. I'm a bit of an access initiate, but I know a bunch of VBA if needed for the solution. Thanks for even bothering to take the time to look at this!



To simplify things a lot, I have three tables: OrderT with fields OrderID, OrderTime, OrderStatusID, and AssignedTo; OrderStatusT with fields OrderStatusID and Status; and EmployeeT with fields EmployeeID and FirstName. Each order gets time-stamped when it is first created, marked as Active (OrderStatusID = 1), and then assigned to an EmployeeID.

I'm trying to create a query that displays the average time that has passed (in hours) for each employee's active orders. If that were it, this would be easy enough to do: I would make a query with EmployeeT and include one field for EmployeeID, one for FirstName, and something like AvgAge: NOW() - DAVG("OrderTime", "OrderT", "OrderStatus = 1 AND AssignedTo = " & [EmployeeID]). The beauty is that Access has the DAVG function which gives me one number per employee that I can manipulate.

The problem is that I need AvgAge to only count office hours between 9–5, Mon–Fri. So if you started an order at 4PM on Friday and I ran the query at 10AM on Monday, it would display "2". I can maybe rig up a function that calculates this, but I have no idea how to put this into my query. Like, let's say that I write a function OfficeHourTimePassed(OrderTime as single), how do I run each record's OrderTime through that function AND THEN find the average of each record's modified result?

It won't work to perform any calculation to a DAVG function that finds the start time, because of weekends and stuff. So I need to perform a calculation on each record in a field AND THEN take the average. Is this possible? Thanks!!!

[jmfenn]

I think I've got it! After an hour of banging my head against my keyboard, I've discovered that the DAvg function works for queries, too. So I'm going to create one query that converts OrderTime to OfficeHourTimePassed, and another that calls OfficeHourTimePassed from the first query and averages all of them up for each Employee's active files.

So, in case anyone's wondering the same thing, I have one Query (Query1) based on OrderT, including the fields: AssignedTo, OrderStatus, and TimePassed: OfficeHourTimePassed([OrderTime]), where OfficeHourTimePassed is a vba function that I will write that returns the difference in hours between Now() and the OrderTime, but only calculating hours 9–5 and Mon–Fri.

Then I have a second Query very much like the one I was originally trying to create, this one based on EmployeeT and including the fields: EmployeeID, FirstName, and AvgAge: DAvg("TimePassed", "Query1", "AssignedTo = " & [EmployeeID])

[ssanfu]

Not sure why the DAvg () , but..... if you get stuck on the VBA function, take a peek in the attached dB.


For your 2nd query, you might change "Query1" to "qryTimePassed_Only_Active".


This is only 1 of many ways to get the elapsed hours.
And, remember........ no peeking....


And welcome to the forum....

[jmfenn]

Thanks Steve! I learned a lot just from looking through what you sent. This is my first time making a database, and I've only been using vba for a couple years, so I've got lots to learn.

[jmfenn]

To clarify, most of my queries have different names than I said in my post because the database has become pretty big and complicated. Also, I'm using the DAvg() because I want a list of each employee, not a list of each order. The purpose is to have a better idea where everyone is at on their active orders before assigning them another one. With what you sent, I will just make another query that averages the Expr1 for each employee. I'm definitely going to comb through your code and learn, though. Thanks again!

[June7]

Domain aggregate functions can perform slowly in queries. Consider building a report and using Sorting & Grouping features with aggregate calcs in group/report header/footer sections.

[jmfenn]

Thanks June7! I'll run a speed test and try to work that in if necessary.

[jmfenn]

Hey Steve, I'm attaching a modified version of the database you sent me. Feel free to look at it, but you don't have to. It might be helpful for other people later. Here are a few things I changed:

1. Removed the creation of a DAO recordset in the code. Maybe that was a dumb move (I honestly have no idea), but it made it simpler to read/understand. Does the DAO recordset in the code speed things up?
2. Changed the code so that if an order is placed outside of office hours or if the query is run outside of office hours it doesn't add (or subtract) the time for that day.
3. Changed the code so that if the query is run on the same day as an order, it doesn't add an extra 8 hours.
4. Changed the code so that if I need to change the office start or end dates I only need to change it up top (replaced any reference to 8 hours with "DateDiff("h", OfficeStart, OfficeEnd)".
5. Changed some records for testing purposes.
6. Added a qryEdit that runs my new code
7. Added a qryEmployeePerformance that runs the query that I originally intended: a list of each employee and their average order time for active files.

Thanks for your code, though! It was really helpful.

Order Time in Office Hours.zip

[ssanfu]

1. Removed the creation of a DAO recordset in the code. Maybe that was a dumb move (I honestly have no idea), but it made it simpler to read/understand.

I started out in one direction and ended up going another.... and didn't clean up the unused code. (too many other things going on - read work)

Does the DAO recordset in the code speed things up?

No, slower (by milliseconds - you'll probably never notice though ). But still, shouldn't have garbage code in a procedure.

4. Changed the code so that if I need to change the office start or end  dates I only need to change it up top (replaced any reference to 8 hours  with "DateDiff("h", OfficeStart, OfficeEnd)".

Still one line where 8 (hrs) is hard coded...??? (If TotHrs > 8 Then ...)

Along that vein, towards the top of the code, I might do

Dim HrsPerDay  as Single
.
.
HrsPerDay = DateDiff("h", OfficeStart, OfficeEnd)

True, it will save maybe a millisecond or 2, but does add to ease of reading. (And my personal preference is to never use the single line IF() statement syntax)


I see I forgot to check in the first or last day is a weekend day.


I have to say "nice job". Good catches on my errors.

[jmfenn]

Right... good catch on my last 8 hrs. Thanks for your valuable time!