By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,210 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Select Replace in MS Access Queries

P: 11
Hi,

I have 3 tables. Table1, Table 2 and Table3.

How Can I update(only one time) Table 2 using the Mapping Table3 ?



Table 1 have the ff fields:
_______________________________
Transaction | ProductType | Product |
-----------------------------------------------------
001 | | X
002 | | Y


Table 2 have the ff fields:
_______________________________
Transaction | ProductType | Product |
-----------------------------------------------------
| | | |
| | | |


Mapping Table 3 have the fields:
___________________________________
| Productype1 | Productype2
-----------------------------------------------------------
11111111 | 22222222


Conditions:

If the Product field of Table 1 is equal to X:
Get Productype1 in Table 3 and Place it in ProductType field in Table2

If the Product is equal to Y:
Get Productype2 in Table 3 and Place it in ProductType field in Table 2


Here is my Query:

UPDATE Table2 SET Table2.Product = "1111111"
WHERE ((([Table1].Product)="X"));

I think this is incomplete, because, this is only applicable to 1 condition which is If Product is X, What if Product isequal to Y, Does it mean Another query should be made?

How Can I update(only one time) Table 2 using the Mapping Table3 ?

Is Select Replace applicable?

Thank you for taking time to help me.
Jul 25 '08 #1
Share this Question
Share on Google+
1 Reply


Expert Mod 2.5K+
P: 2,545
Hi, and welcome to Bytes!

Thank you for taking the time to put in some details of your three tables, but unfortunately the data you have provided does not help us here. Could you post samples of actual data? As it stands the dummy data is too limited to give us much of a clue as to what you really want to replace with what.

In particular: what are the actual contents of mapping table 3? What are the real product types etc involved? Is Table 2 truly empty, as it appears to be from your post? Is there no content for Product Type in table 1, as it appears to be blank from what you have posted? What relationships (if any) exist between tables 1, 2 and 3, and what are the key fields of these tables?

-Stewart
Jul 26 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.