Need help changing a database

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

We receive payments from clients on either a monthly or quarterly basis. We
then print a simple report listing each clients name, payment amount, and
day of payment in a column with the total payment at the bottom of the
listing. To generate this report, I simply enter the current month number, 1
for January, 2 for February, etc. into a query.

My database fields consists of CLIENT, PAYMENT, QUARTER1, QUARTER2,
QUARTER3, QUARTER4, and MONTHLY.

The query first tests to see if there is a payment value (PAYMENT field) in
QUARTER1, then it tests QUARTER2, QUARTER3, and finally QUARTER4. If there
are no values in any of these fields the query goes on to check the MONTHLY
field.

This database has worked well in the past but now I need to make a change
which is giving me quite a challenge. We have a new group of clients and I
need to list their payments in a separate column in our report. I've added a
yes/no field to identify the new client and also a new PAYMENT1 field.

The logic goes something like this:
The query checks the new yes/no field to see if this is a new client. If NO,
the query proceeded as before. If YES, the query makes the same tests as
before but uses the PAYMENT1 field instead of the PAYMENT field.

So far I've not been successful in implementing this change and any
suggestions would be greatly appreciated.

Jim
 
Thank you, I will give it a try.

Jim

Delordson Kallon said:
Hi Jim,

Try using the Immediate If Function (IIF). It works something like this:

IIf([New]=Yes,[Payment1],[Payment])

For more info, see http://support.microsoft.com/?kbid=209192

Delordson Kallon
www.instantsoftwaretraining.com

Jim said:
We receive payments from clients on either a monthly or quarterly basis.
We
then print a simple report listing each clients name, payment amount, and
day of payment in a column with the total payment at the bottom of the
listing. To generate this report, I simply enter the current month
number, 1
for January, 2 for February, etc. into a query.

My database fields consists of CLIENT, PAYMENT, QUARTER1, QUARTER2,
QUARTER3, QUARTER4, and MONTHLY.

The query first tests to see if there is a payment value (PAYMENT field)
in
QUARTER1, then it tests QUARTER2, QUARTER3, and finally QUARTER4. If
there
are no values in any of these fields the query goes on to check the
MONTHLY
field.

This database has worked well in the past but now I need to make a change
which is giving me quite a challenge. We have a new group of clients and
I
need to list their payments in a separate column in our report. I've
added a
yes/no field to identify the new client and also a new PAYMENT1 field.

The logic goes something like this:
The query checks the new yes/no field to see if this is a new client. If
NO,
the query proceeded as before. If YES, the query makes the same tests as
before but uses the PAYMENT1 field instead of the PAYMENT field.

So far I've not been successful in implementing this change and any
suggestions would be greatly appreciated.

Jim
Click to expand...
Click to expand...
 
Delordson,

Your suggestion works fine with one exception. In the report, the payment
amounts for the new group of clients appears in both the PAYMENT column as
well as the PAYMENT1 column. I don't quite understand why this is happenng.
Otherwise everything is correct.

Jim



Delordson Kallon said:
Hi Jim,

Try using the Immediate If Function (IIF). It works something like this:

IIf([New]=Yes,[Payment1],[Payment])

For more info, see http://support.microsoft.com/?kbid=209192

Delordson Kallon
www.instantsoftwaretraining.com

Jim said:
We receive payments from clients on either a monthly or quarterly basis.
We
then print a simple report listing each clients name, payment amount, and
day of payment in a column with the total payment at the bottom of the
listing. To generate this report, I simply enter the current month
number, 1
for January, 2 for February, etc. into a query.

My database fields consists of CLIENT, PAYMENT, QUARTER1, QUARTER2,
QUARTER3, QUARTER4, and MONTHLY.

The query first tests to see if there is a payment value (PAYMENT field)
in
QUARTER1, then it tests QUARTER2, QUARTER3, and finally QUARTER4. If
there
are no values in any of these fields the query goes on to check the
MONTHLY
field.

This database has worked well in the past but now I need to make a change
which is giving me quite a challenge. We have a new group of clients and
I
need to list their payments in a separate column in our report. I've
added a
yes/no field to identify the new client and also a new PAYMENT1 field.

The logic goes something like this:
The query checks the new yes/no field to see if this is a new client. If
NO,
the query proceeded as before. If YES, the query makes the same tests as
before but uses the PAYMENT1 field instead of the PAYMENT field.

So far I've not been successful in implementing this change and any
suggestions would be greatly appreciated.

Jim
Click to expand...
Click to expand...
 
I think I may have found my problem, need a little more time.

Jim

Jim said:
Delordson,

Your suggestion works fine with one exception. In the report, the payment
amounts for the new group of clients appears in both the PAYMENT column as
well as the PAYMENT1 column. I don't quite understand why this is
happenng. Otherwise everything is correct.

Jim



Delordson Kallon said:
Hi Jim,

Try using the Immediate If Function (IIF). It works something like this:

IIf([New]=Yes,[Payment1],[Payment])

For more info, see http://support.microsoft.com/?kbid=209192

Delordson Kallon
www.instantsoftwaretraining.com

Jim said:
We receive payments from clients on either a monthly or quarterly basis.
We
then print a simple report listing each clients name, payment amount,
and
day of payment in a column with the total payment at the bottom of the
listing. To generate this report, I simply enter the current month
number, 1
for January, 2 for February, etc. into a query.

My database fields consists of CLIENT, PAYMENT, QUARTER1, QUARTER2,
QUARTER3, QUARTER4, and MONTHLY.

The query first tests to see if there is a payment value (PAYMENT field)
in
QUARTER1, then it tests QUARTER2, QUARTER3, and finally QUARTER4. If
there
are no values in any of these fields the query goes on to check the
MONTHLY
field.

This database has worked well in the past but now I need to make a
change
which is giving me quite a challenge. We have a new group of clients and
I
need to list their payments in a separate column in our report. I've
added a
yes/no field to identify the new client and also a new PAYMENT1 field.

The logic goes something like this:
The query checks the new yes/no field to see if this is a new client. If
NO,
the query proceeded as before. If YES, the query makes the same tests as
before but uses the PAYMENT1 field instead of the PAYMENT field.

So far I've not been successful in implementing this change and any
suggestions would be greatly appreciated.

Jim
Click to expand...
Click to expand...
Click to expand...
 
Delordson,

After I corrected my mistake, your solution did indeed work - many thanks.

Jim

Jim said:
Delordson,

Your suggestion works fine with one exception. In the report, the payment
amounts for the new group of clients appears in both the PAYMENT column as
well as the PAYMENT1 column. I don't quite understand why this is
happenng. Otherwise everything is correct.

Jim



Delordson Kallon said:
Hi Jim,

Try using the Immediate If Function (IIF). It works something like this:

IIf([New]=Yes,[Payment1],[Payment])

For more info, see http://support.microsoft.com/?kbid=209192

Delordson Kallon
www.instantsoftwaretraining.com

Jim said:
We receive payments from clients on either a monthly or quarterly basis.
We
then print a simple report listing each clients name, payment amount,
and
day of payment in a column with the total payment at the bottom of the
listing. To generate this report, I simply enter the current month
number, 1
for January, 2 for February, etc. into a query.

My database fields consists of CLIENT, PAYMENT, QUARTER1, QUARTER2,
QUARTER3, QUARTER4, and MONTHLY.

The query first tests to see if there is a payment value (PAYMENT field)
in
QUARTER1, then it tests QUARTER2, QUARTER3, and finally QUARTER4. If
there
are no values in any of these fields the query goes on to check the
MONTHLY
field.

This database has worked well in the past but now I need to make a
change
which is giving me quite a challenge. We have a new group of clients and
I
need to list their payments in a separate column in our report. I've
added a
yes/no field to identify the new client and also a new PAYMENT1 field.

The logic goes something like this:
The query checks the new yes/no field to see if this is a new client. If
NO,
the query proceeded as before. If YES, the query makes the same tests as
before but uses the PAYMENT1 field instead of the PAYMENT field.

So far I've not been successful in implementing this change and any
suggestions would be greatly appreciated.

Jim
Click to expand...
Click to expand...
Click to expand...
 
Back
Top