Values comparison

[Rekha V]

Kindly help with below query:
I have a MS access table with 12 numerical columns for 12 Months payments for our customer base.
I need to calculate variance of payments month on month and identify set of customers who have made huge payments.

[Robeen]

Hi Rekha,
Can you explain with a little more detail what you need to do?
Give an example if you can.

[Rekha V]

Hi Robeen, Below is an example:

Customer No	Month 1 Payments	Month 2 Payments	Month 3 Payments	Month 4 Payments	Month 5 Payments	Month 6 Payments	Month 7 Payments	Month 8 Payments	Month 9 Payments	Month 10 Payments	Month 11 Payments	Month 12 Payments
Customer No 1	300	350	250	650	12000	650	325	400	820	750	650	120
Customer No 2	0	0	0	250	590	450	18600	780	345	780	14500	346
Customer No 3	820	750	650	120	650	325	350	250	25600	250	650	22000
so on

In the above examples, I need to month on month compare the following:

a. Month on Month variance in Payments Percentage
b. Payment of Month 3 Vs Average of (Month1+Month2) payments and so on
c. Easily identify huge payments like 12000, 18600,14500,25600,22000 in the above examples

[Rekha V]

Dears,
Kindly help as I am in urgent need of a solution to this problem.

Regards
Rekha

[recyan]

a) Do you want for Customer1
Month2Variance = ((350 - 300) / 300) * 100, Month3Variance = ((250 - 350) / 350) * 100, & so on

b) Do you want for Customer1
( 250 / ((300 + 350 ) / 2)) * 100 & so on

if you want the way it is shown above in a & b, then there are two ways of doing it, the crude way & the polished way.
Currently I do not know of the polished way, but have you tried the crude way of painstakingly building the query or do you want to avoid doing it the crude way.

c) Identifying huge payments can be done in a report. Use conditional formatting on the fields in the Report Design View.

If you confirm the questions asked in a & b, someone should perhaps be able to help you with a polished way of doing it.

Thanks

[Rekha V]

Hi,

The answers to a and b is Yes. However, after calculating variance month on month, I also need to select records where variance is over and above x% (x to be determined by me)

The queries can be built however can be a cumbersome process.

Also for the huge payments, I havent worked on report designs as such and need another solution.

Kindly help.

Regards

[recyan]

Let us wait for someone to help with a polished way of handling a & b.
As for c :
below would highlight in your query.
SELECT
CustomerNo,
Month1Payments,
................,
................,
................,
IIf([Month5Payments]>=1000,"Payment Greater than or Equal to 1000",[Month5Payments]) AS Month5,
................
FROM tblCustomerPayments

I am also no good with reports, but,
In your case, just select the table that you have shown, & use the Create basic Report to create a Report. Then go in to Design View of the Report & select the field which you want to highlight if Amount >= 1000. Right Click & select conditional formatting, then just follow through. You should have a report that will display the way you want it. Have a go.

Thanks

[Rekha V]

Thanks a lot. Will try and let you know for c. For a & b, lets wait for some help.

Regards
Rekha

[Rekha V]

Hi,

For question c, the query you provided is not helping, as you need to know before writing the query that Month5 has a huge payment. If in a huge database, I need to find out which month payment is huge how will i do it.

Regards
Rekha

[recyan]

Try the same for each month, something like below :
SELECT
CustomerNo,
IIf([Month1Payments]>=1000,"Payment Greater than or Equal to 1000",[Month1Payments]) AS Month1,
IIf([Month2Payments]>=1000,"Payment Greater than or Equal to 1000",[Month2Payments]) AS Month2,
IIf([Month3Payments]>=1000,"Payment Greater than or Equal to 1000",[Month3Payments]) AS Month3,
IIf([Month4Payments]>=1000,"Payment Greater than or Equal to 1000",[Month4Payments]) AS Month4,
IIf([Month5Payments]>=1000,"Payment Greater than or Equal to 1000",[Month5Payments]) AS Month5,
................,
................,
IIf([Month12Payments]>=1000,"Payment Greater than or Equal to 1000",[Month12Payments]) AS Month12,
FROM
tblCustomerPayments

What I had shown was just indicative.

Thanks

[Rekha V]

Access experts:

Kindly clarify my query below:


Rekha V

Hi, Below is an example:

Customer No	Month 1 Payments	Month 2 Payments	Month 3 Payments	Month 4 Payments	Month 5 Payments	Month 6 Payments	Month 7 Payments	Month 8 Payments	Month 9 Payments	Month 10 Payments	Month 11 Payments	Month 12 Payments
Customer No 1	300	350	250	650	12000	650	325	400	820	750	650	120
Customer No 2	0	0	0	250	590	450	18600	780	345	780	14500	346
Customer No 3	820	750	650	120	650	325	350	250	25600	250	650	22000
so on

In the above examples, I need to month on month compare the following:

a. Month on Month variance in Payments Percentage
b. Payment of Month 3 Vs Average of (Month1+Month2) payments and so on
c. Easily identify huge payments like 12000, 18600,14500,25600,22000 in the above examples