We help IT Professionals succeed at work.

# Sum/Avg/Max/Min

on
Medium Priority
81 Views
Hello Team,

I am trying to calculate Min, Avg, Max, Standard deviation and count based on specific criteria. I want to calculate based on number of shifts and based on titles. this will need to be by month, July, August, Sept and by year.

one staff may have worked 4 shifts per month, then went on vacation. if he cared for 100 customer during the 4 days, max would be 100, average would be 25...etc. I want also to count the number of shifts he worked, and the number of customer he cared for. the result needed is by title also. I used the query below and I got a strange results.
Database44.accdb
Comment
Watch Question

## View Solution Only

CERTIFIED EXPERT

Commented:
No query is attached.
Chief Technology Officer
CERTIFIED EXPERT

Commented:
Easy!  Got it working without any problem!  See query attached.
Software & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
I guess this is the question of the "invisible query"...now you see it...now you don't... :)
Chief Technology Officer
CERTIFIED EXPERT

Commented:
John:  Don't think too hard on this one.  I used Fed's attached query to come up with my answer..... ;-)
Software & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Still not seeing it... : )

Commented:
there is no attachment....
CERTIFIED EXPERT

Commented:
And do you plan to post one?

Otherwise the only proposal we can do is:

SELECT MIN(SomeColumn), MAX(SomeColumn), AVG(SomeColumn)
FROM SomeTable
WHERE SpecificCriteria

You should also provide some data samples and tell what's strange with your results.

Commented:
sorry folks, for some reason the database was not attached in the question when i sent it. my apology!

kindly see attached database
Software & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
I think we need some explaining about the "data" you provide...
otherwise if i understood your reqs...the ways to go is to create a crosstab query for each aggregation and finally "concatenate" all the info from the cross tab queries to one master query
Database44.accdb

Commented:
This works for me. thank you!

Commented:
Brilliant!
Software & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Glad it worked as you wanted...:)...