TOTAL BY FIELD

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jan - 26 Apr 2006 21:22 GMT

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

Reply to this Message

Douglas J. Steele - 26 Apr 2006 21:42 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>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

Reply to this Message

jschweitzer - 27 Apr 2006 16:25 GMT

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.
>
[quoted text clipped - 11 lines]
>>
>> Any help would be appreciated. Thank you in advance - Jan

Reply to this Message

Douglas J Steele - 27 Apr 2006 17:54 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(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
[quoted text clipped - 17 lines]
> >>
> >> Any help would be appreciated. Thank you in advance - Jan

http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200604/1

Reply to this Message

jschweitzer - 27 Apr 2006 21:13 GMT

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

>You can't use more than one query per report.
>
[quoted text clipped - 8 lines]
>
>http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200604/1

Reply to this Message

Larry Daugherty - 30 Apr 2006 16:03 GMT

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

HTH

Signature

-Larry-
--

> 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
[quoted text clipped - 19 lines]
>
> Any help would be appreciated. Thank you in advance - Jan

http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200604/1

Reply to this Message