How to selectively insert data in fields

[Wind54Surfer]

Hi all,

I have a table "tblBills" with fields "orderID", "supplier", "suppliername"

and another table "tblInstSales" with fields "orderID", "installername",
"salesrepname"

How can I update the field "suppliername" based on what exists in field
"supplier"

Example: if in field "supplier" entry is "Installer" then use data from
field "installername" and if it is "Salesrep" then use data from
"salesrepname"

I am a newbie with limited query knowledge and appreciate any help.

Thanks in advance,
Emilio

 

[Stefan Hoffmann]

hi Emilio,

I have a table "tblBills" with fields "orderID", "supplier", "suppliername"
and another table "tblInstSales" with fields "orderID", "installername",
"salesrepname"

You normally don't need an UPDATE query, this SELECT does the lookup on
the fly:

SELECT *,
Iif(B.supplier = "Installer",
S.installername,
S.Salesrep) AS suppliername
FROM tblBills B
LEFT JOIN tblInstSales S

How can I update the field "suppliername" based on what exists in field
"supplier"

If you really need to update your table, use the SELECT from above and
make an UPDATE query withit in the query designer.


mfG
--> stefan <--

 

[Wind54Surfer]

Thanks Stefan for your help

Emilio

hi Emilio,

You normally don't need an UPDATE query, this SELECT does the lookup on
the fly:

SELECT *,
Iif(B.supplier = "Installer",
S.installername,
S.Salesrep) AS suppliername
FROM tblBills B
LEFT JOIN tblInstSales S

If you really need to update your table, use the SELECT from above and
make an UPDATE query withit in the query designer.


mfG
--> stefan <--