Total different groups

  • Thread starter Thread starter Amelia
  • Start date Start date
A

Amelia

I have a report that generates weekly deposit amounts. I have it grouped by
[account number] and then by [type of payment]. I want to total the
individual types of payments only if more then one payment type exists. I am
sure this is possible I just don't know how to do it.

I already have it totaling each group and then a final total for that
account and then a grand total at the end of the report. But if only cash was
deposited in a particular account then I do not need it to total that group
since I have the group total.

If anyone has any thoughts let me know!

Thanks for the help. You guys are the best!
 
Here is one thought I had, but of course it didn't work. I want to use
something like:

IFF([type of payment]=cash and check, SUM([amount collected]), null)

Hope this helps you understand what I am looking for.
 
Ideally you should have a numeric field in you payment type lookup table that
identifies which types to total. For instance a field name [IsCash] with
values of 1 for cash and check and 0 for all other types. You can then add
the payment type lookup table to your reports record source and total the
cash and checks with a text box like:
=Sum([Amount Collected] * [IsCash])
 
I have changed the structure of "type of payment" for other purposes. Now I
am trying to total up my weekly deposits and break them into cash, check,
credit card and other groups.

How would I do this?

I have 4 check boxes (one for each option [cash], [check], [credit card],
[other]) I have the deposit in broke into [account code] groups and then each
account code has a seperate account total, and a grand total of the deposit.

I want the individual account totals to total by [cash], [check], [credit
card], [other]. So for example if I had 5 entries this month that were paid
by cash, 10 entries this month paid by check and 1 paid by credit card my
report would produce this:

Account code Receipt Number Date Collected Amount Collected
12-12-1234
123456 7/22/09 20.00
123457 7/23/09 15.00
123458 7/24/09 100.00

Account Total:135.00



Cash: 20.00

Check:15.00

Credit Card:100.00
I want to keep my checkboxes that are part of the query hidden and just show
the amount from each of them. Can someone please help and let me know if this
is possible or if it is not possible when using checkboxes?

I thought maybe using an IF statement would work:
IF[Cash]=true, sum([amount collected]), null
IF[Check]=true, sum(amount collected]), null
Etc...

Duane Hookom said:
Ideally you should have a numeric field in you payment type lookup table that
identifies which types to total. For instance a field name [IsCash] with
values of 1 for cash and check and 0 for all other types. You can then add
the payment type lookup table to your reports record source and total the
cash and checks with a text box like:
=Sum([Amount Collected] * [IsCash])
--
Duane Hookom
Microsoft Access MVP


Amelia said:
I have a report that generates weekly deposit amounts. I have it grouped by
[account number] and then by [type of payment]. I want to total the
individual types of payments only if more then one payment type exists. I am
sure this is possible I just don't know how to do it.

I already have it totaling each group and then a final total for that
account and then a grand total at the end of the report. But if only cash was
deposited in a particular account then I do not need it to total that group
since I have the group total.

If anyone has any thoughts let me know!

Thanks for the help. You guys are the best!
Click to expand...
Click to expand...
 
"I have 4 check boxes (one for each option [cash], [check], [credit card],
[other]) " IMO, this isn't a very good structure. You should have a single
PaymentType field to store one of the four values. Then you would create a
totals query summing the amount by PaymentType.

If you want to display both details and sums, you can use the totals query
as the Record Source of a subreport.

--
Duane Hookom
Microsoft Access MVP


Amelia said:
I have changed the structure of "type of payment" for other purposes. Now I
am trying to total up my weekly deposits and break them into cash, check,
credit card and other groups.

How would I do this?

I have 4 check boxes (one for each option [cash], [check], [credit card],
[other]) I have the deposit in broke into [account code] groups and then each
account code has a seperate account total, and a grand total of the deposit.

I want the individual account totals to total by [cash], [check], [credit
card], [other]. So for example if I had 5 entries this month that were paid
by cash, 10 entries this month paid by check and 1 paid by credit card my
report would produce this:

Account code Receipt Number Date Collected Amount Collected
12-12-1234
123456 7/22/09 20.00
123457 7/23/09 15.00
123458 7/24/09 100.00

Account Total:135.00



Cash: 20.00

Check:15.00

Credit Card:100.00
I want to keep my checkboxes that are part of the query hidden and just show
the amount from each of them. Can someone please help and let me know if this
is possible or if it is not possible when using checkboxes?

I thought maybe using an IF statement would work:
IF[Cash]=true, sum([amount collected]), null
IF[Check]=true, sum(amount collected]), null
Etc...

Duane Hookom said:
Ideally you should have a numeric field in you payment type lookup table that
identifies which types to total. For instance a field name [IsCash] with
values of 1 for cash and check and 0 for all other types. You can then add
the payment type lookup table to your reports record source and total the
cash and checks with a text box like:
=Sum([Amount Collected] * [IsCash])
--
Duane Hookom
Microsoft Access MVP


Amelia said:
I have a report that generates weekly deposit amounts. I have it grouped by
[account number] and then by [type of payment]. I want to total the
individual types of payments only if more then one payment type exists. I am
sure this is possible I just don't know how to do it.

I already have it totaling each group and then a final total for that
account and then a grand total at the end of the report. But if only cash was
deposited in a particular account then I do not need it to total that group
since I have the group total.

If anyone has any thoughts let me know!

Thanks for the help. You guys are the best!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top