Group Calculation

[Guest]

I need some assistance in a report.

My report totals the units sold per ship to customer location. I need
another calculation for the total units sold for the bill to customer if
different from the shipping address of the customer location.

Example:
ship to Exide, Chicago, IL = one total unit
ship to Exide, Los Angels, CA = one total unit

Then I need the grand total of the above 2 shipments for the bill to
location for Exide.

Appreciate any suggestions. Thanks.

 

[Al Campagna]

Johnny,
I think an easy method would be to concatenate the City, and State and use that as
"sub" grouping. You don't indicate if Exide is common to all addresses. If it is,
Grpuing by Company, and subgrouping by City State would do the trick.
In the query behind the report create a calculated column like...
ShippedTo : City & ", " & State
Then use ShippedTo as a sub-group of Company.
--
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]

Maybe if I show you how the report comes out, it might make more sense. Also
we have more than one group like exide.
ProductType Description
UnitsSold Actual Sales

SOShipToNum: BATT00
BillToNum: BATT00
3000 SEAMLESS JARS 1,330 $5,224.38
7000 BATTERY JAR COVERS 1,620 $4,272.12
SOShipToCustName: BATTERY BUILDERS INC. 2,950 $9,496.50
BillToCustName: BATTERY BUILDERS INC.
CustGroupNum: BATT00
SOShipToNum: CDMI00 TOTAL 8,268 $44,003.28
BillToNum: CDBB00 TOTAL

The very last total showing above is what I need a formula for, but don't
know much about functions or writing formulas. In the above example, the
biller might have more than one location to ship to. The actual city and
state adress are not part of the report. Sorry for the confusion.

Any other suggestion?

 

[Al Campagna]

Johnny,
I have no idea what this data represents. You don't indicate what you expect for a
CDBB00 total, or even where CDBB00 came from. How can give you a formula to total CDBB00
when no values associated with that appear in your data.
Also, you have a BillToNum of BATT00, then it appears to change to BillToNum CDBB00.
You appear to wenat a total for CDBB00... what happened to a total for BATT00?
Are there Group Headers and Footers involved in this data output? Is there a Detail
section in this report?

I can only suggest one thing... if you want to know what the total of some field is
(say BillToNum) then you need a grouping on that field, with totals in that group's
footer.

--
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]

Al,

Even though my message was very confusing to (and I apologize for that), but
you gave me the answer I needed. Just a group footer did the trick. Thank
you so much.