Non-continuous counting in query (1 Viewer)

AnnaMagna

New member
Local time
Today, 19:07
Joined
Mar 16, 2020
Messages
11
Hello,

I have a table in my database that contains is a list of all the work record dates for each employee, along with a "type", which is either "ABS" (absence) or "work". For example an extract of my table looks like this:
Clock number Date Type
1 1/1/20 Work
1 2/1/20 Work
1 4/1/20 ABS
1 5/1/20 ABS
1 6/1/20 Work
1 9/1/20 Work
1 10/1/20 ABS
1 11/1/20 Work

If there isn't a record for a date then there was no working or absence on that date, i.e. it wasn't the scheduled shift pattern.

What i want to do is count how many distinct periods of absence the employee has had in a given date range. 1 absence period is classed as a period of absence without any work records in between ABS work records, no matter how many ABS days there have been in the period. i.e. if there are 2 or more consecutive days of ABS without any "Work" in between this counts as 1 absence period. In the example above, there would be 2 absence periods; 1 from 4/1/20-5/1/20, and 1 on 10/1/20.

I can't fathom out how to do this in a query, is there something obvious i am missing?

Thanks
Anna
 

vba_php

Forum Troll
Local time
Today, 14:07
Joined
Oct 6, 2019
Messages
2,884
Anna,

when you say this:
1 absence period is classed as a period of absence without any work records in between ABS work records, no matter how many ABS days there have been in the period. i.e. if there are 2 or more consecutive days of ABS without any "Work" in between this counts as 1 absence period.
and then you say this:
In the example above, there would be 2 absence periods; 1 from 4/1/20-5/1/20, and 1 on 10/1/20
you are contradicting yourself. the first quote means something different than the 2nd quote. a DISTINCT query, when written, looks like this:
Code:
SELECT DISTINCT field1, field2, etc... FROM table
if you want to return, like you say, a notification of an absence PERIOD, like in this data of yours:

1 4/1/20 ABS
1 5/1/20 ABS


more than likely you will need more than one query or code.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 14:07
Joined
Feb 28, 2001
Messages
26,999
That certainly IS a head-scratcher. The problem is, as you point out, that some days are not listed because of shift issues. So for example in your table, there IS no entry of either kind for 3/1/20. Fortunately, a sub-query might be just what you need.

Comments that are side issues, but might help in the long run: (1) The words DATE and TYPE are reserved words and should not be used as a field name. (2) Having spaces in the middle of a field name forces you to use bracketing. If you removed the space, you could omit the brackets. Less typing.

You didn't tell us the table name so I can't use that name. I am arbitrarily calling the table EmpWork (for this discussion). To illustrate my point (2), I removed the space from [Clock Number]. In line with point (1) I changed the DATE field to WDATE and changed the TYPE field to WTYPE. The solution to your problem MIGHT be something like this. I also refer you to this article:


Query1:

Code:
SELECT
    EW.ClockNumber,
    EW.WType,
    EW.WDate,
    ( SELECT TOP 1 PW.WType FROM EmpWork AS PW
      WHERE
        EW.ClockNumber = PW.ClockNumber AND
        PW.WDate < EW.WDate
     ORDER BY PW.WDate DESC, PW.ClockNumber )  AS PrevWType
FROM EmpWork AS EW
WHERE ( EW.WType = 'ABS' ) AND ( EW.WType <> PrevWType )
ORDER BY EW.WDate ;

Query2:

Code:
SELECT ClockNumber, Count(WType) FROM Query1 GROUP BY ClockNumber;

If this works as I think it might, the sub-query looks up the work type from the previous work record for that employee and calls that PrevWType. Then the main part of Query1 looks at absences but ONLY if the PrevWType is NOT an absence. I.e. this will only list the first absence of a sequence. On the 2nd day of an absence, the PrevWType will ALSO be an absence and will get rejected. Then Query 2 just does a count of the records in Query1.

NOTE: This will fail to give a correct count in one case: If the employee is absent for the first scheduled day of work, it will not find that case. Not talking about "first day of shift." I'm talking "first day of work EVER."
 

vba_php

Forum Troll
Local time
Today, 14:07
Joined
Oct 6, 2019
Messages
2,884
I knew you were a genius, Richard. I'm telling you, you should look into PHP! =) or .NET stuff for that matter. it's all the same anyway. the resource creators just change from language to language.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 19:07
Joined
Feb 19, 2013
Messages
16,553
I have a similar solution using a subquery

SQL:
SELECT ClockNum, WorkType, Count(WorkType) AS CountOfWorkType
FROM tblClock
WHERE WorkType="abs" AND nz((SELECT TOP 1 WorkType FROM tblClock T WHERE ClockNum=tblClock.clocknum and clockdate>tblClock.clockDate order by clockdate),"")<>WorkType
GROUP BY ClockNum, WorkType;

Note if ABS is the last record then it will count it. If you don't want to count it, remove the nz code - nz(.......,"")

edit: made a small change to make it more flexible
 
Last edited:

AnnaMagna

New member
Local time
Today, 19:07
Joined
Mar 16, 2020
Messages
11
Thanks for the replies everybody. I have implemented CJ_London's subquery but can't get it to give the result i want.

When i copy and paste the SQL into my database and change all of the field and table names and try and execute the query, it says " At most one record can be returned by this query". And won't run the query. What am i doing wrong?? Would it help if i put a stripped down version of the database on here for you to play with?

Thanks
Anna
 

CJ_London

Super Moderator
Staff member
Local time
Today, 19:07
Joined
Feb 19, 2013
Messages
16,553
implication is that you have more than one record that fits the requirement - top 1 will return 2 (or more) records if they both have the same date
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:07
Joined
May 21, 2018
Messages
8,463
Maybe much simpler in a function. This works for me. I changed some of the names, because you use bad names (spaces and reserved words)
It also has the flexibility to provide any reporting period.
Code:
SELECT DISTINCT tblwork.clocknumber, 
                #1/1/2020#                                               AS 
                StartPeriod, 
                #1/11/2020#                                              AS 
                EndPeriod, 
                Getabsinrange([clocknumber], [startperiod], [endperiod]) AS 
                Absences 
FROM   tblwork;

Code:
Public Function GetAbsInRange(ClockNumber As Long, StartPeriod As Date, EndPeriod As Date)
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim absPeriod As Integer
  Dim prev As String
  strSql = "Select * from tblwork where clockNumber = " & ClockNumber & " AND thedate between #" & Format(StartPeriod, "mm/dd/yyyy") _
           & "# AND #" & Format(EndPeriod, "mm/dd/yyyy") & "#"
  Set rs = CurrentDb.OpenRecordset(strSql)
  'Debug.Print strSql
  Do While Not rs.EOF
    Debug.Print rs!THetype
    If prev <> rs!THetype And rs!THetype = "ABS" Then
      absPeriod = absPeriod + 1
    End If
    prev = rs!THetype
    rs.MoveNext
  Loop
  GetAbsInRange = absPeriod
End Function
 

Attachments

  • AbsPeriods.zip
    20 KB · Views: 118

AnnaMagna

New member
Local time
Today, 19:07
Joined
Mar 16, 2020
Messages
11
implication is that you have more than one record that fits the requirement - top 1 will return 2 (or more) records if they both have the same date

Hi CJ,

Do you mean that if there is more than one record in the table with the same work record date, this error comes up, regardless of whether this record has a different ClockID? for example in the table there are 5 records with the date 1/4/2019 and the worktype as ABS, but these are for 5 different ClockID's; each ClockID only has 1 record on that date.

Hopefully this makes sense and i'm not jabbering on?!

Thanks
Anna
 

CJ_London

Super Moderator
Staff member
Local time
Today, 19:07
Joined
Feb 19, 2013
Messages
16,553
with the same work record date
and has the same clocknum. If they are different per your post, you should not get duplicates.

Suggest copy and paste your sql so we can take a look at what you actually have
 

AnnaMagna

New member
Local time
Today, 19:07
Joined
Mar 16, 2020
Messages
11
Thanks CJ. This is my SQL:

SELECT Occurences.ClockID, Occurences.WorkType, Count(Occurences.WorkType) AS CountOfWorkType
FROM Occurences
WHERE (((Occurences.WorkType)="ABS") AND ((Nz((SELECT TOP 1 WorkType FROM Occurences T WHERE ClockID=Occurences.ClockID and WorkDate>Occurences.WorkDate order by Workdate),""))<>[WorkType]))
GROUP BY Occurences.ClockID, Occurences.WorkType;
 

CJ_London

Super Moderator
Staff member
Local time
Today, 19:07
Joined
Feb 19, 2013
Messages
16,553
OK nothing wrong with the query so far as I can see, so I still think you have records with the same clockID and workdate - can you use the query wizard to create a find duplicates query based on both those fields?

Otherwise suggest try the other two suggestions - Doc's is basically the same as mine but working from the first ABS record whereas mine is looking at the last ABS record so I would expect the same error if I'm right. MajP's is a different solution which looks like it will effectively skip over duplicates.

Whether or not duplicates matter or not in the general scheme of things, is up to you
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:07
Joined
May 21, 2018
Messages
8,463
I am pretty confident my solution works for all cases. If you have some test data that would be helpful for checking.
 

ebs17

Well-known member
Local time
Today, 20:07
Joined
Feb 7, 2020
Messages
1,881
With reference to the data from MajP you could also do something like this:
SQL:
SELECT
   Q.ClockNumber,
   COUNT(*) AS NumberABS
FROM
   (
      SELECT DISTINCT
         W.ClockNumber,
         W.TheDate - DCount("*", "tblWork", "TheType = '" & W.TheType & "' AND TheDate < " & Format(W.TheDate, "\#yyyy\-mm\-dd\#")) AS TheGroup
      FROM
         tblWork AS W
      WHERE
         W.TheType = "ABS"
   ) AS Q
GROUP BY
   Q.ClockNumber

Why don't questioners supply immediately usable data in a database that are then immediately representative?
 

AnnaMagna

New member
Local time
Today, 19:07
Joined
Mar 16, 2020
Messages
11
Hi ebs17,

I had a look at your zip file and it did exactly as i wanted. Therefore i tried to put it into my database, but got this error:

1584714964033.png


I have attached my database, are you able to put your work in for me, as i can't get it to work?

Thanks
Anna
 

Attachments

  • Absences test.zip
    723.1 KB · Views: 147

CJ_London

Super Moderator
Staff member
Local time
Today, 19:07
Joined
Feb 19, 2013
Messages
16,553
you have 24 duplicates in total in your db for ABS worktypes, a lot more for the Work worktype - so you either need to remove them for my (or Doc's) suggestion to work or you'll need to go with madj or ebs17 solution. As I said, depends on whether duplicates matter for other things
Find duplicates for Occurences

ClockIDWorkDateIDWorkType
011359
07/03/2020​
14628​
ABS
011359
07/03/2020​
14627​
ABS
014731
12/03/2020​
16598​
ABS
014731
12/03/2020​
16599​
ABS
016187
11/03/2020​
20649​
ABS
016187
11/03/2020​
20650​
ABS
018599
11/03/2020​
29634​
ABS
018599
11/03/2020​
29635​
ABS
019984
11/03/2020​
34763​
ABS
019984
11/03/2020​
34764​
ABS
020149
24/08/2019​
35390​
ABS
020208
08/03/2020​
36581​
ABS
020208
08/03/2020​
36580​
ABS
020288
11/03/2020​
36931​
ABS
020288
11/03/2020​
36932​
ABS
020562
08/03/2020​
38820​
ABS
020562
08/03/2020​
38821​
ABS
021568
11/03/2020​
43484​
ABS
021568
11/03/2020​
43485​
ABS
022843
03/03/2020​
45362​
ABS
022843
03/03/2020​
45363​
ABS
023119
11/03/2020​
45585​
ABS
023119
11/03/2020​
45586​
ABS
023678
05/03/2020​
45837​
ABS
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:07
Joined
May 21, 2018
Messages
8,463
That issue is a Reference, which should be in Microsoft Access Active Data Objects 1X.0. But i see it when I look.
Check the results. I did not verify.
 

Attachments

  • MajP_Absences test.zip
    743.4 KB · Views: 113

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:07
Joined
May 21, 2018
Messages
8,463
To avoid the issue CJ_London is talking about, you should put a composite index on ClockID and Workdate and make the combination unique.
 

AnnaMagna

New member
Local time
Today, 19:07
Joined
Mar 16, 2020
Messages
11
Thanks everyone, i must have copied into the table without deleting existing data.

I have got rid of the duplicates, however now when i run the query this is the message i get:

1585062239007.png


The date formats are the same as in the database that CJ sent over so i don't know what the problem is?

Thanks
Anna
 

Micron

AWF VIP
Local time
Today, 15:07
Joined
Oct 20, 2018
Messages
3,476
I don't see an error, just a highlighted line. If there's no error but it breaks there, what you might have is a ghost break (one that remains after you removed it). To get rid of it, edit the code when it is not running (any edit will do), put it back the way it was, then save it then compile it. Should be gone. If not, I might have the save/compile backwards but I don't know if it matters.

If that's not the issue, state what the error message is.
 

Users who are viewing this thread

Top Bottom