Greater Than in Query

[rplum]

Hi Guys,

Total rookie with Access and just learning. My work is wanting me to build a small database that pulls data from a live table. They want it to pull PAYCODE "Regular Hours", "Overtime Worked" and "Stat Worked" however they want to calculate Regular Hours > 1000 and Overtime Worked and Stat Worked < 1000. I have it pulling Regular Hours > 1000 right now I just don't know how to get it to pull Overtime Worked and Stat Worked < 1000 from the HRS_WORKED field.


SELECT Transline.PERSONFULLNAME, Transline.HRS_WORKED, Transline.PAYCODENAME
FROM Transline


WHERE (((Transline.HRS_WORKED)>1000))
GROUP BY Transline.PERSONFULLNAME, Transline.HRS_WORKED, Transline.PAYCODENAME
HAVING (((Transline.PAYCODENAME)="Regular Hours"));


Thanks!

[JoeM]

Can you post a small sample so we can see that your data looks like, especially all the fields being referenced in your query?
For each person and each PayCodeName, will there only be one record, or might there be multiple records that need to be added up?

[rplum]

[JoeM]

Since you blanked the name field for everyone, we cannot tell what the answer to the question I asked in my previous reply is. So we will have to rely on you to tell us what the answer is. It is very important to know, as it will change how we approach the solution.

For each person and each PayCodeName, will there only be one record, or might there be multiple records that need to be added up?

So, for example, let's say we have a person named "Bob".
Can Bob have multiple Regular Hours entries, or will he only ever have exactly one?
And is that true for all the different PayCodeName entries?

[rplum]

He can only have one Regular Hours. The same goes with Overtime Worked and Stat Worked.

Sorry, I have reupload one with names.

[JoeM]

Try this code:

SELECT 
    Transline.PERSONFULLNAME, 
    Sum(IIf([Transline]![PAYCODENAME]="Regular Hours",[Transline]![HRS_WORKED],0)) AS Regular_Hours, 
    Sum(IIf([Transline]![PAYCODENAME]="Overtime Worked",[Transline]![HRS_WORKED],0)) AS Overtime_Worked, 
    Sum(IIf([Transline]![PAYCODENAME]="Stat Worked 1.5 Hours",[Transline]![HRS_WORKED],0)) AS Stat_Worked_1_5_Hours
FROM Transline
GROUP BY Transline.PERSONFULLNAME
HAVING 
    (((Sum(IIf([Transline]![PAYCODENAME]="Regular Hours",[Transline]![HRS_WORKED],0)))>1000) 
    AND ((Sum(IIf([Transline]![PAYCODENAME]="Overtime Worked",[Transline]![HRS_WORKED],0)))<1000) 
    AND ((Sum(IIf([Transline]![PAYCODENAME]="Stat Worked 1.5 Hours",[Transline]![HRS_WORKED],0)))<1000));

[rplum]

That worked perfectly!!!


Thanks so much for the help Joe.

[JoeM]

You are welcome!

The key was to create a calculated field for each type that either returns the hours (if it is that type) or a 0 (if it is not).
Then Group the records by Name and sum those calculated fields to get a single line for each Name showing their hours for each type.
Then simply apply the criteria to those calculated fields.