Do not aggregate total in a report

[cjborntorun]

Here is a sample of the controls on my report. The data in my underlying
table/query revolves around sku 100. For information purposes only, I want
to show that sku 100 is comprised of 100A, 100B, and 100C which could be
varied promotional packaging, but essentially the same products inside each
box. The problem is, if my sales for sku 100 are $55,000, Access wants to
multiple this by 3 and shows $165,000 instead. Here is a simplistic example
of what my report design looks like:

SKU HEADER
Shows sku 100

DETAIL
Shows related sku variants
100A
100B
100C

SKU FOOTER
2008 = $165,000

How can I accurately report sales of $55,000 for sku 100, and still show
(informationally only) 100A, 100B, or 100C on the final report?

Thanks for your assistance.

 

[KARL DEWEY]

Post the SQL of the query you are using as source for the report.

 

[Klatuu]

Assuming 2008 in your SKU FOOTER is =Sum([sku])
Try
=Sum(Iif([sku] = 100,[sku],0))

 

[cjborntorun]

I used simpler terms to describe this in my original post, but here is actual
SQL w/real terms. Here are some translations for you:

"SKU" from my original post is "10-DIGIT_UPC" in SQL
"SKU VARIANT" from my original post is "MA SKU16" or "GLOBE_CODE" in SQL

SELECT PRODUCT_MASTER.PL3_CATEGORY_ID, PRODUCT_MASTER.[PL4_SUB-CATEGORY_ID],
PRODUCT_MASTER.PL5_SEGMENT_ID, PRODUCT_MASTER.PL5_SEGMENT,
PRODUCT_MASTER.[10-DIGIT_UPC], PRODUCT_MASTER.PRODUCT_DESC,
PRODUCT_GLOBE_CODES.MA_SKU16, [05$$].[Total05$$], [06$$].[Total06$$],
[07$$].[Total07$$], [08$$].[Total08$$]
FROM ((((PRODUCT_MASTER LEFT JOIN [05$$] ON PRODUCT_MASTER.[10-DIGIT_UPC] =
[05$$].[10-DIGIT UPC]) LEFT JOIN [06$$] ON PRODUCT_MASTER.[10-DIGIT_UPC] =
[06$$].[10-DIGIT UPC]) LEFT JOIN [07$$] ON PRODUCT_MASTER.[10-DIGIT_UPC] =
[07$$].[10-DIGIT UPC]) LEFT JOIN [08$$] ON PRODUCT_MASTER.[10-DIGIT_UPC] =
[08$$].[10-DIGIT_UPC]) INNER JOIN PRODUCT_GLOBE_CODES ON
PRODUCT_MASTER.[10-DIGIT_UPC] = PRODUCT_GLOBE_CODES.[10-DIGIT_UPC]
WHERE (((PRODUCT_MASTER.[PL4_SUB-CATEGORY_ID])="USK102") AND
((([Total05$$]+[Total06$$]+[Total07$$]+[Total08$$]))<>0))
ORDER BY PRODUCT_MASTER.PL3_CATEGORY_ID, PRODUCT_MASTER.PL5_SEGMENT_ID,
PRODUCT_MASTER.[10-DIGIT_UPC];

 

[John Spencer]

You might try using the average of the cost values in the detail section or
just grab the last value by binding the control in the footer to the cost control.

Control Source: = Avg([Cost])

Or
Control Source: [Cost]

If the values are all the same within the grouping, either method should
return what you want.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[cjborntorun]

Thanks to John & Karl - You answers helped me discover my formula error on
the report control itself.

You might try using the average of the cost values in the detail section or
just grab the last value by binding the control in the footer to the cost control.

Control Source: = Avg([Cost])

Or
Control Source: [Cost]

If the values are all the same within the grouping, either method should
return what you want.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Here is a sample of the controls on my report. The data in my underlying
table/query revolves around sku 100. For information purposes only, I want
to show that sku 100 is comprised of 100A, 100B, and 100C which could be
varied promotional packaging, but essentially the same products inside each
box. The problem is, if my sales for sku 100 are $55,000, Access wants to
multiple this by 3 and shows $165,000 instead. Here is a simplistic example
of what my report design looks like:

SKU HEADER
Shows sku 100

DETAIL
Shows related sku variants
100A
100B
100C

SKU FOOTER
2008 = $165,000

How can I accurately report sales of $55,000 for sku 100, and still show
(informationally only) 100A, 100B, or 100C on the final report?

Thanks for your assistance.