Difference in amounts

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a report that shows me the total for each category (Billing,
Expenditures, Expenses), however, I need to also have the following show up
on the report:

Total for Billing only
Total for Expenditures+Expenses
Differences between the two totals

I have tried everything I know how in the report footer and in my query, but
cannot seem to achieve anything.
Does anyone know how I can have these differences show on my report?
Thanks for the help
Jeannie
 
I doubt anyone can help without knowing your table/query structure. Do you
mind sharing your structure and some sample records?
 
My query is a simple query, I remove any kind of possible sum in it since
nothing was working. The sums are actually done in the report itself,
I have the following calculations in controls on the report:
=Sum([InvoiceAmount])
Percentage
=Sum([InvoiceAmount]*[Percentage])
=[TotalSubTotal]*0.07
=([TotalSubTotal]+[TotalGST])*0.075
=[TotalSubTotal]+[TotalGST]+[TotalQST]

My report groups the category, the detail portion is not visible and the
group footer contains the sums.

The report itself looks like this:
Invoice % Subtotal GST QST Total
Billing
Jan-2004 1,000.00 100%
Feb-2004 850.00 100%
Total 1,850.00 1,850.00 129.50 148.476 2,127.96

Expenditures
Jan-2004 125.00 100% 125.00 8.75 10.03 143.78
Feb-2004 552.00 100% 552.00 38.64 44.30 634.94
Total 677.00 677.00 47.39 54.33 778.72

Personal expenses
Jan-2004 52.00 10% 5.20 0.36 0.42 5.98
March-2004 19.25 15% 2.89 0.20 0.23 3.32
March-2004 150.00 25% 37.50 2.63 3.01 43.13
Total 221.25 45.59 3.19 3.66 52.44

I then need the differences

Can you help me
Thanks
Jeannie
 
The total for Invoice Only could use an expression in a report footer of:
=Sum(Abs([Category]="Billing") * [InvoiceAmount])
To get the sum of InvoiceAmount for Expenditures and Personal Expenses
=Sum(Abs([Category]="Expenditures" OR [Category]="Personal Expenses" ) *
[InvoiceAmount])
--
Duane Hookom
MS Access MVP
--

Jeannie said:
My query is a simple query, I remove any kind of possible sum in it since
nothing was working. The sums are actually done in the report itself,
I have the following calculations in controls on the report:
=Sum([InvoiceAmount])
Percentage
=Sum([InvoiceAmount]*[Percentage])
=[TotalSubTotal]*0.07
=([TotalSubTotal]+[TotalGST])*0.075
=[TotalSubTotal]+[TotalGST]+[TotalQST]

My report groups the category, the detail portion is not visible and the
group footer contains the sums.

The report itself looks like this:
Invoice % Subtotal GST QST Total
Billing
Jan-2004 1,000.00 100%
Feb-2004 850.00 100%
Total 1,850.00 1,850.00 129.50 148.476 2,127.96

Expenditures
Jan-2004 125.00 100% 125.00 8.75 10.03 143.78
Feb-2004 552.00 100% 552.00 38.64 44.30 634.94
Total 677.00 677.00 47.39 54.33 778.72

Personal expenses
Jan-2004 52.00 10% 5.20 0.36 0.42 5.98
March-2004 19.25 15% 2.89 0.20 0.23 3.32
March-2004 150.00 25% 37.50 2.63 3.01 43.13
Total 221.25 45.59 3.19 3.66 52.44

I then need the differences

Can you help me
Thanks
Jeannie






Duane Hookom said:
I doubt anyone can help without knowing your table/query structure. Do
you
mind sharing your structure and some sample records?
Click to expand...
Click to expand...
 
Duane, you are absolutely amazing, I've been killing myself over this formula
for almost a week now, and again, you have helped me with a report formula.
I can finally finalize my database and move on to an easy database (one that
deals with words not numbers).
Thank you again for your help, it is truly appreciated
Jeannie

Duane Hookom said:
The total for Invoice Only could use an expression in a report footer of:
=Sum(Abs([Category]="Billing") * [InvoiceAmount])
To get the sum of InvoiceAmount for Expenditures and Personal Expenses
=Sum(Abs([Category]="Expenditures" OR [Category]="Personal Expenses" ) *
[InvoiceAmount])
--
Duane Hookom
MS Access MVP
--

Jeannie said:
My query is a simple query, I remove any kind of possible sum in it since
nothing was working. The sums are actually done in the report itself,
I have the following calculations in controls on the report:
=Sum([InvoiceAmount])
Percentage
=Sum([InvoiceAmount]*[Percentage])
=[TotalSubTotal]*0.07
=([TotalSubTotal]+[TotalGST])*0.075
=[TotalSubTotal]+[TotalGST]+[TotalQST]

My report groups the category, the detail portion is not visible and the
group footer contains the sums.

The report itself looks like this:
Invoice % Subtotal GST QST Total
Billing
Jan-2004 1,000.00 100%
Feb-2004 850.00 100%
Total 1,850.00 1,850.00 129.50 148.476 2,127.96

Expenditures
Jan-2004 125.00 100% 125.00 8.75 10.03 143.78
Feb-2004 552.00 100% 552.00 38.64 44.30 634.94
Total 677.00 677.00 47.39 54.33 778.72

Personal expenses
Jan-2004 52.00 10% 5.20 0.36 0.42 5.98
March-2004 19.25 15% 2.89 0.20 0.23 3.32
March-2004 150.00 25% 37.50 2.63 3.01 43.13
Total 221.25 45.59 3.19 3.66 52.44

I then need the differences

Can you help me
Thanks
Jeannie






Duane Hookom said:
I doubt anyone can help without knowing your table/query structure. Do
you
mind sharing your structure and some sample records?

--
Duane Hookom
MS Access MVP


Hi,
I have a report that shows me the total for each category (Billing,
Expenditures, Expenses), however, I need to also have the following
show
up
on the report:

Total for Billing only
Total for Expenditures+Expenses
Differences between the two totals

I have tried everything I know how in the report footer and in my
query,
but
cannot seem to achieve anything.
Does anyone know how I can have these differences show on my report?
Thanks for the help
Jeannie
Click to expand...
Click to expand...
Click to expand...
 
Databases that deal with words are not nearly as exciting as databases with
lots of numbers.

Glad to hear your current issue has been resolved.

--
Duane Hookom
MS Access MVP
--

Jeannie said:
Duane, you are absolutely amazing, I've been killing myself over this
formula
for almost a week now, and again, you have helped me with a report
formula.
I can finally finalize my database and move on to an easy database (one
that
deals with words not numbers).
Thank you again for your help, it is truly appreciated
Jeannie

Duane Hookom said:
The total for Invoice Only could use an expression in a report footer of:
=Sum(Abs([Category]="Billing") * [InvoiceAmount])
To get the sum of InvoiceAmount for Expenditures and Personal Expenses
=Sum(Abs([Category]="Expenditures" OR [Category]="Personal Expenses" ) *
[InvoiceAmount])
--
Duane Hookom
MS Access MVP
--

Jeannie said:
My query is a simple query, I remove any kind of possible sum in it
since
nothing was working. The sums are actually done in the report itself,
I have the following calculations in controls on the report:
=Sum([InvoiceAmount])
Percentage
=Sum([InvoiceAmount]*[Percentage])
=[TotalSubTotal]*0.07
=([TotalSubTotal]+[TotalGST])*0.075
=[TotalSubTotal]+[TotalGST]+[TotalQST]

My report groups the category, the detail portion is not visible and
the
group footer contains the sums.

The report itself looks like this:
Invoice % Subtotal GST QST Total
Billing
Jan-2004 1,000.00 100%
Feb-2004 850.00 100%
Total 1,850.00 1,850.00 129.50 148.476 2,127.96

Expenditures
Jan-2004 125.00 100% 125.00 8.75 10.03 143.78
Feb-2004 552.00 100% 552.00 38.64 44.30 634.94
Total 677.00 677.00 47.39 54.33 778.72

Personal expenses
Jan-2004 52.00 10% 5.20 0.36 0.42 5.98
March-2004 19.25 15% 2.89 0.20 0.23 3.32
March-2004 150.00 25% 37.50 2.63 3.01 43.13
Total 221.25 45.59 3.19 3.66 52.44

I then need the differences

Can you help me
Thanks
Jeannie






:

I doubt anyone can help without knowing your table/query structure. Do
you
mind sharing your structure and some sample records?

--
Duane Hookom
MS Access MVP


Hi,
I have a report that shows me the total for each category (Billing,
Expenditures, Expenses), however, I need to also have the following
show
up
on the report:

Total for Billing only
Total for Expenditures+Expenses
Differences between the two totals

I have tried everything I know how in the report footer and in my
query,
but
cannot seem to achieve anything.
Does anyone know how I can have these differences show on my report?
Thanks for the help
Jeannie
Click to expand...
Click to expand...
Click to expand...
 
Back
Top