Counting Frequency of numbers & comparing to frequency of another set of numbers in same row

[lhilde01]

I’m hoping someone can help me. I have a table with fields like this one but the weeks go all the way up to 52. What I am trying to do is count the number of consecutive zero’s and if it is more than five, count how many of the following fields have a number in them and if that number is less than the number of zero’s preceding it identify that person.

For example Joe would be identified below because he had 6 consecutive zero’s and then he had 5 weeks of numbers immediately following the string of zero’s. Bob would not be identified because he had 5 consecutive zero’s and then 5 sets of numbers immediately following the string of zero’s so the zero frequency isn’t higher than the number frequency immediately following.

I hope this makes sense – any help would be greatly appreciated.

ID	Name	Date of Hire	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16
25	Joe	2/5/12	25	0	0	0	0	0	0	40	40	40	40	40	0	0	20	20
30	Sue	4/1/14	0	0	0	0	0	40	40	40	40	40	25	0	0	0	0	40
23	Bob	12/12/13	25	40	0	0	0	0	0	40	40	40	40	40	0	40	40	0

[Dal Jeanis]

Part of the issue is problematic table design. You'd almost have to code a program, rather than a query.

Normally, you don't want to store values like that in a horizontal table, because cases like this require a lot of code. Consider the following two designs:

TblEmpHours
  ID
  Name
  HireDate
  HoursWeek01
  HoursWeek02 
  HoursWeek03
  HoursWeek04
...
  HoursWeek52

TblEmp
   EmpID
   Name
   HireDate

TblEmpHours
   EmpID  (foreign key to tblEmp)
   WeekDate
   WeekHours

Your particular problem might be best handled by a program anyway, because counting *consecutive* zeroes isn't a simple query either. However, I'm thinking it can be done with a correlated subquery on the second table design. Let me give it some thought and see if I can find a solution.

[June7]

Usually, alternative to a lot of code in this situation is to manipulate the data into a normalized structure with a UNION query. Then use the UNION query as datasource for an aggregate query.

A UNION query is limited to 50 SELECT lines - unfortunately you need 52.

However, the analysis you want is unusual and I am not sure normalized structure will make it any easier.

[lhilde01]

Thanks Dal Jeanis, any help you can give me would be greatly appreciated!

[Dal Jeanis]

Okay, I agree with June's comment, you might not be able to do this easily with a single select. That means a procedure of some sort.

To really help you, I need to know what the Week numbers really mean. Is every employee record on the same year, or do those represent weeks after hiring?

In plain English, what is it that you are trying to determine?

I'm assuming that the numbers are hours worked in a week. (since you have lots of 40s). I'm assuming that, in English, your task is this -

Find anyone who had N weeks off in a row, where N >=5, and then worked less than N+1 weeks before their next week off.

We can break this down into two sections -
1) Find the last week that an employee took off in each sequence, where he/she had taken at least 5 weeks off in a row (Find N for that week).
2) For each record in that result set, check whether the employee took a week off before N+1 weeks had passed.

In a normalized database, for section 1, I believe we can achieve the result set in a single query. For section 2, it's trivial.

For now, we'll assume that rather than rewrite your database, you're going to create a temporary table that is normalized,
and insert the data into it. Then you can run a sub-select query against it.

Let me look around a little and find a thread where I wrote a similar query.

[Dal Jeanis]

All right, I'm going to write this out very methodically. Many of these steps can be compressed, but I'm going to treat them one at a time because that way I know the solution will, in fact, work.
First, create a temporary table. Here's the layout

tmptblEmpWeeks
   ttKey      autokey
   EmpID      foreign key to Emp
   WeekNo     Number     1 to 52
   WeekDate   Date       (optional field)
   WeekHours  Number     0, 25, 40 or whatever
   CumCode0   Number   
   CumCode1   Number  
   CumCode2   Number  
   CumCode3   Number

You will need code to clear out the temp table, then insert 1 record for each column (Week1 - Week52). That code is long but basic. CumCode0 will be 0 if Weekhours is 0, otherwise 1. Initialize the rest of the CumCode fields to 0.

Second, create a table that has this layout and insert the following fixed data into that table. This table simplifies the code for which weeks get compared to which other weeks:

tmptblCompWeek
  ThisWeekNo   Number
  NextWeekNo   Number 
Fixed Data
 ThisWeekNo   NextWeekNo
    1            2
    2            3
    3            4
    4            5
  ...          ...
   50           51
   51           52

Now, we'll start updating the CumCodes. First, CumCode1 will be used to determine if a particular week is the last week in a string of consecutive absences or presences.
Warning - this is aircode. Access is finnicky about the binds in update queries, and I've sometimes had to create temp tables to hold intermediate results, using a method like this.

DELETE from TempKeys;

SELECT TW1.TTKey Into TempKeys
FROM 
   tmptblEmpWeeks AS TW1
   INNER JOIN
      (tmptblEmpWeeks AS TW2
      INNER JOIN
      tmptblCompWeek AS TCW
      ON TCW.NextWeekNo = TW2.WeekNo)
   ON TCW.ThisWeekNo = TW1.WeekNo
   AND TW1.EmpID = TW2.EmpID
WHERE 
   TW1.CumCode0 NOT EQUAL TW2.CumCode0;

UPDATE 
   tmptblEmpWeeks AS TW 
   INNER JOIN 
   TempKeys AS TK
   ON TW.TTKey = TK.TTKey
SET 
   TW.CumCode1 = 1;

UPDATE 
   tmptblEmpWeeks AS TW 
SET 
   TW.CumCode1 = 1
WHERE 
   WeekNo = 52;

Now, any employee week that is the last in a string of zero, or nonzero, weeks, has been marked with a 1 in CumCode1.

The next item will be used to mark each week with a code for which sequence it belongs to. It happens to be easier to do in descending sequence (where week 52 is designated group 1, the next earlier group is group 2, and so on). This query gets the values for each record:

qryCumCode2
SELECT  TW1.EmpNo, TW1.TTKey, First(TW1.WeekNo) AS WeekNo, First(TW1.CumCode0) As CumCode0, Sum(TW2.CumCode1)  AS GroupNo
FROM 
   tmptblEmpWeeks AS TW1
   INNER JOIN
   tmptblEmpWeeks AS TW2
   ON TW1.EmpID = TW2.EmpID
WHERE 
   TW1.WeekNo <= TW2.WeekNo
GROUP BY TW1.EmpNo, TW1.TTKey;

I was intending to store those values in the tem p record, but I think we can get away with just using queries. We'll see.

Now, you can just sum the results off that query in a different order to get your number of weeks in a row for that group.

qryCumCode3
SELECT  QC.EmpNo, QC.GroupNo, Max(QC.WeekNo) As CumWeekNo, First(QC.CumCode0) As CumCode0, Count(*) AS GroupCount
FROM qryCumCode2 as QC
GROUP BY QC.EmpNo, QC.GroupNo
ORDER BY QC.EmpNo, QC.GroupNo;

And that query gives you everything you absolutely need to answer your question. This should get you a list of the Employee Number, and the number of his last vacation/out week, where he did not work at least that many weeks in the next group.

qryCumCode4
SELECT  
   QC1.EmpNo, 
   QC1.GroupNo, 
   QC1.CumCode0, 
   QC1.CumWeekNo As OutWeekNo, 
   QC1.GroupCount AS OutCount, 
   QC2.CumWeekNo AS InWeekNo, 
   QC2.GroupCount AS InCount
FROM 
   qryCumCode3 as QC1 
   INNER JOIN 
   qryCumCode3 as QC2 
   ON QC1.EmpNo = QC2.EmpNo
   AND QC1.GroupNo = QC2.GroupNo + 1 
WHERE QC1.CumCode0 = 0
AND   QC1.GroupCount > QC2.GroupCount;