"and" criteria

[lwilde]

I'm very new to Access, so I"m not too sure how to create a query to get the
results I want.

What I have done so far is import a spreadsheet into a table that contains
the hours an employee has worked each week, showing the employee id,
beginning date of the week, ending date of the week, standard hours, and
hours worked.

What I would like Access do for me is give me a list of employees who have
worked more than their standard hours and count how many consecutive weeks
they have done so counting backwards.

For example, employee A has standard hours of 15 (meaning 15 hours per week)
and the employee has worked the following hours:
wk1 - 23.5
wk2 - 24
wk3 - 32
wk4 - 22.5
wk5 - 23
wk6 - 25

I would like access to return a result that this employee has worked the
last 6 consecutive weeks over their standard hours. If say, wk 4 was only
14, then I would want the result to show only the last 2 consecutive weeks.

How would I write this expression?

 

[Michel Walsh]

One of the possible solution, involving many queries in sequence, can be to
group on value minus rank. With details: first, make a query which removes
the failing weeks:


SELECT employeeID, weekNumber
FROM tableName
WHERE hourMade >= 15


saved as Q1. The result can be:

EmployeeID weekNumber
1010 1
1010 2
1010 3
1010 5
1010 6
1111 ...
.... ...



Next, a query will rank the week numbers, to produce, say:

EmployeeID weekNumber rank
1010 1 1
1010 2 2
1010 3 3
1010 5 4
1010 6 5
1111 ... ...
... ... ...


SELECT a.employeeID, a.weekNumber, COUNT(*) AS rank
FROM q1 AS a INNER JOIN q1 AS b
ON a.employeeID=b.employeeID
AND a.weekNumber >= b.weekNumber
GROUP BY a.employeeiD, a.weekNumber



saved as Q2.


We can observe that contiguous weeks produce a constant value for
week-rank:



EmployeeID weekNumber rank weekNumber-rank
1010 1 1 0
1010 2 2 0
1010 3 3 0
1010 5 4 1
1010 6 5 1
1111 ... ... ...
... ... ... ...


SELECT employeeID, MAX(weekNumber) AS mweek, COUNT(*) AS stride
FROM q2
GROUP BY employeeID, weekNumber-rank



saved as Q3 returns

EmployeeID mweek stride
1010 3 3
1010 6 2
1011 ... ...
....


which we can read as: for employeeID 1010, the week 3 ends a continuous
sequence of 3 weeks and week 6 ends a continuous sequence of 2 weeks.


You can end with a final query which pumps only the data from Q3, for mweek
= 6 (the lastest week in the original data).





Vanderghast, Access MVP