lookup a value and update a different field based on the value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My goal is to update a field in a SQL database based off two values. My issue
is how to look up two values in different fields and compare their
combination to a second table. Them input that value.

For example... If field one's value is 7 and field two's value is 10% then
based on my table I would input "AC" 7=A & 10%=C.

Thanks
 
Why? As in "why duplicate the data?" If you already have "7" and "10%" in
fields, why not just use a query to associate them?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Hi,


You can lookup on more than one field using a AND:

DLookup( "field3" , "tableName", " field1 = 7 AND field2 =0.1 " )



I missed the point about the second table.



Vanderghast, Access MVP
 
mccloud said:
My goal is to update a field in a SQL database based off two values. My issue
is how to look up two values in different fields and compare their
combination to a second table. Them input that value.

For example... If field one's value is 7 and field two's value is 10% then
based on my table I would input "AC" 7=A & 10%=C.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not enough info about the structure of the tables and the data in them.

A guess: It seems you'd need a translation table or function to
translate 7 to A and 10% to C and, I assume you have more than just 7 &
10%, all other combinations. E.g.: using the Switch() function:

Switch(column1=7, "A",
column1=8, "D",
column1=9, "X") & <- concatenate the results together.

Switch(column2=0.1, "C",
column2=0.5, "E",
column2=1.0, "Z")
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAinqoechKqOuFEgEQLZBwCgzd5703RLSgotwxru3vefvvzgOxMAoOKg
YWU5ay+Y4mxUvoWQntU6pQSj
=eH8j
-----END PGP SIGNATURE-----
 
That works except for Margin I have a between statement and I can't get it to
work.
End: Switch([sum_of_errors]<7,"A",[sum_of_errors]>12,"C",[sum_of_errors]
Between '7' And '12',"B")
Can a between statement be used here or is there a better way? It returns an
#error in the query.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Usually, the BETWEEN works. You might want to remove the delimiting
quotes on the numbers since the data types appear to be numeric.

If the BETWEEN still doesn't work, try this:

sum_of_errors >= 7 And sum_of_errors <= 12

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAywOIechKqOuFEgEQL2ngCfflZkA0Ef91ln7HR5b8EzsaTzStEAoPYa
1TXgLHPAoLz7W+MYiF2kcgG0
=FH/M
-----END PGP SIGNATURE-----
That works except for Margin I have a between statement and I can't get it to
work.
End: Switch([sum_of_errors]<7,"A",[sum_of_errors]>12,"C",[sum_of_errors]
Between '7' And '12',"B")
Can a between statement be used here or is there a better way? It returns an
#error in the query.

:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not enough info about the structure of the tables and the data in them.

A guess: It seems you'd need a translation table or function to
translate 7 to A and 10% to C and, I assume you have more than just 7 &
10%, all other combinations. E.g.: using the Switch() function:

Switch(column1=7, "A",
column1=8, "D",
column1=9, "X") & <- concatenate the results together.

Switch(column2=0.1, "C",
column2=0.5, "E",
column2=1.0, "Z")
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRAinqoechKqOuFEgEQLZBwCgzd5703RLSgotwxru3vefvvzgOxMAoOKg
YWU5ay+Y4mxUvoWQntU6pQSj
=eH8j
-----END PGP SIGNATURE-----
Click to expand...
Click to expand...
 
Back
Top