How do You Filter on Totals from a Subreport?

[Rita]

How can I filter/view data from a subreport or can you do this?
How do I view clients with gross pay >=$25? or another report for clients
with gross pay <=$2?
______________________________________________
Sam Smith Pay Period ending 9/5/08

Subreport Information
Job Piece Gross
Date Code Units Rate Time Pay
8/26/2008 41154 25 $0.00 0.25 $0.29
8/29/2008 41154 200 $0.00 1.50 $2.29
Avg Piece Rate/Hr 225 $0.00 1.75 $2.58
______________________________________________
Tom Jones Pay Period ending 9/5/08

Subreport Information:
Job Piece Gross
Date Code Units Rate Time Pay
8/25/2008 74374 184 $0.00 2.00 $15.34
8/26/2008 74361 50 $0.00 0.50 $2.47
8/26/2008 74374 108 $0.00 1.25 $9.00
8/28/2008 74374 173 $0.00 2.25 $14.42
8/29/2008 74361 117 $0.00 1.25 $5.77
Avg Piece Rate/Hr 632 $0.00 7.25 $47.00
______________________________________________

THANKS for any suggestions!

 

[KARL DEWEY]

Add a calculated field to your query that feeds the report like this --
Sort_Field: IIF([gross pay] >=25, 1,IIF([gross pay] <=2,2,3))

Set criteria <3 and in report use Sorting and Grouping with header.

 

[Rita]

I do get gross pay for each job, and I know how to calculate in a query.

My problem is I have the individual jobs in a separate table. I'm pulling
all these individual jobs over a two week period into my report based upon
the Client ID#. It's pulling all the jobs. I just want to pull in the clients
that have gross pay over a 2 week period over $25.00. I don't know how to
work with calculated fields from subreport.

Sam Smith Pay Period ending 9/5/08
________________________________________________
Subreport Information
Job Piece Gross
Date Code Units Rate Time Pay
8/26/2008 41154 25 $0.00 0.25 $0.29
8/29/2008 41154 200 $0.00 1.50 $2.29
_________________________________________________
Avg Piece Rate/Hr 225 $0.00 1.75 [$2.58]calculated from add gross
pay from sub report
______________________________________________

Add a calculated field to your query that feeds the report like this --
Sort_Field: IIF([gross pay] >=25, 1,IIF([gross pay] <=2,2,3))

Set criteria <3 and in report use Sorting and Grouping with header.

--
KARL DEWEY
Build a little - Test a little

How can I filter/view data from a subreport or can you do this?
How do I view clients with gross pay >=$25? or another report for clients
with gross pay <=$2?
______________________________________________
Sam Smith Pay Period ending 9/5/08

________________________________________________
Subreport Information
Job Piece Gross
Date Code Units Rate Time Pay
8/26/2008 41154 25 $0.00 0.25 $0.29
8/29/2008 41154 200 $0.00 1.50 $2.29
_________________________________________________
Avg Piece Rate/Hr 225 $0.00 1.75 $2.58
______________________________________________

 

[KARL DEWEY]

Do the calculations in a query.
--
KARL DEWEY
Build a little - Test a little

I do get gross pay for each job, and I know how to calculate in a query.

My problem is I have the individual jobs in a separate table. I'm pulling
all these individual jobs over a two week period into my report based upon
the Client ID#. It's pulling all the jobs. I just want to pull in the clients
that have gross pay over a 2 week period over $25.00. I don't know how to
work with calculated fields from subreport.

Sam Smith Pay Period ending 9/5/08
________________________________________________
Subreport Information
Job Piece Gross
Date Code Units Rate Time Pay
8/26/2008 41154 25 $0.00 0.25 $0.29
8/29/2008 41154 200 $0.00 1.50 $2.29
_________________________________________________
Avg Piece Rate/Hr 225 $0.00 1.75 [$2.58]calculated from add gross
pay from sub report
______________________________________________

Add a calculated field to your query that feeds the report like this --
Sort_Field: IIF([gross pay] >=25, 1,IIF([gross pay] <=2,2,3))

Set criteria <3 and in report use Sorting and Grouping with header.

--
KARL DEWEY
Build a little - Test a little

How can I filter/view data from a subreport or can you do this?
How do I view clients with gross pay >=$25? or another report for clients
with gross pay <=$2?
______________________________________________
Sam Smith Pay Period ending 9/5/08

________________________________________________
Subreport Information
Job Piece Gross
Date Code Units Rate Time Pay
8/26/2008 41154 25 $0.00 0.25 $0.29
8/29/2008 41154 200 $0.00 1.50 $2.29
_________________________________________________
Avg Piece Rate/Hr 225 $0.00 1.75 $2.58
______________________________________________

 

[Rita]

Thanks so much for your great suggestions! I’m sorry for not explaining my
question better. I do not know how to work with the field called GrossPayST
located in the CliendID Footer. This field sums the gross pay from each job
in my subreport based if the pay period equals the pay period entered by the
user. I need to check this total if this total is over $25.

Maybe there is way to do this in a query. I am not evaluation each job for
over $25. I am checking the sum of all the gross pays in a pay period. The
user will be prompted to enter the pay period ending to view this
information. This week they will enter 9/5/08. The next time they run the
report, they will enter 9/19.

Here is an example
_____________________________________________________________________
ClientID Header: (This information pulls from a table with just client names
and pay period)
[ClientID]:315 [Name]:Tom Jones [Pay Period ending]: 9/5/08
[TimesheetID]:1511
------------------------------------------------------------------------------------------------
Detail:
My subreport is in the detail section pulling from a separate table with
just this information:
Job Piece Gross Timesheet
Date Code Units Rate Time Pay ID
8/25/2008 74374 184 $0.00 2.00 $15.34
8/26/2008 74361 50 $0.00 0.50 $2.47
8/26/2008 74374 108 $0.00 1.25 $9.00
8/28/2008 74374 173 $0.00 2.25 $14.42
8/29/2008 74361 117 $0.00 1.25 $5.7
------------------------------------------------------------------------------------------------
ClientID Footer:
[Gross PayST]:=Sum([GrossPay]) in this example it is$47.

Do the calculations in a query.
--
KARL DEWEY
Build a little - Test a little

I do get gross pay for each job, and I know how to calculate in a query.

My problem is I have the individual jobs in a separate table. I'm pulling
all these individual jobs over a two week period into my report based upon
the Client ID#. It's pulling all the jobs. I just want to pull in the clients
that have gross pay over a 2 week period over $25.00. I don't know how to
work with calculated fields from subreport.

Sam Smith Pay Period ending 9/5/08
________________________________________________
Subreport Information
Job Piece Gross
Date Code Units Rate Time Pay
8/26/2008 41154 25 $0.00 0.25 $0.29
8/29/2008 41154 200 $0.00 1.50 $2.29
_________________________________________________
Avg Piece Rate/Hr 225 $0.00 1.75 [$2.58]calculated from add gross
pay from sub report
______________________________________________

Add a calculated field to your query that feeds the report like this --
Sort_Field: IIF([gross pay] >=25, 1,IIF([gross pay] <=2,2,3))

Set criteria <3 and in report use Sorting and Grouping with header.

--
KARL DEWEY
Build a little - Test a little


:

How can I filter/view data from a subreport or can you do this?
How do I view clients with gross pay >=$25? or another report for clients
with gross pay <=$2?
______________________________________________
Sam Smith Pay Period ending 9/5/08

________________________________________________
Subreport Information
Job Piece Gross
Date Code Units Rate Time Pay
8/26/2008 41154 25 $0.00 0.25 $0.29
8/29/2008 41154 200 $0.00 1.50 $2.29
_________________________________________________
Avg Piece Rate/Hr 225 $0.00 1.75 $2.58
______________________________________________

Tom Jones Pay Period ending 9/5/08

Subreport Information:
Job Piece Gross
Date Code Units Rate Time Pay
8/25/2008 74374 184 $0.00 2.00 $15.34
8/26/2008 74361 50 $0.00 0.50 $2.47
8/26/2008 74374 108 $0.00 1.25 $9.00
8/28/2008 74374 173 $0.00 2.25 $14.42
8/29/2008 74361 117 $0.00 1.25 $5.77
Avg Piece Rate/Hr 632 $0.00 7.25 $47.00
______________________________________________

THANKS for any suggestions!

 

[KARL DEWEY]

Use a query to pull the data for the subreport and in join a totals query
that has criteria of >25 OR <2 so it only produces the clientID. The
totals query would be inner joined in the subreport query.

--
KARL DEWEY
Build a little - Test a little

Thanks so much for your great suggestions! I’m sorry for not explaining my
question better. I do not know how to work with the field called GrossPayST
located in the CliendID Footer. This field sums the gross pay from each job
in my subreport based if the pay period equals the pay period entered by the
user. I need to check this total if this total is over $25.

Maybe there is way to do this in a query. I am not evaluation each job for
over $25. I am checking the sum of all the gross pays in a pay period. The
user will be prompted to enter the pay period ending to view this
information. This week they will enter 9/5/08. The next time they run the
report, they will enter 9/19.

Here is an example
_____________________________________________________________________
ClientID Header: (This information pulls from a table with just client names
and pay period)
[ClientID]:315 [Name]:Tom Jones [Pay Period ending]: 9/5/08
[TimesheetID]:1511
------------------------------------------------------------------------------------------------
Detail:
My subreport is in the detail section pulling from a separate table with
just this information:
Job Piece Gross Timesheet
Date Code Units Rate Time Pay ID
8/25/2008 74374 184 $0.00 2.00 $15.34
8/26/2008 74361 50 $0.00 0.50 $2.47
8/26/2008 74374 108 $0.00 1.25 $9.00
8/28/2008 74374 173 $0.00 2.25 $14.42
8/29/2008 74361 117 $0.00 1.25 $5.77
------------------------------------------------------------------------------------------------
ClientID Footer:
[Gross PayST]:=Sum([GrossPay]) in this example it is$47.

Do the calculations in a query.
--
KARL DEWEY
Build a little - Test a little

I do get gross pay for each job, and I know how to calculate in a query.

My problem is I have the individual jobs in a separate table. I'm pulling
all these individual jobs over a two week period into my report based upon
the Client ID#. It's pulling all the jobs. I just want to pull in the clients
that have gross pay over a 2 week period over $25.00. I don't know how to
work with calculated fields from subreport.

Sam Smith Pay Period ending 9/5/08
________________________________________________
Subreport Information
Job Piece Gross
Date Code Units Rate Time Pay
8/26/2008 41154 25 $0.00 0.25 $0.29
8/29/2008 41154 200 $0.00 1.50 $2.29
_________________________________________________
Avg Piece Rate/Hr 225 $0.00 1.75 [$2.58]calculated from add gross
pay from sub report
______________________________________________

:

Add a calculated field to your query that feeds the report like this --
Sort_Field: IIF([gross pay] >=25, 1,IIF([gross pay] <=2,2,3))

Set criteria <3 and in report use Sorting and Grouping with header.

--
KARL DEWEY
Build a little - Test a little


:

How can I filter/view data from a subreport or can you do this?
How do I view clients with gross pay >=$25? or another report for clients
with gross pay <=$2?
______________________________________________
Sam Smith Pay Period ending 9/5/08
________________________________________________
Subreport Information
Job Piece Gross
Date Code Units Rate Time Pay
8/26/2008 41154 25 $0.00 0.25 $0.29
8/29/2008 41154 200 $0.00 1.50 $2.29
_________________________________________________
Avg Piece Rate/Hr 225 $0.00 1.75 $2.58
______________________________________________
Tom Jones Pay Period ending 9/5/08

Subreport Information:
Job Piece Gross
Date Code Units Rate Time Pay
8/25/2008 74374 184 $0.00 2.00 $15.34
8/26/2008 74361 50 $0.00 0.50 $2.47
8/26/2008 74374 108 $0.00 1.25 $9.00
8/28/2008 74374 173 $0.00 2.25 $14.42
8/29/2008 74361 117 $0.00 1.25 $5.77
Avg Piece Rate/Hr 632 $0.00 7.25 $47.00
______________________________________________

THANKS for any suggestions!

 

[Rita]

I love the Totals Query! I’ve never used it, and it is exactly what I want! I
read the article, http://support.microsoft.com/kb/290136, and created the
running total in the Northwind Database. They used =DSum(“[Field you want to
total]â€,â€Tableâ€,â€Criteria)

I have the following fields in my query to get my Gross Pay (it is a
calculated field). The query uses other queries and not tables, and I am not
sure how to set the criteria portion up (I want it to total each Client so
would it be [TimesheetMFID]=[TimesheetMFID]?

Is a Totals Query possible with these parameters, or can it just be used on
fields with numbers and tables?

THANKS!
Fields used to generate Gross pay on each job:
Gross Pay: IIf([Gross Pay Formula]>0,[Gross Pay Formula],[q
sf/Code]![Subsidy])
Gross Pay Formula: IIf([Prevailing Wage]>0,[Piece Rate Wage],[Hourly Wage])
Hourly Wage: IIf(IsNull([q sf/Code]![Prevailing Wage]),([q sf/Code]![Avg
Pay])*[q sf/Code]![Time])
Avg Piece Rate: [q sf/Code]![Piece Rate Wage]/[q sf/Code]![Time_wo_Hourly]
Avg Piece Rate Wage: IIf([Time_wo_Hourly]>0,[Piece Rate
Wage]/[Time_wo_Hourly])

Use a query to pull the data for the subreport and in join a totals query
that has criteria of >25 OR <2 so it only produces the clientID. The
totals query would be inner joined in the subreport query.

--
KARL DEWEY
Build a little - Test a little

Thanks so much for your great suggestions! I’m sorry for not explaining my
question better. I do not know how to work with the field called GrossPayST
located in the CliendID Footer. This field sums the gross pay from each job
in my subreport based if the pay period equals the pay period entered by the
user. I need to check this total if this total is over $25.

Maybe there is way to do this in a query. I am not evaluation each job for
over $25. I am checking the sum of all the gross pays in a pay period. The
user will be prompted to enter the pay period ending to view this
information. This week they will enter 9/5/08. The next time they run the
report, they will enter 9/19.

Here is an example
_____________________________________________________________________
ClientID Header: (This information pulls from a table with just client names
and pay period)
[ClientID]:315 [Name]:Tom Jones [Pay Period ending]: 9/5/08
[TimesheetID]:1511
------------------------------------------------------------------------------------------------
Detail:
My subreport is in the detail section pulling from a separate table with
just this information:
Job Piece Gross Timesheet
Date Code Units Rate Time Pay ID
8/25/2008 74374 184 $0.00 2.00 $15.34
8/26/2008 74361 50 $0.00 0.50 $2.47
8/26/2008 74374 108 $0.00 1.25 $9.00
8/28/2008 74374 173 $0.00 2.25 $14.42
8/29/2008 74361 117 $0.00 1.25 $5.77
------------------------------------------------------------------------------------------------
ClientID Footer:
[Gross PayST]:=Sum([GrossPay]) in this example it is$47.

Do the calculations in a query.
--
KARL DEWEY
Build a little - Test a little


:

I do get gross pay for each job, and I know how to calculate in a query.

My problem is I have the individual jobs in a separate table. I'm pulling
all these individual jobs over a two week period into my report based upon
the Client ID#. It's pulling all the jobs. I just want to pull in the clients
that have gross pay over a 2 week period over $25.00. I don't know how to
work with calculated fields from subreport.

Sam Smith Pay Period ending 9/5/08
________________________________________________
Subreport Information
Job Piece Gross
Date Code Units Rate Time Pay
8/26/2008 41154 25 $0.00 0.25 $0.29
8/29/2008 41154 200 $0.00 1.50 $2.29
_________________________________________________
Avg Piece Rate/Hr 225 $0.00 1.75 [$2.58]calculated from add gross
pay from sub report
______________________________________________

:

Add a calculated field to your query that feeds the report like this --
Sort_Field: IIF([gross pay] >=25, 1,IIF([gross pay] <=2,2,3))

Set criteria <3 and in report use Sorting and Grouping with header.

--
KARL DEWEY
Build a little - Test a little


:

How can I filter/view data from a subreport or can you do this?
How do I view clients with gross pay >=$25? or another report for clients
with gross pay <=$2?
______________________________________________
Sam Smith Pay Period ending 9/5/08
________________________________________________
Subreport Information
Job Piece Gross
Date Code Units Rate Time Pay
8/26/2008 41154 25 $0.00 0.25 $0.29
8/29/2008 41154 200 $0.00 1.50 $2.29
_________________________________________________
Avg Piece Rate/Hr 225 $0.00 1.75 $2.58
______________________________________________
Tom Jones Pay Period ending 9/5/08

Subreport Information:
Job Piece Gross
Date Code Units Rate Time Pay
8/25/2008 74374 184 $0.00 2.00 $15.34
8/26/2008 74361 50 $0.00 0.50 $2.47
8/26/2008 74374 108 $0.00 1.25 $9.00
8/28/2008 74374 173 $0.00 2.25 $14.42
8/29/2008 74361 117 $0.00 1.25 $5.77
Avg Piece Rate/Hr 632 $0.00 7.25 $47.00
______________________________________________

THANKS for any suggestions!