This is easy in excel, seemingly impossible in Access(XP, db=2000 fromat). Namely, need to list subtotals horizontally. I have a table (Charges: Date, shift, name, chargetype, amount). Need to list the charges, by name, with subtotals for each chargetype. Would look like:
John Adams SubtotalFood SubAdvances SubLoans SubUniforms
Charge 1Date type amount
Charge 2 date type Amount
Tom Bostwick SubtotalFood SubAdvances SubLoans etc
Charge1date type amount
Charge2date type amount
Charge3date type amount
Jeff Smith etc ect etc
I'm also limiting the report to a certain start and end date. Am I missing something, or is this really difficult?
Originally posted by bc301
Do I understand this correctly: You want to list all the charges then at bottom of form/report you want subtotals for each charge type.
If so, try using IIf function. Put a running sum field for each charge type in the form/report detail section. Set Control Source to:
Where [txtChargeType] is the field containing charge type
"Type1" is a charge type
[txtAmount] is field containing amoutn
You need to put the IIf in each running sum, changing Type1 to appropriate type.
At form/report footer, refereance appropriate running sub to obtain subtotals.
Thanks BC, appreciate the response! Hadn't thought of IIF in detail section - I worry that the performance penalty might be severe. Actually, I need to put the subs in the header section for each name, followed by list of each charge below. The report is filtered to a date range...
I tried dsum, but the syntax is tortured for multiple criteria.
I'm working on sourcing from a crosstab query, just need to get the subtotals squared away...