Querey pulling Max number not working (1 Viewer)

msanderson

New member
Local time
Yesterday, 19:50
Joined
Mar 27, 2015
Messages
3
I have a table in Access with corrected pay data for a whole year. The important columns are PPC, PPP, wk1hours, wk2hours. I want to sum the week one and two hours but only for the most highest pay period processed(PPP). So a time sheet for pay period 10 was corrected and processed in pay period 11. It was then corrected and processed again in pay period 12. I only want the totals for pay period 12.

SELECT [combined corrected TA data].lastname, [combined corrected TA data].[Fiscal Year], [combined corrected TA data].ppcov, Max([combined corrected TA data].PPproc) AS MaxOfPPproc, Sum([wk1hrs]+[wk2hrs]) AS Hours
FROM [combined corrected TA data]
GROUP BY [combined corrected TA data].lastname, [combined corrected TA data].[Fiscal Year], [combined corrected TA data].ppcov
HAVING ((([combined corrected TA data].ppcov)="10"));


In this example the results show PPP as 12, but it's totaling the hours from PPP11 and PPP12 instead of just 12, giving me duplicate hours.
 

plog

Banishment Pending
Local time
Yesterday, 19:50
Joined
May 11, 2011
Messages
11,613
Can you demonstrate your issue with data? Please provide 2 sets:

A. Starting sample data from [combined corrected TA data]. Include field names and enough sample data to cover all cases.

B. Expected results based on A. Shwo what data you expect your query to return based on that initial data you provide in A.

My gut says this is going to take a sub-query to identify the records you want.
 

msanderson

New member
Local time
Yesterday, 19:50
Joined
Mar 27, 2015
Messages
3
For Example, Here is a table I created since the original has PII.
Table1 ID EmployeeID PPC PPP Wk1Hrs Wk2Hrs 1 123456 10 11 10 10 2 123456 10 11 2 6 3 123456 10 12 10 8 4 123456 10 12 2 6 5 654321 08 15 10 10 6 654987 11 12 10 10

So I want the totals of Wk1 And Wk2 for the last pay period processed(PPP). If I use Max for PPP in the query it shows MaxOfPPP as 12 but totals the hours for 11 and 12 for EmployeeID 123456. It should be 26 not 54.
Query1 EmployeeID PPC MaxOfPPP Hours 123456 10 12 54 654321 08 15 20 654987 11 12 20
 

msanderson

New member
Local time
Yesterday, 19:50
Joined
Mar 27, 2015
Messages
3
Tables didn't paste correct. Let me try attaching a document.
 

Attachments

  • Query.zip
    228.3 KB · Views: 70

JHB

Have been here a while
Local time
Today, 01:50
Joined
Jun 17, 2012
Messages
7,732
You can do it using 2 queries:

  • The first one find the max in [PPP]
  • Then link the above query to your table and do the summing.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:50
Joined
May 7, 2009
Messages
19,169
SELECT T1.[lastname], T1.[Fiscal Year], T1.[ppcov], T1.PPProc, Sum(T1.[wk1hrs]+T1.[wk2hrs]) As Hours FROM [combined corrected TA data] AS T1
GROUP BY T1.lastname, T1.[Fiscal Year], T1.ppcov
HAVING ((T1.ppcov)="10") AND (T1.PPProc IN (SELECT MAX([PPProc]) FROM [combined corrected TA data]))
 
Last edited:

Users who are viewing this thread

Top Bottom