Help with aggregate function

[Ian]

Hello, all.

I am building a fairly simple time tracking DB. One of the reports I need to
write calculates the persons Comp Time on a weekly basis. There are two types
of employees: Hourly and Salaried. For Hourly, anything over 40 accrues comp
time; for Salaried, anything over 45 does so.

I have a report with two groupings: Week number inside Employee number. Week
number is defined as DatePart("ww","tblTimeEntries.tdate), or the week number
part taken out of the week the time was reported. This works fine.

Within that grouping, I have no problems calculating the correct number of
hours they "should" work as

=IIf([PersonType]="Salaried",45,40)

and in the Employee group, I just SUM that and it gives me the correct
number of hours they should have worked.

Also in the weekly grouping, I calculate the hours they did work as:

=Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))

[note that tjob is a job code, and X0012 represents lunch. They don't get
credit for this time.

Again, Putting this in the Employee group using a sum gives me what I expect.

Now, in order to get the number of hours comp time they are due that week, I
just subtract them, with the additional caveat that if less than 0, they are
due 0 hours. In the week group, that looks like:

=IIf(Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40)<0,0,Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40))

And that seems to work fine for each week.

Now the difficulty:

If I then take that expression and put it into the Employee group, thus:

=SUM(IIf(Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40)<0,0,Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40)))

I get an error 3095, cannot have aggregate function in expression.

How can I fix this? My SQL is not up to calculating the weekly available
time in a Query, althought that seems like it might work. Is that the answer?

Thanks for the help. I really appreciate it.

///Ian

 

[Duane Hookom]

Two small semi-issues that I would change would be to create a table to
store PersonType and CompTimeAt with records like:
PersonType CompTimeAt
Salaried 45
Hourly 40

I would create another table of tjob and Credit
TJob Credit
X0012 0
... 1
... 1

You can then add these to your query to multiply CreditHrs: Credit *
([tStopTime]-[tStartTime]) * 24 to get credited time.

Create your solution based on data values rather than hard-coded values.

 

[Ian]

Thanks for the ideas. I have created the first table you suggested
(PersonType and CompTimeAt) to get rid of that inline if statement.

Unfortunately, the job types are not a predetermined list, so I can't really
do that table as you suggest: the user will be free to type in anything they
like. A few items are predetermined (such as lunch, sick, vacation), but
others will be made up on the spot.

I will see if losing that inline if statement cures my issue, but I am not
confident. Look for a further post soon.

///Ian

Two small semi-issues that I would change would be to create a table to
store PersonType and CompTimeAt with records like:
PersonType CompTimeAt
Salaried 45
Hourly 40

I would create another table of tjob and Credit
TJob Credit
X0012 0
... 1
... 1

You can then add these to your query to multiply CreditHrs: Credit *
([tStopTime]-[tStartTime]) * 24 to get credited time.

Create your solution based on data values rather than hard-coded values.
--
Duane Hookom
MS Access MVP

Hello, all.

I am building a fairly simple time tracking DB. One of the reports I need
to
write calculates the persons Comp Time on a weekly basis. There are two
types
of employees: Hourly and Salaried. For Hourly, anything over 40 accrues
comp
time; for Salaried, anything over 45 does so.

I have a report with two groupings: Week number inside Employee number.
Week
number is defined as DatePart("ww","tblTimeEntries.tdate), or the week
number
part taken out of the week the time was reported. This works fine.

Within that grouping, I have no problems calculating the correct number of
hours they "should" work as

=IIf([PersonType]="Salaried",45,40)

and in the Employee group, I just SUM that and it gives me the correct
number of hours they should have worked.

Also in the weekly grouping, I calculate the hours they did work as:

=Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))

[note that tjob is a job code, and X0012 represents lunch. They don't get
credit for this time.

Again, Putting this in the Employee group using a sum gives me what I
expect.

Now, in order to get the number of hours comp time they are due that week,
I
just subtract them, with the additional caveat that if less than 0, they
are
due 0 hours. In the week group, that looks like:

=IIf(Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40)<0,0,Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40))

And that seems to work fine for each week.

Now the difficulty:

If I then take that expression and put it into the Employee group, thus:

=SUM(IIf(Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40)<0,0,Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40)))

I get an error 3095, cannot have aggregate function in expression.

How can I fix this? My SQL is not up to calculating the weekly available
time in a Query, althought that seems like it might work. Is that the
answer?

Thanks for the help. I really appreciate it.

///Ian

 

[Ian]

Still have the problem. Can anyone help me out?

Thanks for the ideas. I have created the first table you suggested
(PersonType and CompTimeAt) to get rid of that inline if statement.

Unfortunately, the job types are not a predetermined list, so I can't really
do that table as you suggest: the user will be free to type in anything they
like. A few items are predetermined (such as lunch, sick, vacation), but
others will be made up on the spot.

I will see if losing that inline if statement cures my issue, but I am not
confident. Look for a further post soon.

///Ian

Two small semi-issues that I would change would be to create a table to
store PersonType and CompTimeAt with records like:
PersonType CompTimeAt
Salaried 45
Hourly 40

I would create another table of tjob and Credit
TJob Credit
X0012 0
... 1
... 1

You can then add these to your query to multiply CreditHrs: Credit *
([tStopTime]-[tStartTime]) * 24 to get credited time.

Create your solution based on data values rather than hard-coded values.
--
Duane Hookom
MS Access MVP

Hello, all.

I am building a fairly simple time tracking DB. One of the reports I need
to
write calculates the persons Comp Time on a weekly basis. There are two
types
of employees: Hourly and Salaried. For Hourly, anything over 40 accrues
comp
time; for Salaried, anything over 45 does so.

I have a report with two groupings: Week number inside Employee number.
Week
number is defined as DatePart("ww","tblTimeEntries.tdate), or the week
number
part taken out of the week the time was reported. This works fine.

Within that grouping, I have no problems calculating the correct number of
hours they "should" work as

=IIf([PersonType]="Salaried",45,40)

and in the Employee group, I just SUM that and it gives me the correct
number of hours they should have worked.

Also in the weekly grouping, I calculate the hours they did work as:

=Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))

[note that tjob is a job code, and X0012 represents lunch. They don't get
credit for this time.

Again, Putting this in the Employee group using a sum gives me what I
expect.

Now, in order to get the number of hours comp time they are due that week,
I
just subtract them, with the additional caveat that if less than 0, they
are
due 0 hours. In the week group, that looks like:

=IIf(Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40)<0,0,Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40))

And that seems to work fine for each week.

Now the difficulty:

If I then take that expression and put it into the Employee group, thus:

=SUM(IIf(Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40)<0,0,Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40)))

I get an error 3095, cannot have aggregate function in expression.

How can I fix this? My SQL is not up to calculating the weekly available
time in a Query, althought that seems like it might work. Is that the
answer?

Thanks for the help. I really appreciate it.

///Ian

 

[Duane Hookom]

I was hoping you would come back with the expressions using the new
table/fields.
--
Duane Hookom
MS Access MVP

Still have the problem. Can anyone help me out?

Thanks for the ideas. I have created the first table you suggested
(PersonType and CompTimeAt) to get rid of that inline if statement.

Unfortunately, the job types are not a predetermined list, so I can't
really
do that table as you suggest: the user will be free to type in anything
they
like. A few items are predetermined (such as lunch, sick, vacation), but
others will be made up on the spot.

I will see if losing that inline if statement cures my issue, but I am
not
confident. Look for a further post soon.

///Ian

Two small semi-issues that I would change would be to create a table to
store PersonType and CompTimeAt with records like:
PersonType CompTimeAt
Salaried 45
Hourly 40

I would create another table of tjob and Credit
TJob Credit
X0012 0
... 1
... 1

You can then add these to your query to multiply CreditHrs: Credit *
([tStopTime]-[tStartTime]) * 24 to get credited time.

Create your solution based on data values rather than hard-coded
values.
--
Duane Hookom
MS Access MVP


Hello, all.

I am building a fairly simple time tracking DB. One of the reports I
need
to
write calculates the persons Comp Time on a weekly basis. There are
two
types
of employees: Hourly and Salaried. For Hourly, anything over 40
accrues
comp
time; for Salaried, anything over 45 does so.

I have a report with two groupings: Week number inside Employee
number.
Week
number is defined as DatePart("ww","tblTimeEntries.tdate), or the
week
number
part taken out of the week the time was reported. This works fine.

Within that grouping, I have no problems calculating the correct
number of
hours they "should" work as

=IIf([PersonType]="Salaried",45,40)

and in the Employee group, I just SUM that and it gives me the
correct
number of hours they should have worked.

Also in the weekly grouping, I calculate the hours they did work as:

=Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))

[note that tjob is a job code, and X0012 represents lunch. They don't
get
credit for this time.

Again, Putting this in the Employee group using a sum gives me what I
expect.

Now, in order to get the number of hours comp time they are due that
week,
I
just subtract them, with the additional caveat that if less than 0,
they
are
due 0 hours. In the week group, that looks like:

=IIf(Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40)<0,0,Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40))

And that seems to work fine for each week.

Now the difficulty:

If I then take that expression and put it into the Employee group,
thus:

=SUM(IIf(Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40)<0,0,Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40)))

I get an error 3095, cannot have aggregate function in expression.

How can I fix this? My SQL is not up to calculating the weekly
available
time in a Query, althought that seems like it might work. Is that the
answer?

Thanks for the help. I really appreciate it.

///Ian

 

[Ian]

Oops. Sorry. Here they are:

ALL THE BELOW ARE IN THE WEEK GROUP
----------------------------------------------------
For expected time this week:
=[ptCompTimeAt]

For actual time this week:
=Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))
[recall that I could not put the codes into a DB as you suggested - although
I certainly see the advantage, and if you can think of another way...perhaps
use 1 as a default and list only the 0's?]

For difference (comp time accrued this week)
=IIf(Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-ptCompTimeAt<0,0,Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-[ptCompTimeAt])

The formulas in the Person Group are just those surrounded by a SUM().

The error message is:
Cannot have aggregate function in expression
sum(=IIf(Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-ptCompTimeAt<0,0,Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-[ptCompTimeAt]))

From what I can see, if I can finally do away with the IIF for the case when
the difference is less than zero, I am there. Any suggestions there? Is there
a rounding function that works that way?

Thanks again for all the help.

///Ian

I was hoping you would come back with the expressions using the new
table/fields.
--
Duane Hookom
MS Access MVP

Still have the problem. Can anyone help me out?

Thanks for the ideas. I have created the first table you suggested
(PersonType and CompTimeAt) to get rid of that inline if statement.

Unfortunately, the job types are not a predetermined list, so I can't
really
do that table as you suggest: the user will be free to type in anything
they
like. A few items are predetermined (such as lunch, sick, vacation), but
others will be made up on the spot.

I will see if losing that inline if statement cures my issue, but I am
not
confident. Look for a further post soon.

///Ian

:

Two small semi-issues that I would change would be to create a table to
store PersonType and CompTimeAt with records like:
PersonType CompTimeAt
Salaried 45
Hourly 40

I would create another table of tjob and Credit
TJob Credit
X0012 0
... 1
... 1

You can then add these to your query to multiply CreditHrs: Credit *
([tStopTime]-[tStartTime]) * 24 to get credited time.

Create your solution based on data values rather than hard-coded
values.
--
Duane Hookom
MS Access MVP


Hello, all.

I am building a fairly simple time tracking DB. One of the reports I
need
to
write calculates the persons Comp Time on a weekly basis. There are
two
types
of employees: Hourly and Salaried. For Hourly, anything over 40
accrues
comp
time; for Salaried, anything over 45 does so.

I have a report with two groupings: Week number inside Employee
number.
Week
number is defined as DatePart("ww","tblTimeEntries.tdate), or the
week
number
part taken out of the week the time was reported. This works fine.

Within that grouping, I have no problems calculating the correct
number of
hours they "should" work as

=IIf([PersonType]="Salaried",45,40)

and in the Employee group, I just SUM that and it gives me the
correct
number of hours they should have worked.

Also in the weekly grouping, I calculate the hours they did work as:

=Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))

[note that tjob is a job code, and X0012 represents lunch. They don't
get
credit for this time.

Again, Putting this in the Employee group using a sum gives me what I
expect.

Now, in order to get the number of hours comp time they are due that
week,
I
just subtract them, with the additional caveat that if less than 0,
they
are
due 0 hours. In the week group, that looks like:

=IIf(Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40)<0,0,Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40))

And that seems to work fine for each week.

Now the difficulty:

If I then take that expression and put it into the Employee group,
thus:

=SUM(IIf(Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40)<0,0,Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40)))

I get an error 3095, cannot have aggregate function in expression.

How can I fix this? My SQL is not up to calculating the weekly
available
time in a Query, althought that seems like it might work. Is that the
answer?

Thanks for the help. I really appreciate it.

///Ian

 

[Duane Hookom]

I would simplify this by creating a new field/column in the query that
calculates the nonlunch time:
WrkHours: Abs([tjob]<>'X0012') * ([tStopTime]-[tStartTime])*24
Then your report control sources can use much simpler expressions:
=IIf(Sum(WrkHours)<ptCompTimeAt, 0, Sum(WrkHours) - ptCompTimeAt)
You can sum this in other group footers.

--
Duane Hookom
MS Access MVP

Oops. Sorry. Here they are:

ALL THE BELOW ARE IN THE WEEK GROUP
----------------------------------------------------
For expected time this week:
=[ptCompTimeAt]

For actual time this week:
=Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))
[recall that I could not put the codes into a DB as you suggested -
although
I certainly see the advantage, and if you can think of another
way...perhaps
use 1 as a default and list only the 0's?]

For difference (comp time accrued this week):
=IIf(Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-ptCompTimeAt<0,0,Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-[ptCompTimeAt])

The formulas in the Person Group are just those surrounded by a SUM().

The error message is:
Cannot have aggregate function in expression
sum(=IIf(Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-ptCompTimeAt<0,0,Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-[ptCompTimeAt]))

From what I can see, if I can finally do away with the IIF for the case
when
the difference is less than zero, I am there. Any suggestions there? Is
there
a rounding function that works that way?

Thanks again for all the help.

///Ian

I was hoping you would come back with the expressions using the new
table/fields.
--
Duane Hookom
MS Access MVP

Still have the problem. Can anyone help me out?

:

Thanks for the ideas. I have created the first table you suggested
(PersonType and CompTimeAt) to get rid of that inline if statement.

Unfortunately, the job types are not a predetermined list, so I can't
really
do that table as you suggest: the user will be free to type in
anything
they
like. A few items are predetermined (such as lunch, sick, vacation),
but
others will be made up on the spot.

I will see if losing that inline if statement cures my issue, but I am
not
confident. Look for a further post soon.

///Ian

:

Two small semi-issues that I would change would be to create a table
to
store PersonType and CompTimeAt with records like:
PersonType CompTimeAt
Salaried 45
Hourly 40

I would create another table of tjob and Credit
TJob Credit
X0012 0
... 1
... 1

You can then add these to your query to multiply CreditHrs: Credit *
([tStopTime]-[tStartTime]) * 24 to get credited time.

Create your solution based on data values rather than hard-coded
values.
--
Duane Hookom
MS Access MVP


Hello, all.

I am building a fairly simple time tracking DB. One of the reports
I
need
to
write calculates the persons Comp Time on a weekly basis. There
are
two
types
of employees: Hourly and Salaried. For Hourly, anything over 40
accrues
comp
time; for Salaried, anything over 45 does so.

I have a report with two groupings: Week number inside Employee
number.
Week
number is defined as DatePart("ww","tblTimeEntries.tdate), or the
week
number
part taken out of the week the time was reported. This works fine.

Within that grouping, I have no problems calculating the correct
number of
hours they "should" work as

=IIf([PersonType]="Salaried",45,40)

and in the Employee group, I just SUM that and it gives me the
correct
number of hours they should have worked.

Also in the weekly grouping, I calculate the hours they did work
as:

=Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))

[note that tjob is a job code, and X0012 represents lunch. They
don't
get
credit for this time.

Again, Putting this in the Employee group using a sum gives me
what I
expect.

Now, in order to get the number of hours comp time they are due
that
week,
I
just subtract them, with the additional caveat that if less than
0,
they
are
due 0 hours. In the week group, that looks like:

=IIf(Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40)<0,0,Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40))

And that seems to work fine for each week.

Now the difficulty:

If I then take that expression and put it into the Employee group,
thus:

=SUM(IIf(Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40)<0,0,Sum(IIf([tjob]<>'X0012',([tStopTime]-[tStartTime])*24,0))-IIf([PersonType]="Salaried",45,40)))

I get an error 3095, cannot have aggregate function in expression.

How can I fix this? My SQL is not up to calculating the weekly
available
time in a Query, althought that seems like it might work. Is that
the
answer?

Thanks for the help. I really appreciate it.

///Ian