Windows XP 
Access 2007
I have 2 spreadsheets:
This first shows violations. This spreadsheet has duplicate names on it and additional names (some new and some duplicate) will be added on a daily basis.
Beg Date End Date Employee Number First Name Middle Name Last Name Category Sub Cat Minutes Note Excused Week Day Violation Date Hire Date Supervisor Points Date Notified of Weekly Points 08/02/15 08/02/15 10908 FN 1 R LN 1 Other (See Notes) Forgot to Clock In/Out 0.00996 Notes FALSE Sunday 08/02/15 04/11/06 PA Guards 1 08/02/15 08/02/15 16737 FN 2 R LN 2 No Call No Show No Call No Show 6.00 Notes TRUE Sunday 08/02/15 10/02/13 PA Guards 15 08/02/15 08/02/15 19018 FN 3 R LN 3 No Call No Show No Call No Show 6.00 Notes FALSE Sunday 08/02/15 07/29/15 PA Guards 15 08/02/15 08/02/15 18820 FN 4 R LN 4 Late Late:Fri to Sun 31 to 60 Min 0.45 Notes FALSE Sunday 08/02/15 06/12/15 NY Guards 3.5
The second is Pivot table which looks at the above and summarizes the amount of violations and total points for each person:
EEN Last Name # of Violations Total Points 10908 LN1 1 1 16737 LN2 1 15 19018 LN3 1 6 18820 LN4 2 15
I need to summarize the number of the points & the #of violations for each employee on a third sheet (below), but also maintain the existing information for each employee recorded in all the columns below after the "Total Points" (starting with Verbal [Date Written]). The last 15 columns would be manually updated.
EEN First Name Last Name # of Violations Total Points Verbal (Date Written) Points @time of Verbal Verbal (Date Counseled) Written (Date Written) Points @time of Written Written (Date Counseled) Final (Date Written) Points @time of FINAL Final (Date Counseled) Susp(Date Written) Points @time of SUSP Susp (Date Counseled) Term (Date Written) Points @time of TERM Term (Date Counseled) 10908 FN 1 LN 1 1 1 08/01/15 1 08/02/15 16737 FN 2 LN 2 1 15 08/06/15 15 19018 FN 3 LN 3 1 6 08/05/15 6 16737 FN 3 LN 3 2 15 08/01/15 8 08/02/15 08/08/15 15
I was thinking I could create a cross tab for the first sheet and a regular query for the second sheet and then create a third Query which would somehow yield the results I need to see on the third sheet. However, when I tried creating a crosstab for the first sheet to summarize all the employee's, it did not come out correctly. Also, I am not exactly sure what I would need to do update the third sheet while maintaining the last 15 columns information to each employee?
Reply With Quote
