Report Sum If <=

[ChrisG]

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.

 

[Jeff Boyce]

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]

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

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

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.

 

[Jeff Boyce]

So, I saw nothing in the query's SQL statement that mentioned <=24.

Perhaps you need to do more than one query, and "chain" them together?

Regards

Jeff Boyce
Microsoft Office/Access MVP

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

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

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]

Jeff,

Took your advise and made two queries. I wasn't able to use =IIf([Sum Of
TimeHours]<=24,"YES","NO") in the critera cell as it was prompting for a
value of ([Sum Of TimeHours] so I made a report. It the long way and I hope
to improve it while I learn more. Thanks for the help.


SELECT [Employee Info].LastName, Events.AttendanceCode, Events.TimeHours
FROM [Employee Info] LEFT JOIN Events ON [Employee Info].EmployeeNumber =
Events.EmployeeID
WHERE (((Events.AttendanceCode) Not Like "V" And (Events.AttendanceCode) Not
Like "VX" And (Events.AttendanceCode) Not Like "B" And
(Events.AttendanceCode) Not Like "J") AND ((Events.EmployeeID) Not Like
"0225" And (Events.EmployeeID) Not Like "0219" And (Events.EmployeeID) Not
Like "0104"))
ORDER BY [Employee Info].LastName;

SELECT DISTINCTROW AWARD1.LastName, Sum(AWARD1.TimeHours) AS [Sum Of
TimeHours]
FROM AWARD1
GROUP BY AWARD1.LastName;


--
ChrisG

So, I saw nothing in the query's SQL statement that mentioned <=24.

Perhaps you need to do more than one query, and "chain" them together?

Regards

Jeff Boyce
Microsoft Office/Access MVP

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

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

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.