total sum of quantity per item in a report

[Guest]

I have a report that has the following columns:

customer: order#: date: shipper#: item#: qty: amount
abc co. 8597 12/5/06 01-13665 210056 3000 $3,000.00
12/14/06 01-15588 210056 2000 $2,000.00
12/25/06 01-88991 210056 1000 $1,000.00

8739 12/5/06 01-13788 209570 500 $1,000.00
12/12/06 01-14341 209570 200 $800.00


given the above information,
1). how can I sum quantities per item and sum the amount per item.
2). after adding them at the bottom per each item, how can I do a grand
total of the amount per customer. PLEASE SEE BELOW WHAT I MEANT:
Customer: order#: date: shipper#: item#: qty: amount:
abc co. 8597 12/5/06 01-13665 210056 3000 $3,000.00
12/14/06 01-15588 210056 2000 $2,000.00
12/25/06 01-88991 210056 1000
$1,000.00

_____ _________
6000 $6,000.00


8739 12/5/06 01-13788 209570 500
$1,000.00
12/12/06 01-14341 209570 200
$800.00

____ ________

700 $1,800.00

Total for Customer ABC CO. 6700 $7,800.00

 

[Al Campagna]

rej,
This is accomplished with Groups. In this case, you would create a Group on OrderNo,
and... using the Sorting & Grouping, create an OrderNo Footer.
In that Footer... 2 calculated text controls with....
= Sum(Qty) and =Sum(Amount)
would yield totals after each OrderNo.

Finally, in the Report Footer istelf...
= Sum(Qty) and = Sum(Amount)
would yield the total Qty and Amount... "over all".

Do you see the beauty of this? If your report data and grouping is all correct, then
the same calculation in each group always yield the correct calculation for that Group.

--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

Dear Al:

Please be advised that I followed your steps. Please look below the
original picture which is similar to the one I sent earlier.
customer: order#: date: shipper#: item#: qty: amount



I made the IV_CUST_ORDER yes on Group Footer and no on Group Header.

I followed your instructions and I was able to get the total of the QTY,
unfortunately, on the AMOUNT it duplicated the amounts per line. Please look
below
customer: order#: date: shipper#: item#: qty: amounttotal qty:
6000
total qty:
700


Please tell me how to correct this issue so I can sub total the amount. The
total of the quantity is right though. Please help!!!

 

[Guest]

Dear Al:
I wrote you wrong. What it did was after following your instructions was it
also totalled the amount instead of leaving the details per line it totalled
it and put the total in line per line.

customer: order#: date: shipper#: item#: qty: amount

abc co. 8597 12/5/06 01-13665 210056 3000 $6,000.00
12/14/06 01-15588 210056 2000 $6,000.00
12/25/06 01-88991 210056 1000 $6,000.00
total qty: 6000

I hope I am not confusing you. How can I leave the detail amount as is and
just do the sub total of the amount in line with the total qty. Please
help!!!

 

[Guest]

Dear Al:

I saw the problem and it fixed the issue. The problem was on the label and
what you gave me were right and solved my issue. I want to thank you very
much for your help and it just did not solve my issue if also taught me
rather enhances my access knowledge. Thank you once again for imparting your
knowledge in solving my issue and imparting it to me.

 

[Al Campagna]

I'm afraid I'm having trouble understanding what you mean. If =Sum(Qty) in one
calculated field gives you the correct [TotalQtyPerOrderNo],
then... =Sum(Amount) in another calculated field should yield the [TotalAmountPerOrderNo].
You never have indicated what you want to see in the Amount total.

Also, your data doesn't seem to make sense... if abc co. orders 3000 of an ItemNo for
$6000, how can they later order 2000 for $6000, or... 1000 for $6000??
Why do you show $6000 for each line? Looks like you may have summed the Amount in your
query, and are displaying that as a "per line total"... which is not true.

Let's say Item 210056 sells @ $1.00, then I would expect to see...

customer: order#: date: shipper#: item#: qty: amount

-----------------------------------------------------------------------------
6000
$6,000.00

What I need to know... simply stated... is what your "raw" data looks like, and what
you want to see on the rpeort.
Are you looking to show the Totals for Qty and Amount for all order transactions by
ITEMNO, or by ORDERNo, or what...?
Show me some sample data *AND* what YOU say the Totals should be.
I'm quessing... at this point that your report data is incorrect, and now you're having
troble getting the right totals to calculate.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

Sir Al, so sorry for confusing you. After I wrote back to you I went back to
access report again and once again analyzed what is causing the problem and I
found what was causing the problem. I then posted back to you that what you
told me to do worked and it also helped my understand more of access. When
everything was said and done, I rated you to 10 (rate scale of 1-10) but I
was having a hard time rating, would not give me the option of rating you
though I followed their instructions of how to rate. So I posted another one
of how to rate the answers given from questions posted.

Thank you very much for you understanding and all you help,
rej

I'm afraid I'm having trouble understanding what you mean. If =Sum(Qty) in one
calculated field gives you the correct [TotalQtyPerOrderNo],
then... =Sum(Amount) in another calculated field should yield the [TotalAmountPerOrderNo].
You never have indicated what you want to see in the Amount total.

Also, your data doesn't seem to make sense... if abc co. orders 3000 of an ItemNo for
$6000, how can they later order 2000 for $6000, or... 1000 for $6000??
Why do you show $6000 for each line? Looks like you may have summed the Amount in your
query, and are displaying that as a "per line total"... which is not true.

Let's say Item 210056 sells @ $1.00, then I would expect to see...

customer: order#: date: shipper#: item#: qty: amount

-----------------------------------------------------------------------------
6000
$6,000.00

What I need to know... simply stated... is what your "raw" data looks like, and what
you want to see on the rpeort.
Are you looking to show the Totals for Qty and Amount for all order transactions by
ITEMNO, or by ORDERNo, or what...?
Show me some sample data *AND* what YOU say the Totals should be.
I'm quessing... at this point that your report data is incorrect, and now you're having
troble getting the right totals to calculate.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Dear Al:
I wrote you wrong. What it did was after following your instructions was it
also totalled the amount instead of leaving the details per line it totalled
it and put the total in line per line.

customer: order#: date: shipper#: item#: qty: amount

I hope I am not confusing you. How can I leave the detail amount as is and
just do the sub total of the amount in line with the total qty. Please
help!!!