access database

[SA Writes from FL]

Help

I need help on how to insert an "IIF" formula to each field or cell of a
seperate row of 250 rows in an access database table not the query. I could
not use the query for the insertion because all row have different formula.

If the above is not possible with access could somebody suggest how to write
a query for this problem.

I am developing a database that comprise 250 clients, each client has a
different reward like this, if you spend :

0 to $1,000 gets 0.005 points
$1,001 to $5,000 gets 0.006 points
$5,001 to $10,000 gets 0.007 points
$10,001 to $25,000 gets 0.008 points

 

[Duane Hookom]

I would start by creating a rewards table of MinSpend, MaxSpend, and Points.
You can then create a query that uses your table and the rewards table. Set
the criteria under the SPEND column to
Between MinSpend And MaxSpend

 

[John Vinson]

On Wed, 8 Jun 2005 19:44:02 -0700, "SA Writes from FL" <SA Writes from

Help

I need help on how to insert an "IIF" formula to each field or cell of a
seperate row of 250 rows in an access database table not the query. I could
not use the query for the insertion because all row have different formula.

YOu cannot. Tables contain data, not expressions.

If the above is not possible with access could somebody suggest how to write
a query for this problem.

I am developing a database that comprise 250 clients, each client has a
different reward like this, if you spend :

0 to $1,000 gets 0.005 points
$1,001 to $5,000 gets 0.006 points
$5,001 to $10,000 gets 0.007 points
$10,001 to $25,000 gets 0.008 points

I'd suggest a table-driven approach. If I understand correctly, each
client gets their own point scheme? If so, consider a Table,
ClientPoints, with four fields: ClientID; Low; High; Reward. You can
then create a "Non Equi Join" query:

SELECT <yourtable>.<various fields>, Points.Reward
FROM yourtable INNER JOIN Points
ON yourtable.ClientID = Points.ClientID
AND yourtable.Sales >= Points.Low
AND yourtable.Sales < Points.High;


John W. Vinson[MVP]

 

[PC Datasheet]

The Switch function is what you want, not an IIF expression!!

 

[SA writes From FL]

Thank you for your qiuck response, please help further giving me details of
how do create this Switch function or create it for me and details step by
step on how to apply it to this Dbase.

 

[SA writes From FL]

Please explain further, do you mean I should create 250 reward tables, cause
the reward desribed earlier is only for one clients and is not the same with
the other clients, each seperate clients has a unique different reward points
and max and min spend.

 

[SA writes From FL]

Thanks for your quick response, not only do each client gets their own reward
scheme, they also have different spend max and min, how will this table be
set up, how many "Non Equi Join" query" I am creating, please explain more in
detail, Thanks in Advance,

 

[John Vinson]

Thanks for your quick response, not only do each client gets their own reward
scheme, they also have different spend max and min, how will this table be
set up, how many "Non Equi Join" query" I am creating, please explain more in
detail, Thanks in Advance,

One table, set up as in the next paragraph; one query, based on the
paragraph after that.

The Query will look up the point scheme for the client matching the
current ClientID.

Could you please post the structure of your current table? I cannot
see it, of course, so I don't know what a "spend max and min" might
be, nor do I know the names of your fields, so I must make a guess
that you have fields ClientID and Sales. This guess is likely to be
wrong, only you can help correct it.


John W. Vinson[MVP]

 

[Duane Hookom]

My response is basically the same as John Vinson's. A single reward table
should work. Each record would have one Points field and enough additional
information to determine which record contains the Points value for
awarding.

 

[PC Datasheet]

From Help file ---

Evaluates a list of expressions and returns a Variant value or an expression
associated with the first expression in the list that is True.

Syntax

Switch(expr-1, value-1[, expr-2, value-2 . [, expr-n,value-n]])

The Switch function syntax has these parts:

Part Description
expr Required. Variant expression you want to evaluate.
value Required. Value or expression to be returned if the corresponding
expression is True.
Remarks

The Switch function argument list consists of pairs of expressions and
values. The expressions are evaluated from left to right, and the value
associated with the first expression to evaluate to True is returned. If the
parts aren't properly paired, a run-time error occurs. For example, if
expr-1 is True, Switch returns value-1. If expr-1 is False, but expr-2 is
True, Switch returns value-2, and so on.

Switch returns a Null value if:

· None of the expressions is True.
· The first True expression has a corresponding value that is Null.

Switch evaluates all of the expressions, even though it returns only one of
them. For this reason, you should watch for undesirable side effects. For
example, if the evaluation of any expression results in a division by zero
error, an error occurs.

Example -------------------

The following example uses the Switch function to determine the appropriate
language for a specified city based on the values of the ShipCountry and
ShipCity fields in an Orders table. You can enter the following expression
in a calculated control on a form or report. The expression is shown on
multiple lines for clarity; you can also enter it on a single line.

= Switch([ShipCity] = "Madrid", "Spanish", _
[ShipCity] = "Berlin", "German", _
[ShipCity] = "Torino", "Italian", _
[ShipCountry] = "France", "French", _
True, "English")

If the city is Madrid, the Switch function returns "Spanish"; if it is
Berlin, it returns "German"; and so on. If the city is not one of those
listed, but the country is France, it returns "French". If the city in
question is not in the list, the Switch function returns "English".


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

 

[SA writes From FL]

Client/reward table"Y"

Client Code# Client Name RewardPoint NetSpend Contract
Date Frequency Payment Date
1 A 0 0 - $1,500 01/02/2004 A
0.005 $1,500- $10,000
0.006 $10,000- $25,000
0.007 $25,000 and above
2 B 0.005 0 - $10,000 01/07/2004 Q
0.006 $10,000 - $25,000
0.007 $25,000 and above
3 C 0 0 - $1,500 02/05/2005 M
0.0045 $1,500 - $5,000
0.005 $5,000 - $10,000
0.0055 $10,000 - $15,000
4 D 0.005 0 - $5,000 07/08/2002 A
0.0055 $5,000 - $10,000
0.006 $10,000 - $15,000
0.0065 $15,000 - $20,000
0.007 $20,000 - $25,000
5 E 0.005 $1,500 - $5,000 06/04/2005 Q
0.0055 $5,000 - $10,000
0.006 $10,000 - $15,000
0.0065 $15,000 - $20,000
6 F 0.004 0 - $5,000 02/02/2003 M
0.005 $5,000 - $10,000
0.006 $10,000 - $25,000
0.007 > $25,000
7 G 0.004 0 - $5,000 06/08/2005 M
0.005 $5,000 - $10,000
0.006 $10,000 - $25,000
0.007 > $25,000


Net Spend Table"Z"

Client Code# Net Spend Month
1 $12,000.00 04/01/2005
2 $- 04/01/2005
3 $1,234.00 04/01/2005
4 $456.00 04/01/2005
5 $754.00 04/01/2005
6 $4,567.00 04/01/2005
7 $8,735.00 04/01/2005

I have two tables as structured above:
Table "Y" has the cilents,code, name, contract date, frequency of reward
payment,("M" monthly,"A"Annually or"Q"Quarterly) and the last field is the
next payment date (should a client be qualified for the reward point, based
on his or her contract date, what should the reward due date be).
Note the info in Table"Y" are for the first nine clients, I have 243 more
clients like that with different contract dates and different reward point.

Table"Z" has over 5000 records of (monthly spend amounts for each client),
it has a field for client code, name and net spend for that month.

Other relevant facts:
-some clients will not spend on a giving month.
-some clients will meet the threshold of spend on a given period and will be
giving the reward points.
-some clients contract have monthly reward payment, some have quarterly
reward payment, and some have yearly reward payments.(they have to spend up
to those amount within those agreed periods to qualify for the periods reward
points.)

Based on the above, this is what I want the database to accomplish.
1) Based on the Contract dates and frequency of reward per client, what will
the next payment date be (or review date for each client). What criteria do I
use here for this payment field, and where do I insert the criteria, is it in
the table design of query design.(The reason for having this probable payment
date posted in advance is to know when to make a payment request for a
particular client, which will be #2 below).
2) I want to extablish a report for every client that will state:
-the total amount of reward being paid for that period.
-show what type of cilent reward it is either "M""Q" OR"A" client reward.
-will list out those months and how much net spend for those months.
-will add up the total net spend for the period.
-will show which point was used based on total net spend.
-based on the total net spend will show the reward point used to arrive at
the actual reward.

Based on the above, this is what I have tried that did not work:
-I established an "IIF" statement for the first seven clients, (each client,
with a seperate quey and report), the idea was to code each query 1 to 7 and
have a field in the table listing the number that fits each client and run
there reports based on that, it worked, however, I later realized that I have
250 different rewaed point schedules, it then means running a query for each
schedule and client, this solution was aborted.
-I developed the "IIF" formula for each client, I tried to insert each to a
field (a cell of the same row of each client) in the "Y" table, the to use it
as a string and formular for each client it did not work hence my seeking for
help from the discussion group.


Now with this long narrative, could somebody there "HELP" , and tell, show
, or give any solution to this unque database.








stwhich is the best way to go about reconstucting my tables, write my
queries and develop my report.

 

[SA writes From FL]

Please take a look at this, how can a switch function work with this:

Table "Y"

Client Code# Client Name RewardPoint NetSpend Contract
Date Frequency Payment Date
1 A 0 0 - $1,500 01/02/2004 A
0.005 $1,500- $10,000
0.006 $10,000- $25,000
0.007 $25,000 and above
2 B 0.005 0 - $10,000 01/07/2004 Q
0.006 $10,000 - $25,000
0.007 $25,000 and above
3 C 0 0 - $1,500 02/05/2005 M
0.0045 $1,500 - $5,000
0.005 $5,000 - $10,000
0.0055 $10,000 - $15,000
4 D 0.005 0 - $5,000 07/08/2002 A
0.0055 $5,000 - $10,000
0.006 $10,000 - $15,000
0.0065 $15,000 - $20,000
0.007 $20,000 - $25,000
5 E 0.005 $1,500 - $5,000 06/04/2005 Q
0.0055 $5,000 - $10,000
0.006 $10,000 - $15,000
0.0065 $15,000 - $20,000
6 F 0.004 0 - $5,000 02/02/2003 M
0.005 $5,000 - $10,000
0.006 $10,000 - $25,000
0.007 > $25,000
7 G 0.004 0 - $5,000 06/08/2005 M
0.005 $5,000 - $10,000
0.006 $10,000 - $25,000
0.007 > $25,000

Table "Z"

Client Code# Net Spend Month
1 $12,000.00 04/01/2005
2 $ - 04/01/2005
3 $ 1,234.00 04/01/2005
4 $ 456.00 04/01/2005
5 $ 754.00 04/01/2005
6 $ 4,567.00 04/01/2005
7 $ 8,735.00 04/01/2005
I have two tables as structured above:
Table "Y" has the cilents,code, name, contract date, frequency of reward
payment,("M" monthly,"A"Annually or"Q"Quarterly) and the last field is the
next payment date (should a client be qualified for the reward point, based
on his or her contract date, what should the reward due date be).
Note the info in Table"Y" are for the first nine clients, I have 243 more
clients like that with different contract dates and different reward point.

Table"Z" has over 5000 records of (monthly spend amounts for each client),
it has a field for client code, name and net spend for that month.

Other relevant facts:
-some clients will not spend on a giving month.
-some clients will meet the threshold of spend on a given period and will be
giving the reward points.
-some clients contract have monthly reward payment, some have quarterly
reward payment, and some have yearly reward payments.(they have to spend up
to those amount within those agreed periods to qualify for the periods reward
points.)

Based on the above, this is what I want the database to accomplish.
1) Based on the Contract dates and frequency of reward per client, what will
the next payment date be (or review date for each client). What criteria do I
use here for this payment field, and where do I insert the criteria, is it in
the table design of query design.(The reason for having this probable payment
date posted in advance is to know when to make a payment request for a
particular client, which will be #2 below).
2) I want to extablish a report for every client that will state:
-the total amount of reward being paid for that period.
-show what type of cilent reward it is either "M""Q" OR"A" client reward.
-will list out those months and how much net spend for those months.
-will add up the total net spend for the period.
-will show which point was used based on total net spend.
-based on the total net spend will show the reward point used to arrive at
the actual reward.

Based on the above, this is what I have tried that did not work:
-I established an "IIF" statement for the first seven clients, (each client,
with a seperate quey and report), the idea was to code each query 1 to 7 and
have a field in the table listing the number that fits each client and run
there reports based on that, it worked, however, I later realized that I have
250 different rewaed point schedules, it then means running a query for each
schedule and client, this solution was aborted.
-I developed the "IIF" formula for each client, I tried to insert each to a
field (a cell of the same row of each client) in the "Y" table, the to use it
as a string and formular for each client it did not work hence my seeking for
help from the discussion group.


Now with this long narrative, could somebody there "HELP" , and tell, show
, or give any solution to this unque database.

From Help file ---

Evaluates a list of expressions and returns a Variant value or an expression
associated with the first expression in the list that is True.

Syntax

Switch(expr-1, value-1[, expr-2, value-2 . [, expr-n,value-n]])

The Switch function syntax has these parts:

Part Description
expr Required. Variant expression you want to evaluate.
value Required. Value or expression to be returned if the corresponding
expression is True.
Remarks

The Switch function argument list consists of pairs of expressions and
values. The expressions are evaluated from left to right, and the value
associated with the first expression to evaluate to True is returned. If the
parts aren't properly paired, a run-time error occurs. For example, if
expr-1 is True, Switch returns value-1. If expr-1 is False, but expr-2 is
True, Switch returns value-2, and so on.

Switch returns a Null value if:

· None of the expressions is True.
· The first True expression has a corresponding value that is Null.

Switch evaluates all of the expressions, even though it returns only one of
them. For this reason, you should watch for undesirable side effects. For
example, if the evaluation of any expression results in a division by zero
error, an error occurs.

Example -------------------

The following example uses the Switch function to determine the appropriate
language for a specified city based on the values of the ShipCountry and
ShipCity fields in an Orders table. You can enter the following expression
in a calculated control on a form or report. The expression is shown on
multiple lines for clarity; you can also enter it on a single line.

= Switch([ShipCity] = "Madrid", "Spanish", _
[ShipCity] = "Berlin", "German", _
[ShipCity] = "Torino", "Italian", _
[ShipCountry] = "France", "French", _
True, "English")

If the city is Madrid, the Switch function returns "Spanish"; if it is
Berlin, it returns "German"; and so on. If the city is not one of those
listed, but the country is France, it returns "French". If the city in
question is not in the list, the Switch function returns "English".


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Thank you for your qiuck response, please help further giving me details of
how do create this Switch function or create it for me and details step by
step on how to apply it to this Dbase.

 

[John Vinson]

Client/reward table"Y"

Client Code# Client Name RewardPoint NetSpend Contract
Date Frequency Payment Date
1 A 0 0 - $1,500 01/02/2004 A
0.005 $1,500- $10,000
0.006 $10,000- $25,000
0.007 $25,000 and above

If you're STUCK using this improperly normalized data structure, it's
going to be very, very difficult.

A human being can read a text string like "$25,000 and above" and make
sense of it. A computer will need a lot of help. You are storing
multiple pieces of information in a single field - a violation of the
basic principle that database fields should be "atomic".

Is there ANY chance that you could - as suggested in this thread -
restructure your data so that instead of a big, human-readable
"RewardPoint" field (? word wrap messed up your explanation) field,
could you have Low and High fields?

If not - you'll have to write some fairly complex VBA code to pull in
the text strings like "0 - $1,500" and "$25,000 and above", parse
them, recreate new Low and High values, and construct a SQL string to
find the value you need.

John W. Vinson[MVP]