UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Totals In Text Columns, Access 2016    
 
   
PvL
post Sep 14 2019, 04:31 PM
Post#1



Posts: 3
Joined: 21-January 17



Sample date of table:
Date | Jobnr | User
10/09/19 | a1234 | Bob
10/09/19 | b4384 | Bob
10/09/19 | a4595 | Stuart
11/09/19 | c9845 | Bob
13/09/19 | f8456 | Stuart

I've got a table with these 3 fields.
I would like to know how many jobs a user does per month.
And also I would like to see how many jobs a user does average per working day (so filtering out the dates that a certain user has not done any jobs).
I know the expression to filter the month by month number, but am not sure about the average and totals (guess it would be through Dsum but have no idea how to write this expression).

Hope anyone can give me a hand?
Thanks
Go to the top of the page
 
GroverParkGeorge
post Sep 14 2019, 04:47 PM
Post#2


UA Admin
Posts: 35,902
Joined: 20-June 02
From: Newcastle, WA


To return the User and a count of jobs completed by that user in a given month:

SQL
SELECT YourTableNameGoesHere.User, Count(YourTableNameGoesHere.Jobnr) as JobsCompleted
FROM YourTableNameGoesHere WHERE YourTableNameGoesHere.YourDateField Between #01/09/2019# AND #30/09/2019#
GROUP BY YourTableNameGoesHere.User

This post has been edited by GroverParkGeorge: Sep 14 2019, 04:56 PM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
June7
post Sep 14 2019, 05:35 PM
Post#3



Posts: 897
Joined: 25-January 16



For all months:

SELECT User, Count(Jobnr) AS JobsCompleted, Format([Date], "yyyymm") AS YrMo
FROM YourTableNameGoesHere
GROUP BY User, Format([Date], "yyyymm");

Date is a reserved word, should not use reserved words as names for anything.


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
projecttoday
post Sep 14 2019, 06:29 PM
Post#4


UtterAccess VIP
Posts: 11,087
Joined: 10-February 04
From: South Charleston, WV


For an average of the number of jobs per day, do

CODE
SELECT User, Count(Jobnr) AS JobsCompleted, Datefield
FROM YourTableNameGoesHere
GROUP BY User, Datefield


and save that. Let's say it's qryDailyJobCounts. Then

CODE
SELECT User, AVG(JobsCompleted) GROUP BY User


Untested.

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    23rd October 2019 - 09:43 PM