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    
post Sep 14 2019, 04:31 PM

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?
Go to the top of the page
post Sep 14 2019, 04:47 PM

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

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

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
post Sep 14 2019, 05:35 PM

Posts: 876
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
post Sep 14 2019, 06:29 PM

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

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

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

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

SELECT User, AVG(JobsCompleted) GROUP BY User


Robert Crouser
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    18th October 2019 - 06:37 PM