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
> Count Weeks Worked Per Month, Access 2007    
 
   
admatura
post Aug 14 2019, 08:36 AM
Post#1



Posts: 83
Joined: 15-August 16



Good Day,
I have a query that gets the total days worked for an employee grouped by month. I am trying to get the number of weeks worked for each month but I am have a little trouble with the results. I looked at this post https://www.UtterAccess.com/forum/index.php...owtopic=2034639 which kind of help a little but results are still not what I want.

Using this expression:

CODE
=Sum(IIf(Format([WorkDay],"ww")=Format(Date(),"ww"),1,0)


The Data
EMPID: 1
WorkDay: 5/1/19
WorkDay: 5/2/19
WorkDay: 5/3/19
WorkDay: 5/4/19

Results: 0 Should be 1, as 5/1/19 to 5/4/19 is only 1 week

EMPID: 8
WorkDay: 8/9/19
WorkDay: 8/10/19
WorkDay: 8/12/19
WorkDay: 8/13/19

Results: 2 Correct as 8/9/19 to 8/10/19 is 1 week and 8/12/19 to 8/13/19 is the second week

Go to the top of the page
 
BruceM
post Aug 14 2019, 09:51 AM
Post#2


UtterAccess VIP
Posts: 7,951
Joined: 24-May 10
From: Downeast Maine


Using "ww" with Format gives you the week number for the year. Today the dates in the second batch that matched the current week are 8/12 and 8/13. It appears you got the correct result by coincidence. If you add 8/14 to the records I expect the result will be 3.

If this is for reporting purposes (that is, if a read-only recordset is acceptable) there are things you could do with counting and grouping in queries, but nothing simple occurs to me at the moment.

Also, how do you plan to handle a week that is split between two months?
Go to the top of the page
 
admatura
post Aug 14 2019, 02:20 PM
Post#3



Posts: 83
Joined: 15-August 16



After tinkering with it for a while I came up with this:

CODE
SELECT DISTINCT Format([WorkDay],"m/yyyy") AS WCGBM, qryPayDetails.EMPID AS WCEMPID, qryPayDetails.WKNUM
FROM qryPayDetails
GROUP BY Format([WorkDay],"m/yyyy"), qryPayDetails.EMPID, qryPayDetails.WKNUM;


This Groups my Pay Details query by month (WCGBM) and for each WorkDay I get the Week Number for each Employee (EMPID). Now I need to Count each WCGBM.

I created another Query to count the WCGBM but when I join it to another query the results are duplicating.

Any advice anyone?
Go to the top of the page
 
RJD
post Aug 14 2019, 02:58 PM
Post#4


UtterAccess VIP
Posts: 10,017
Joined: 25-October 10
From: Gulf South USA


Hi: Is this anything like you are trying to produce? See attached. Without a picture of what you are trying to display, we are flying a bit blind.

HTH
Joe
Attached File(s)
Attached File  CountWeeks.zip ( 23.03K )Number of downloads: 1
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
admatura
post Aug 14 2019, 03:21 PM
Post#5



Posts: 83
Joined: 15-August 16



Yes RJD, that is far I have reached. But I want to include the results of qryWeekAndMonthSummary into another query I have called qryPaySummary. This query totals the pay based on a start and an end date criteria. When I join qryWeekAndMonthSummary to it, I get a running sum on the Gross pay field.
Attached File(s)
Attached File  Payroll.zip ( 1.21MB )Number of downloads: 1
 
Go to the top of the page
 
RJD
post Aug 14 2019, 04:26 PM
Post#6


UtterAccess VIP
Posts: 10,017
Joined: 25-October 10
From: Gulf South USA


Hi: Still much I do not understand about what you want, but see if this is close. You may have to move things around, or provide more information about what you want.

I put the month and week counts by each employee in the report, but that may not be in the correct place.

HTH
Joe
Attached File(s)
Attached File  Payroll_Rev1.zip ( 1.18MB )Number of downloads: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
admatura
post Aug 14 2019, 07:00 PM
Post#7



Posts: 83
Joined: 15-August 16



Ok, that's good. Thank you. One little problem though which was highlighted by BruceM

QUOTE
BruceM
Also, how do you plan to handle a week that is split between two months?


Normally how would someone deal with this issue?

In my country, National Insurance is calculated per week multiplied by the number of Mondays in the month. I wanted to calculate the number of weeks worked by an employee for any given month. But access is counting weeks by the Saturdays. I need a way to count weeks by Mondays.
This post has been edited by admatura: Aug 14 2019, 07:00 PM
Go to the top of the page
 
RJD
post Aug 14 2019, 08:45 PM
Post#8


UtterAccess VIP
Posts: 10,017
Joined: 25-October 10
From: Gulf South USA


Hi:

I'm not sure what your business rules are for dealing with weeks split between two months, so don't know how to address that issue immediately. Knowing what your rules are, I am sure you can develop calculations by finding a specific day of the week count in a month.

QUOTE
Normally how would someone deal with this issue?

I don't know. I have never seen this issue in my databases.

QUOTE
I wanted to calculate the number of weeks worked by an employee for any given month. But access is counting weeks by the Saturdays. I need a way to count weeks by Mondays.

Actually, I think Access counts weeks by Sundays. If you want Mondays, try this in the Format function ... note the additional ",2" to set Monday as the first of the week.

Format([WorkDay],"yyyyww",2)

In qryWeekCountByMonth ...

CODE
SELECT wks.WCEMPID, wks.WCGBM, Count(wks.WKNUM) AS WeeksCount
FROM (SELECT Format([WorkDay],"yyyymm") AS WCGBM, qryPayDetails.EMPID AS WCEMPID, Format([WorkDay],"yyyyww",2) AS WKNUM FROM qryPayDetails WHERE WorkDay Between [Forms]![frmPayPop]![txtPeriodStart] And [Forms]![frmPayPop]![txtPeriodEnd] GROUP BY Format([WorkDay],"yyyymm"), qryPayDetails.EMPID, Format([WorkDay],"yyyyww",2))  AS wks
GROUP BY wks.WCEMPID, wks.WCGBM
ORDER BY wks.WCEMPID, wks.WCGBM;

See if this gives you what you need.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
admatura
post Aug 16 2019, 10:26 AM
Post#9



Posts: 83
Joined: 15-August 16



I'm still getting the week count to be 3 for August, it should be 2. Is there any way to set the week to end on a Monday? For every Monday it will count as 1 week.
Go to the top of the page
 
RJD
post Aug 16 2019, 11:03 AM
Post#10


UtterAccess VIP
Posts: 10,017
Joined: 25-October 10
From: Gulf South USA


Hi: Okay, so you are not counting weeks at all, but counting Mondays, right? In the 8/1 to 8/13 time frame (your table) there are actually 3 weeks represented, but only 2 Mondays. So the "ww" doesn't seem that it will give you the value you want. Just to be clear, if the last day of a period is on a Monday, that is counted as a week in that period, and even though the majority of the week falls in another period, it is not counted in that other period.

I'll take another look, but it seems that the week count approach will need a rework, away from the "ww" approach.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
admatura
post Aug 16 2019, 11:11 AM
Post#11



Posts: 83
Joined: 15-August 16



I was thinking to redo the query to calculate the gross per week so it looks like this:

CODE
WK1: SumofGross Between  [Forms]![frmPayPop]![txtPeriodStart] And SomeFunctionFindFirstMonday([Forms]![frmPayPop]![txtPeriodStart])
WK2: SumofGross Between  SomeFunctionFindFirstMonday([Forms]![frmPayPop]![txtPeriodStart]) And SomeFunctionFindFirstMonday([Forms]![frmPayPop]![txtPeriodStart])+7
WK3: SumofGross Between  SomeFunctionFindFirstMonday([Forms]![frmPayPop]![txtPeriodStart]) And SomeFunctionFindFirstMonday([Forms]![frmPayPop]![txtPeriodStart])+14
WK4: SumofGross Between  SomeFunctionFindFirstMonday([Forms]![frmPayPop]![txtPeriodStart]) And SomeFunctionFindFirstMonday([Forms]![frmPayPop]![txtPeriodStart])+28


The Results should look Like this:

EmpID___WK1_____Wk2______Wk3____Wk4
6_______765.63__1593.75___1062.50___0__
This post has been edited by admatura: Aug 16 2019, 11:47 AM
Go to the top of the page
 
RJD
post Aug 16 2019, 12:20 PM
Post#12


UtterAccess VIP
Posts: 10,017
Joined: 25-October 10
From: Gulf South USA


Hi: Well, your last post seems to be going in a different direction than what I was working on. I was simply counting the weeks in a different way, to include only weeks that contain a Monday in the month in question. But perhaps that is not what you actually wanted.

See my revision 2 to your db attached. See the report with week counts. Then see qryPayDetails and qryWeekCountByMonth for modifications.

See if this is helpful, or if you are actually trying to do something different.

HTH
Joe
Attached File(s)
Attached File  Payroll_Rev2.zip ( 1.18MB )Number of downloads: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
admatura
post Aug 16 2019, 01:26 PM
Post#13



Posts: 83
Joined: 15-August 16



I've decided to take a different approach.

Using this function:

CODE
Public Function FirstMonday(Optional InputDate As Variant) As Date
    Dim i As Integer
    Dim dteTemp As Date
    
    If IsMissing(InputDate) Or Not IsDate(InputDate) Then
        dteTemp = Date
    Else
        dteTemp = InputDate
    End If
    
    For i = 1 To 7
        dteTemp = DateSerial(Year(dteTemp), Month(dteTemp), i)
        If Weekday(dteTemp) = vbMonday Then
            FirstMonday = dteTemp
            Exit For
        End If
    Next i
End Function


I was able to get the first Monday for each Month.

Then I created 5 queries summing the gross for each pay period ending at a Monday


CODE
Wk1: Between [Forms]![frmPayPop]![txtPeriodStart] And FirstMonday([Forms]![frmPayPop]![txtPeriodStart])

Wk2: Between FirstMonday([Forms]![frmPayPop]![txtPeriodStart])+1 And FirstMonday([Forms]![frmPayPop]![txtPeriodStart])+7


and so on. I merged each query to give me the Gross salary by each week. Now I can properly calculate the taxes.
Go to the top of the page
 
RJD
post Aug 16 2019, 01:36 PM
Post#14


UtterAccess VIP
Posts: 10,017
Joined: 25-October 10
From: Gulf South USA


Good. Sounds you have your issue resolved. thumbup.gif

Continued success ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
admatura
post Aug 20 2019, 02:45 PM
Post#15



Posts: 83
Joined: 15-August 16



Hello RJD, can you help me with something. In the attachment I'm trying to get the Pay by week grouped by month in this format
I am have problems joining the queries to show the data as follows

EMPID___MONTH____WK1_____WK2_____WK3_____WK4_____WK5
1________1/2019_____800_______0_______200______500_______0
2________6/2019_____1000_____100______300_______0_______600
1________6/2019_____800_______0_______200______500_______0

As seen in qryYTDtot but the values are duplicating. The reason, I need it like that to calculate the YTD totals for every employee, each week per month.
Attached File(s)
Attached File  Payroll_v2.0.accdb.zip ( 1.21MB )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Aug 20 2019, 03:44 PM
Post#16


UtterAccess VIP
Posts: 10,017
Joined: 25-October 10
From: Gulf South USA


Hi: I didn't dig too deeply into everything you are doing, so assumed that the feeder queries (e.g. qryYTDWk1, etc.) gave you the results you want individually. See the new query qryYTDSumofWeeksTotal and see if this gives you what you need. Note that it uses a subquery that is a UNION of the 5 weeks of data and the main query summarizes the values.

The problem you encountered before was that the way you were doing the JOINs you were causing several 1-many relationships, thus causing multiple counting of values. The UNION should avoid that situation.

HTH
Joe
Attached File(s)
Attached File  Payroll_v2_0_Rev1.zip ( 1.19MB )Number of downloads: 1
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
admatura
post Aug 22 2019, 09:06 AM
Post#17



Posts: 83
Joined: 15-August 16



That's perfect. Thank you. I really need to do some reading up on Union Queries.
Go to the top of the page
 
RJD
post Aug 22 2019, 09:38 AM
Post#18


UtterAccess VIP
Posts: 10,017
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Glad that worked for you. thumbup.gif

Yes, UNION queries are powerful tools, and well worth learning about.

Continued success ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
June7
post Aug 25 2019, 02:47 PM
Post#19



Posts: 788
Joined: 25-January 16



Cross post with alternate approach https://stackoverflow.com/questions/5757730...580812#57580812

--------------------
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
 


Custom Search


RSSSearch   Top   Lo-Fi    16th September 2019 - 11:44 AM