Re: Report Sum If <=

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Jeff,

I understand you reply but I wasn't able to get it to work. Perhaps my
request is more complicated then I orginally explained. I wanted to get the
sum of the hours for attendance codes and then test for the sum for each
employee where the SUM is <= 24. I afraid I not as knowlegable as I would
like to be on creating function syntax in a query. Any help or direction is
greatly appreciated.

SELECT DISTINCTROW [Employee Info].LastName, Events.EmployeeID,
Events.AttendanceCode, Sum(Events.TimeHours) AS SumOfTimeHours
FROM [Employee Info] RIGHT JOIN Events ON [Employee Info].EmployeeNumber =
Events.EmployeeID
GROUP BY [Employee Info].LastName, Events.EmployeeID, Events.AttendanceCode
HAVING (((Events.AttendanceCode) Like "L" Or (Events.AttendanceCode)="LX" Or
(Events.AttendanceCode)="A" Or (Events.AttendanceCode)="AX" Or
(Events.AttendanceCode)="WC" Or (Events.AttendanceCode)="D" Or
(Events.AttendanceCode)="F" Or (Events.AttendanceCode)="S" Or
(Events.AttendanceCode)="VAX"));



--
ChrisG


"Jeff Boyce" wrote:

Chris

If I'm understanding what you are trying to do, do it in the query. First
create the new expression (your IIF statement). Then, in the
Selection/Criteria "cell", enter "Yes".

Paraphrased in English, create a calculated value of either "Yes" or "No",
based on the size of my [sum of hours] field, then only return rows where
this calculated value is "Yes".

Regards

Jeff Boyce
Microsoft Office/Access MVP

"ChrisG" <ChrisG@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:00A20660-8688-4670-8629-5B884B84ECED@xxxxxxxxxxxxxxxx
I want to make a report from a query that extracts coded events L, LX etc
and
the hours associated with these codes. I need help with the correct
syntax
for this expression.

sum([sum of hours])
IIf ([sum of hours] <= 24, "Yes", "No")

I would like to display only those entries who meet the criteria or flag
the
YES entries.

--
ChrisG



.

Relevant Pages

  • Re: Report Sum If <=
    ... value of ([Sum Of TimeHours] so I made a report. ... ORDER BY [Employee Info].LastName; ... "Jeff Boyce" wrote: ... Perhaps you need to do more than one query, ...
    (microsoft.public.access.reports)
  • Re: summary by weeks
    ... Then I would focus on creating a query that allows you ... > weekly sum, by employee, other than in the report generator. ... > "Jeff Boyce" wrote: ...
    (microsoft.public.access.queries)
  • Re: Multiple Sheets and Columns
    ... Column E = values to sum ... Valko" wrote: ... Each Date has like 10 entries below before going to the next Date. ... I have a formula that gets all the totals in one bucket, ...
    (microsoft.public.excel.worksheet.functions)
  • RE: Sum of a range of cells that include cells using LOOKUP
    ... I've made a spreadsheet listing employee benefits. ... Column G contains various adjustments ... Here is my problem: I am trying to sum H1:H57 and it is not working at all. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Employee count for HR Cube
    ... You cannot really sum a distinct count. ... Darren Gosbell - SQL Server MVP ... But the given soln got sum up from year to year as i want the employee ... Depending on the pattern of you data ...
    (microsoft.public.sqlserver.olap)