Updating a value using a matrix

[Nancy Lytle]

I have to create some values based on a formula that is fairly straight
forward, but I seem to be having a bit of a mental block on pieces of it.
This is my first time trying anything like this and any help or pointers
will be much appreciated.
Overall I am trying to create a function either behind a form or in a module
where the user can pick the item then input the values of x, y and come up
with z .

TIA,
Nancy

The formula is like this:
item: vehicle
Type 1 includes offRoadvehicles (pickup, SUV, by brand and make)
Type 2 includes all others (by brand and make)
y is one rating for an item, say mechanical condition
x is a rating for an item on a different condition, like body condition
z is a percentage that some one came up with based on y and x as to how much
above or below a set value they are willing to go to purchase the item (for
example might be willing to pay 10% over asking for a OffRoad in excellent
mechanical condition but with a less than perfect body and 5% less for a non
OffRoad that in excellent mechanical condition but needs body work.

If Left(IbrandandmakeID, 3) in (a list of about 25 pieces of 3 character
text) then

If y = 5 then
if x = 5 then
z = 120
Elseif x = 4 then
z= 50
'x goes down to 3, then 2, then 1 with the z also changing by no set
formula - just taking numbers off a 'spreadsheet
End if

ElseIf y= 4 then
if x = 5 then
z = 100
Elseif x = 4 then
z= 40 'x goes down to 3, then 2, then 1 with the z also changing
by no set formula - just taking numbers off a 'spreadsheet
End if

' y goes down to 3 then 2 then one, again with a lookup to x's value (1-5)
and based on those 2 numbers z is gotten from the spreadsheet.

Then there is the overall Else statement for IDNumbers not in the IN clause.
The the asking price for the item will be multiplied by the z percentage to
get the price we are willing to pay up to.

I am have one problem and one question:
Problem:
I cannot seem to get the
If Left(IDNumber, 3) in (123,156,195) then
part to work.

My question is:
Is there an easier way to do this that I am not seeing?

 

[Marshall Barton]

I have to create some values based on a formula that is fairly straight
forward, but I seem to be having a bit of a mental block on pieces of it.
This is my first time trying anything like this and any help or pointers
will be much appreciated.
Overall I am trying to create a function either behind a form or in a module
where the user can pick the item then input the values of x, y and come up
with z .

The formula is like this:
item: vehicle
Type 1 includes offRoadvehicles (pickup, SUV, by brand and make)
Type 2 includes all others (by brand and make)
y is one rating for an item, say mechanical condition
x is a rating for an item on a different condition, like body condition
z is a percentage that some one came up with based on y and x as to how much
above or below a set value they are willing to go to purchase the item (for
example might be willing to pay 10% over asking for a OffRoad in excellent
mechanical condition but with a less than perfect body and 5% less for a non
OffRoad that in excellent mechanical condition but needs body work.

If Left(IbrandandmakeID, 3) in (a list of about 25 pieces of 3 character
text) then

If y = 5 then
if x = 5 then
z = 120
Elseif x = 4 then
z= 50
'x goes down to 3, then 2, then 1 with the z also changing by no set
formula - just taking numbers off a 'spreadsheet
End if

ElseIf y= 4 then
if x = 5 then
z = 100
Elseif x = 4 then
z= 40 'x goes down to 3, then 2, then 1 with the z also changing
by no set formula - just taking numbers off a 'spreadsheet
End if

' y goes down to 3 then 2 then one, again with a lookup to x's value (1-5)
and based on those 2 numbers z is gotten from the spreadsheet.

Then there is the overall Else statement for IDNumbers not in the IN clause.
The the asking price for the item will be multiplied by the z percentage to
get the price we are willing to pay up to.

I am have one problem and one question:
Problem:
I cannot seem to get the
If Left(IDNumber, 3) in (123,156,195) then
part to work.

My question is:
Is there an easier way to do this that I am not seeing?

The problem with your If statement is that IN is not a VBA
operator. (It would work if you used it in a query's Where
clause or a text box expression.)

In VBA, you have to use a bunch of ORs to accomplish the
same task:

If Left(IDNumber, 3) = 123 OR Left(IDNumber, 3) = 156 OR
Left(IDNumber, 3) = 195 Then

BUT, you should seriously consider using a table for this
kind of thing instead of hard coding those value in a VBA
procedure. If you did this, then there would be several
ways of retreiving the value of z from an appropriately
designed table.

 

[Nancy Lytle]

I must really be having a bad brain day!
So I would create a table and place in it those values that I want to check
against (the special values) and then in the code, have it match the input
against each row in the table using a query to see if there is a match, and
if there is, flag it one way and take the code through the "special process"
and if not, no flag and it goes through regular processing.
So easy, and yet I didn't think of it. I guess I was looking for something
harder.
And then just create a matrix table or two? This is the somewhat harder
part, bcause there is no consistency in how z is derived, there is no
"mechanical counts for 60% of the decision and Body for 40%" the z part of
the formula is just someone best guesstimate based on 20 years of knowledge.
Maybe that's the sticking point.
Any additional ideas would be welcomed.

Thanks again,
Nancy

 

[Marshall Barton]

I'm not 100% sure I understand the details of your operation
here, but I 'm pretty sure I would go a couple of steps
further. I'm thinking of using at least two tables like
this:

Table name: BrandMakeCategory
Fields: Code Text(3) <Prinmary Key>
Cat Integer <Brand and Make Category>
Example Records:
XYZ 1
XXX 1
ABC 2
PQY 3
PQZ 3
etc.

Table name: ZScore
Fields: VType Integer <Vehicle Type>
Y Integer
X Integer
BMC Integer <Brand and Make Category>
Z Integer
With a unique index (Primary Key) composed of VType, X and Y
Example records:
1 5 5 1 120
1 5 4 1 50
1 4 5 1 100
1 4 4 1 40
etc.

With all that in place, you can retrieve a Z value by using
a query like:

SELECT Z
FROM ZScore As ZS INNER JOIN BrandMakeCategory AS BMCat
ON ZS.BMC = BMCat.Cat
WHERE BMCat.Code = Left(Forms!yourform.IbrandandmakeID, 3)
And ZS.Y=Forms!yourform.txtY And ZS.X=Forms!yourform.txtX

The next step after that would be to create a form/subform
for the express purpose of allowing some person to easily
enter/edit the values in these tables.

 

[Nancy Lytle]

Thanks, I think that last bit has really gotten me over the hump and headed
in the right direction. I am trying to set up something a little less
cumbersome than the current process which involves multiple batch queries
and updates.
Again, many thanks,
Nancy

I'm not 100% sure I understand the details of your operation
here, but I 'm pretty sure I would go a couple of steps
further. I'm thinking of using at least two tables like
this:

Table name: BrandMakeCategory
Fields: Code Text(3) <Prinmary Key>
Cat Integer <Brand and Make Category>
Example Records:
XYZ 1
XXX 1
ABC 2
PQY 3
PQZ 3
etc.

Table name: ZScore
Fields: VType Integer <Vehicle Type>
Y Integer
X Integer
BMC Integer <Brand and Make Category>
Z Integer
With a unique index (Primary Key) composed of VType, X and Y
Example records:
1 5 5 1 120
1 5 4 1 50
1 4 5 1 100
1 4 4 1 40
etc.

With all that in place, you can retrieve a Z value by using
a query like:

SELECT Z
FROM ZScore As ZS INNER JOIN BrandMakeCategory AS BMCat
ON ZS.BMC = BMCat.Cat
WHERE BMCat.Code = Left(Forms!yourform.IbrandandmakeID, 3)
And ZS.Y=Forms!yourform.txtY And ZS.X=Forms!yourform.txtX

The next step after that would be to create a form/subform
for the express purpose of allowing some person to easily
enter/edit the values in these tables.
--
Marsh
MVP [MS Access]

I must really be having a bad brain day!
So I would create a table and place in it those values that I want to
check
against (the special values) and then in the code, have it match the input
against each row in the table using a query to see if there is a match,
and
if there is, flag it one way and take the code through the "special
process"
and if not, no flag and it goes through regular processing.
So easy, and yet I didn't think of it. I guess I was looking for
something
harder.
And then just create a matrix table or two? This is the somewhat harder
part, bcause there is no consistency in how z is derived, there is no
"mechanical counts for 60% of the decision and Body for 40%" the z part
of
the formula is just someone best guesstimate based on 20 years of
knowledge.
Maybe that's the sticking point.

 

[John Spencer (MVP)]

Marshall,

I agree with your proposed solution to this particular problem. HOWEVER, I
think there is a solution to the IN problem that is a bit less cumbersome to
code then using all those OR statements. Especially handy if you need to modify
by adding or removing values.


Quote
In VBA, you have to use a bunch of ORs to accomplish the
same task:

If Left(IDNumber, 3) = 123 OR Left(IDNumber, 3) = 156 OR
Left(IDNumber, 3) = 195 Then
End Quote

SELECT CASE Left(IDNumber,3)
Case "123", "156", "195"
'Since Left returns a string value make sure you
'check for string values

'Do whatever you need to do

END SELECT


That said, I still think a table-driven solution (as you proposed) is a better solution.

 

[Marshall Barton]

Marshall,

I agree with your proposed solution to this particular problem. HOWEVER, I
think there is a solution to the IN problem that is a bit less cumbersome to
code then using all those OR statements. Especially handy if you need to modify
by adding or removing values.


Quote
In VBA, you have to use a bunch of ORs to accomplish the
same task:

If Left(IDNumber, 3) = 123 OR Left(IDNumber, 3) = 156 OR
Left(IDNumber, 3) = 195 Then
End Quote

SELECT CASE Left(IDNumber,3)
Case "123", "156", "195"
'Since Left returns a string value make sure you
'check for string values

'Do whatever you need to do

END SELECT


That said, I still think a table-driven solution (as you proposed) is a better solution.

RIght, much better than an If with a long string of ORs.

Another way is to get the Expression Service to evaluate the
IN operator:

If Eval(Left(IDNumber, 3) & "IN (123, 156, 195)") Then

But, it's still has hard coded data values.

 

[Gerardo Ugarte]

Hi, how are you? .

The reason for which I'm writing you is quite simple... I just want you to
take part in a big movement that is happening on Internet, and maybe you
still don´t know about that... Well, this is about...



THE EASIEST TECHNIQUE TO EARN MONEY!!!



Believe me when I say it's real and not just another trick

You can earn U$S 15.000 in one month.... (if you don´t believe, at least
take yourself a moment to read what's this about)

The only way to know this is not a trick is just using logic and
intelligence and so you'll realize that money multiplies. I ask you to think
and analyze what is described in next lines... I swear it's true



EARN MANY U$S FROM HOME!!

I'm sure your time has got a worth, but I assure you that if you read this
letter, the time you invert on it will be more than well rewarded.



Before anything, it's important you know that in the world there's a lot of
money, but it's distributed in a wrong way; well, this is the solution of
the problem. The matter is not that little give much, the matter is that
much give little.





Some days ago, I read a similar article that said it's possible to earn
thousands of dollars with just a SIX DOLLARS INVERSION (U$S 6).



Inmediately I thought "Oh, no! ¿Another trick?" But, as many of us, my
curiosity was stronger and I went on reading. It said: if you send one
dollar to the six names and addresses mentioned in this article, and write
your name, address and zip code at the end of the list replacing number 6,
and send this article at least to 200 newsgroups (there are thousands of
these in the web) you'll receive, from all the world up to U$S 15.000 or
even more!!

In other words, the only thing you might lose is six seals and six dollars.



Let me explain to you how this works and the most important: WHY THIS WORKS.
Be sure to print a copy of this article NOW, to have all the information
when you need it. The process is very simple and has three easy steps:




STEP nº 1



Get 6 paper sheets and write on all of them "PLEASE, INCLUDE ME IN YOUR
CORRESPONDENCE LIST", with your name, address and e-mail. Now, get 6 ONE
DOLLAR bills and introduce each dollar with the sheet, trying to cover the
bill (try to use a dark peace of paper to avoid robberies in the
correspondence). Now, you must have six sealed letters with the paper sheet
with the phrase mentioned, your name, your address and one dollar bill. What
you are doing is creating a service, making this completley LEGAL!!!


As a nice detail, write also the position of every name when you sent the
dollar (For example: "you were in slot 3") to make it more complete!! This
is the matter, to make money and and have fun at the same time.

Send the six letters to the next addresses:


1. Marco Arosemena M.
Apartado 87-2843, Zona 7
Panamá, REP. DE PANAMÁ

2. Daniel N. Mesta
405 North 14th Street
Phoenix, Arizona, USA 85006

3. Jesus Treviño Hernandez
C/ Juan Suarez de Peralta #2741
Col. Jardinez de la Paz
Tlaquepaque. Jalisco
MEXICO

4. Victor A. Lencina
De la Peña 1525 ( Wilde )
C.P: 1875 Buenos Aires
ARGENTINA

5. Leopoldo C. Chew
Topiltzin # 312 Col. Rodriguez
CP. 89170
Tampico, Tamaulipas

MEXICO



6. Gerardo Ugarte Valencia

Calle Hermanos Andrade #180 Urb. El Bosque
CP Lima 25

Rímac, Lima

PERU




STEP nº 2
Now delete the first name in the list and move the other names one number up
(nº 6 becomes 5, nº 5 becomes 4, etc), and INCLUDE YOUR DATA IN NUMBER 6 OF
THE LIST



STEP nº 3
Change what you consider necessary of this article, and post it on at least
200 newsgroups (there are more than 24 000 groups) and send it directly to
200 e-mails (there are millions). You just need 200, but if you send more,
you'll get more money.



If you know BBS, much better... when you publish this article, try to write
an interesting tittle... like for example.. "DO YOU NEED FAST MONEY? READ
THIS ARTICLE". "DO YOU NEED TO PAY YOUR DEBTS?", ETC.


LET'S COMPROMISE TO BE HONEST, GIVING A HUNDRED TWENTY PERCENT OF US TO MAKE
THIS WORKS. The system will work only if we do what is correct to make money
move. That's the idea!! You`ll get surprised with the results, believe me!!!



Now, WHY IS THIS TRUE??


From 200 sents (among newsgroups and e-mails), let's say we just get 5
answers (very low example). So I would make five dollars with my name in
position number 6 of that letter. Now, each one fo the five persons that
sent me U$S 1 send also a minimum of 200 newsgroups and e-mails, each one
with my name in number 5, and they get just five answers... that makes me
receive another 25 dollars... Now, these 25 persons send a minimun of 200
newsgroups and e-mails with my name in number 4, and they also get just 5
answers... I would be making another 125 dollars. AND NOW, these 125 people
send their 200 newsgroups and e-mails with my name in number 3 and the get 5
answers.. I'd make 625 more dollars!!!



Ok. Now comes the funniest way... each one fo these 625 persons will send
the article to another 200 newsgroups and e-mails with my name in number 2
and each one of them gets only 5 answers... So now I would be making U$S 3
125!!!. These persons repeat the process with my name in number 1 and I
would get U$S 15 625!!.



FROM AN INVERSION OF JUST SIX DOLLARS!!!!



GREAT!! And as I said befote, five answers is a very low example. So imagine
all the money you can get in a easy, funny and fast way!!!!!



HOW TO MANGE THE NEWSGROUPS



Nº.1> You don't need to redact the whole letter to make yours. Just copy it
and save it in you PC as a text file.




Nº.2> Now, you may change all the things you consider necessary.



IF YOU USE NETSCAPE


Nº.3> Inside Netscape program, go to "COMMUNICATOR" and select "GROUPSNEWS"
Now, click on any of the newsgroups that will appear... Inside this
newsgroup, click on "TO NEWS". Now you'll be in the messages box.



Nº.4> Fill that space. That will be the tittle everybody that goes through
that group will see.



Nº.5> Paste your letter inside the program... Then press "SEND"
AND YOU JUST FINISHED WITH YOUR GIRST GROUP. CONGRATULATIONS!!!


IF YOU USE INTERNET EXPLORER


Nº. 3: Go to Newsgroups and select to post an article. Or to post an
announcement, etc.


Nº. 4: Paste the article the same way used in NETSCAPE.. and THAT'S ALL


You just have to enter in different newsgroups and paste them, and when you
get practice, every new newsgroup will take you just 30 seconds!!!

MAKE SURE ALL THE ADDRESSES ARE WRITTEN CORRECTLY

When time goes by and your name doesn't appear in the list, take the last
announcement of the newsgroup and send 6 dollars to the names in that list,
putting down your name in number 6, repeating the whole proccess.



What you have to keep always in mind is that THOUSANDS of people, all over
the world, connect to Internet everyday and they'll read these articles
everyday, just like YOU AND ME DO NOW!!!!



This way, I think nobody will have troubles to invert 6 dollars and see if
this really works. Some people could say... "And... if nobody decides to
answer me??



What? What's the possibility of this when there are thousands of thousands
of people (like us) that are looking for a way to get financial independence
and pay their debts?? And they're disposed to try... It's estimated that
there are 50 000 to 70 000 new users of Internet EVERY SINGLE DAY!!!



Let's review the reasons for doing this:

The only costs are 6 seals, 6 envelopes and six dollars (One dollar bill for
each one in the list), then publish again the article INCLUDING YOUR NAME in
all the newsgroups or BBS that you want (this is free), send as many e-mails
as you want (remember, more e-mails mean more money) and then JUST WAIT for
the letters. We all have six dollars for a simple inversion that doesn't
mean any kind of effort.



Remember to be honest and this system will work successfully. Be sure to
print the article now, try to keep the list of all the people that send you
money, and see in the newsgroups if everybody is taking place fairly.
Remember, HONESTY IS THE BEST WAY



THAT'S ALL!! All you have to do is go in different newsgroups and post the
articles there, and when you get practice, every new newsgroup will take you
just 30 seconds!!



REMEMBER, THE MORE NEWSGROUPS YOU GET ADN THE MORE E-MAILS YOU SEND, THE
MORE MONEY YOU'LL GET

BUT YOU HAVE TO GO IN AT LEAST 200 NEWSGROUPS

ANOTHER EXCELLENT WAY IS GETTING E-MAILS, AT LEAST 200 ADRESSES (THE BIGGER
NUMBER, THE MORE MONEY) THIS RESULTS FROM 5% TO 15%



AND IT'S DONE!!... you'll be receiving money from all over the world, from
place you don't even know, and in a short time!! If you prefer, use
nicknames, as long as the postman deliver the mail in the right place


JUST SIT AND ENJOY; 'CAUSE MONEY IS ON THE WAY!!! Expect for a little amount
of money during the second week, but since the third one.. A THUNDER OF
LETTERS in your inbox!!!



All you have to do is receive it, and try not to scream so loud when you
realize that this time you got it!!!



It's time to pay your debts and buy something special for you or for that
special person in your life, a gift you'll never forget. ENJOY LIFE!!!


And when you need money again, just reactivate this file and resend i ton
the same places you're gonna do now, and another new places you'l know in
the future. Always keep by your hand this article, and reactivate it every
time you need money



IT'S AN INCREDIBLE TOOL... YOU ALWAYS MAY USE AGAIN WHEN YOU NEED MONEY IN A
FAST WAY


HONESTY

IS WHAT MAKES THIS PROGRAM SUCCESSFUL. DON'T FORGET IT
GOOD LUCK!!!