If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"and" criteria
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? |
#2
|
|||
|
|||
"and" criteria
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 "lwilde" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|