We help IT Professionals succeed at work.

Sum/Avg/Max/Min

Medium Priority
81 Views
Last Modified: 2019-08-18
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

CERTIFIED EXPERT

Commented:
No query is attached.
Mark EdwardsChief Technology Officer
CERTIFIED EXPERT

Commented:
Easy!  Got it working without any problem!  See query attached.
John TsioumprisSoftware & 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... :)
Mark EdwardsChief 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..... ;-)
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Still not seeing it... : )

Author

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.

Author

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

Author

Commented:
This works for me. thank you!

Author

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

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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.