Math question in a query

  • Thread starter Thread starter reno.04
  • Start date Start date
R

reno.04

Hi

I have a table (tbl_payment) and a client and make payments eg 1 full
payment or many smaller payments.

How can i get all the client's payments add the together and subtract them
from the total fee?

the fields are client ID, fee, payment amount, Payment_due

cheers
 
Reno, design a new query using the tbl_payment. Add the fields you listed
below to the query grid. Click view/totals from the main menu. Under client
ID set it to Group By. Under the other fields set it to Sum. Close and
Save the query as qtotPayments. Now design a new query and use the
qtotPayments as the record source for this query. Add the payment_amount,
which will be listed as SumOfpayment_amount if you didn't change it in the
first query and add the SumOffee to the query. Now type the following in
the top row (Field Row) of an empty grid:

=[SumOffee]-[SumOfpayment_amount]

You will want to watch for nulls and use the Nz function where applicable.
 
Hey thanks Reggie that worked a treat but i see what mean about those who
havent paid could u please explain the nz function's use

cheers
sorry to be a pain
Reggie said:
Reno, design a new query using the tbl_payment. Add the fields you listed
below to the query grid. Click view/totals from the main menu. Under client
ID set it to Group By. Under the other fields set it to Sum. Close and
Save the query as qtotPayments. Now design a new query and use the
qtotPayments as the record source for this query. Add the payment_amount,
which will be listed as SumOfpayment_amount if you didn't change it in the
first query and add the SumOffee to the query. Now type the following in
the top row (Field Row) of an empty grid:

=[SumOffee]-[SumOfpayment_amount]

You will want to watch for nulls and use the Nz function where applicable.
--
Reggie

----------
reno.04 said:
Hi

I have a table (tbl_payment) and a client and make payments eg 1 full
payment or many smaller payments.

How can i get all the client's payments add the together and subtract them
from the total fee?

the fields are client ID, fee, payment amount, Payment_due

cheers
Click to expand...
Click to expand...
 
Reno, When adding numbers, a field that is null is telling access that the
value for that field = Unknown. So therefore:
Null is the same as Unknown. When you try to add Unknown to something
access gets confused and will display unpredictable results. What you need
to do is use the Nz function. Nz stands for Null Zero. If used in a query
you must specify the value to use if the control is null"

=(Nz([SumOffee],0))-(Nz([SumOfpayment_amount],0))

Lookup Nz in the help file.

--
Reggie

----------
reno.04 said:
Hey thanks Reggie that worked a treat but i see what mean about those who
havent paid could u please explain the nz function's use

cheers
sorry to be a pain
Reggie said:
Reno, design a new query using the tbl_payment. Add the fields you listed
below to the query grid. Click view/totals from the main menu. Under client
ID set it to Group By. Under the other fields set it to Sum. Close and
Save the query as qtotPayments. Now design a new query and use the
qtotPayments as the record source for this query. Add the payment_amount,
which will be listed as SumOfpayment_amount if you didn't change it in the
first query and add the SumOffee to the query. Now type the following in
the top row (Field Row) of an empty grid:

=[SumOffee]-[SumOfpayment_amount]

You will want to watch for nulls and use the Nz function where applicable.
--
Reggie

----------
reno.04 said:
Hi

I have a table (tbl_payment) and a client and make payments eg 1 full
payment or many smaller payments.

How can i get all the client's payments add the together and subtract them
from the total fee?

the fields are client ID, fee, payment amount, Payment_due

cheers
Click to expand...
Click to expand...
Click to expand...
 
Back
Top