Filtering report by unbound sum field in group footer

[Erin Freeman]

Is this possible?
I need to filter an unbound field that is a sum of another field in the
detail part of report.
Field = [Shifts This TC]
sumofshifts=sum([Shifts This TC]) - this resides in the footer

when the report opens i need it to filter by sumofshifts >14.

however, i have tried many different ways and I can not get it to work.
Is the best way to put an field based on an expression in the query? if so
what would the expression look like.

Thanks in advance.

 

[vbasean]

I thought the same thing before I got to your solution in your post

SumOver14: sum([Shifts This TC])

criteria is : >14

 

[vbasean]

oops

SumOver14: Dsum("[Shifts This TC]", "[table this field resides]",
"[childfield] = " & [the parent field])

criteria is : >14

I thought the same thing before I got to your solution in your post

SumOver14: sum([Shifts This TC])

criteria is : >14

Is this possible?
I need to filter an unbound field that is a sum of another field in the
detail part of report.
Field = [Shifts This TC]
sumofshifts=sum([Shifts This TC]) - this resides in the footer

when the report opens i need it to filter by sumofshifts >14.

however, i have tried many different ways and I can not get it to work.
Is the best way to put an field based on an expression in the query? if so
what would the expression look like.

Thanks in advance.

 

[Erin Freeman]

Thanks for your quick response,

do i keep it as a "group by" it tells me i cant have an aggregate function....

any ideas

I thought the same thing before I got to your solution in your post

SumOver14: sum([Shifts This TC])

criteria is : >14

Is this possible?
I need to filter an unbound field that is a sum of another field in the
detail part of report.
Field = [Shifts This TC]
sumofshifts=sum([Shifts This TC]) - this resides in the footer

when the report opens i need it to filter by sumofshifts >14.

however, i have tried many different ways and I can not get it to work.
Is the best way to put an field based on an expression in the query? if so
what would the expression look like.

Thanks in advance.

 

[vbasean]

hmmm,

can you base your totals query on another query that runs the function?

Thanks for your quick response,

do i keep it as a "group by" it tells me i cant have an aggregate function....

any ideas

I thought the same thing before I got to your solution in your post

SumOver14: sum([Shifts This TC])

criteria is : >14

Is this possible?
I need to filter an unbound field that is a sum of another field in the
detail part of report.
Field = [Shifts This TC]
sumofshifts=sum([Shifts This TC]) - this resides in the footer

when the report opens i need it to filter by sumofshifts >14.

however, i have tried many different ways and I can not get it to work.
Is the best way to put an field based on an expression in the query? if so
what would the expression look like.

Thanks in advance.

 

[Erin Freeman]

ok,
sumover14: DSUM("[Shifts This TC]","[Time Card 1]","[
*****now stupid question, what would the childfield and parent field be?

oops

SumOver14: Dsum("[Shifts This TC]", "[table this field resides]",
"[childfield] = " & [the parent field])

criteria is : >14

I thought the same thing before I got to your solution in your post

SumOver14: sum([Shifts This TC])

criteria is : >14

Is this possible?
I need to filter an unbound field that is a sum of another field in the
detail part of report.
Field = [Shifts This TC]
sumofshifts=sum([Shifts This TC]) - this resides in the footer

when the report opens i need it to filter by sumofshifts >14.

however, i have tried many different ways and I can not get it to work.
Is the best way to put an field based on an expression in the query? if so
what would the expression look like.

Thanks in advance.

 

[vbasean]

something tells me this is the totally wrong approach. running a query with
a function it take a bit of thinking for the processor. Running a totals
query based off of a query with a function is unreasonably slow and possibly
capable of crashing Access.

maybe a different approach:

skip the dsum approach.

create a totals query based on the field you want to sum with parent ids
grouped and have your criteria in this query. i.e. >14

add this query and your parent table to another query and create a
relationship between the ids

this is your query.

hmmm,

can you base your totals query on another query that runs the function?

Thanks for your quick response,

do i keep it as a "group by" it tells me i cant have an aggregate function....

any ideas

I thought the same thing before I got to your solution in your post

SumOver14: sum([Shifts This TC])

criteria is : >14


:

Is this possible?
I need to filter an unbound field that is a sum of another field in the
detail part of report.
Field = [Shifts This TC]
sumofshifts=sum([Shifts This TC]) - this resides in the footer

when the report opens i need it to filter by sumofshifts >14.

however, i have tried many different ways and I can not get it to work.
Is the best way to put an field based on an expression in the query? if so
what would the expression look like.

Thanks in advance.

 

[Allen Browne]

No. You cannot filter a report by a result that it does not have until after
it has laid out the records on the report.

You will need to find a way to get that figure before the report runs. It
may be possible to do that with a Totals query. Alternatively, you might be
looking at designing a subquery to use as the report's filter (typically in
the WhereCondition of OpenReport.)

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

We can't see your tables and so can't give you the right expression, but it
might be something along these lines:

(SELECT Count([Shift]) AS HowMany
FROM MyRosterTable
WHERE MyRosterTable.EmployeeID = MyMainTable.EmployeeID
AND MyRosterTable.RosterDate Between #1/1/2008 And #1/31/2008#)