SQL and sum() on a report

[Arjay418]

I have a table with information like this:



Category CreditAmt DebitAmt

Materials 30 0
Cleaning 50 0
Labor 20 0
Income 0 200
Materials 40 0
Labor 60 0



Using a normal report wizard, I can create a report that gives totals for each category. I use a =Sum([CreditAmt]-[DebitAmt]) expression in the Category footer of the report and this works fine. I get:

Materials Total 70
Cleaning Total 50
Income Total -200
Labor Total 80

My question is how can I select one of those categories (Labor) and instead of showing a total of 80, it shows a total of 96 (just 80 * 1.2)? That is the only category that needs to be multiplied by 1.2. The categories are not the same for every report. Sometimes it has some, sometimes it has others.

I'd like to tell Access, "Give me the totals for each of the categories and multiply the Labor category by 1.2 and use that as the Labor Total."

Then as an added bonus, "Give me the grand total of all of the subtotals I just asked you to find."

I imagine this will take some SQL (which I'm not too familiar with) so any ideas will be helpful.

Thanks,

RJ

 

[BobStubbs]

Why not 'cheat' with a lookup table? You could create a table, tblMultipliers, with two fields, e.g.

Category - text 10 chars
Multipier - numeric, single

Populate the table like this:

Materials 1
Cleaning 1
Labor 1.2
Income 1

Now add an unbound field to the Category footer. Call this txtMultiplier, with a data source of:

=DLookUp("[Multiplier]","tblMultipliers","[Category]=Reports!rptYourReportName![Category]")

Finally, use this value in your original formula:

=Sum([CreditAmt]-[DebitAmt]) * [txtMultiplier.value]

This avoids complex SQL, and will also be useful if you need to change the multipliers on any other categories in the future.


Bob Stubbs

 

[dhookom]

I would avoid DLookup() by placing tblMultipliers in the report's record source. Then join the Category fields so you can add Multiplier to the report's field list.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Arjay418]

All I can say, guys, is that you're amazing. It worked perfectly. I created the tblMultipliers as suggested by BobStubbs and added it to the query as suggested by dhookom. I should mention that in the tblMultipliers I had to specify 1 for the Scale so that the value could accept a decimal point.

Thanks for your help.

--rj