TOTAL BY FIELD

[Jan via AccessMonster.com]

I created a query based on a table that totals pieces of defective items
using simple expressions ([Total SV Ctns 1]*[Pieces per Ctn 1]). Entries are
made to this db daily to record defective production. Each record has an
entry for "disposition" which is a 7 record table (Pass, Reinspect, Discard,).
I have created a report for the previous weeks entries. I am trying to get
grand totals of pieces by each disposition and am having difficulty in
creating this in the report footer. The table looks something like:

Item Date Disposition Total Parts
123 04/16/06 Pass 500
321 04/17/06 Pass 500
432 04/18/06 Discard 250
234 04/19/06 Reinspect 100
543 04/19/06 Grind 0

I would like the total by disposition to be similar to:

Pass = 1,000
Discard = 250
Reinspect = 100
Grind = 0

Any help would be appreciated. Thank you in advance - Jan

 

[Douglas J. Steele]

Try creating a Totals query.

You do this by creating a new query, selecting the table and dragging the
fields Disposition and Total Parts from that table into the grid.

Then, click on the Sigma icon on the icon bar (or click on Totals under the
View menu). That will add a new row to the grid named Total, with "Group By"
under each field. Change it from "Group By" to "Sum" under Total Parts, and
save the query.

 

[jschweitzer via AccessMonster.com]

Thank you Doug. The query worked great. Can you assist on one other item?
I am using the "qryWeeklyReport" as the Record Source for the report showing
all of the data required and now need to add the query you helped me with
"qryDispoTotals" to the Report Footer and am unsure how to do this. I tried
a few things but none worked. Thanks again. Jan

Try creating a Totals query.

You do this by creating a new query, selecting the table and dragging the
fields Disposition and Total Parts from that table into the grid.

Then, click on the Sigma icon on the icon bar (or click on Totals under the
View menu). That will add a new row to the grid named Total, with "Group By"
under each field. Change it from "Group By" to "Sum" under Total Parts, and
save the query.

I created a query based on a table that totals pieces of defective items
using simple expressions ([Total SV Ctns 1]*[Pieces per Ctn 1]). Entries

[quoted text clipped - 22 lines]

Any help would be appreciated. Thank you in advance - Jan

 

[Douglas J Steele]

You can't use more than one query per report.

Try creating another report based on qryDispoTotals and using that report as
a subreport on your main report.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you Doug. The query worked great. Can you assist on one other item?
I am using the "qryWeeklyReport" as the Record Source for the report showing
all of the data required and now need to add the query you helped me with
"qryDispoTotals" to the Report Footer and am unsure how to do this. I tried
a few things but none worked. Thanks again. Jan

Try creating a Totals query.

You do this by creating a new query, selecting the table and dragging the
fields Disposition and Total Parts from that table into the grid.

Then, click on the Sigma icon on the icon bar (or click on Totals under the
View menu). That will add a new row to the grid named Total, with "Group By"
under each field. Change it from "Group By" to "Sum" under Total Parts, and
save the query.

I created a query based on a table that totals pieces of defective items
using simple expressions ([Total SV Ctns 1]*[Pieces per Ctn 1]).

Entries
[quoted text clipped - 22 lines]

Any help would be appreciated. Thank you in advance - Jan

 

[jschweitzer via AccessMonster.com]

Again I Thank You. It worked like a charm. Jan

You can't use more than one query per report.

Try creating another report based on qryDispoTotals and using that report as
a subreport on your main report.

Thank you Doug. The query worked great. Can you assist on one other item?
I am using the "qryWeeklyReport" as the Record Source for the report showing

[quoted text clipped - 17 lines]

 

[Larry Daugherty]

For your query it sounds like you don't need Item. For dates use
Between but don't show on the report.

HTH