Syntax for this Query

[elysia]

Hey! So I'm having a bit of trouble writing this syntactically and hopefully the explanation of my situation below is not too confusing.

Specifically, I have three tables (1, 2, 3) and there are three different fields (A, B, C) within Table1 I want to reproduce and two different fields (X, Y) to map them with. Basically, Table 1 should be updated in columns 1, 2, 3 based off 2 and 3.


It should be mapped to check case one first and then case two only if case one didn't yield a join:


Case1:


Field A - all cells from A should be replaced by those from another field in table2 where for that row table1.fieldx=table2.fieldx


Field B - all cells from B should be replaced by those from another field in table3 where for that row table1.fieldx=table3.fieldx


Field C - all cells from C should be replaced by those from another field in table2 where for that row table1.fieldx=table2.fieldx


Case2:


Field A - all remaining cells from A should be replaced by those from another field in table1 where for that row table1.fieldy=table2.fieldy


Field B - all remaining cells from B should be replaced by those from another field in table3 where for that row table1.fieldx=table2.fieldx


Field C - all remaining cells from C should be replaced by those from another field in table3 where for that row table1.fieldx=table3.fieldx


So logically, I was thinking of doing a select join first to create two separate tables for each case then somehow merge to two (maybe update tblCase1 by checking for null cells) but I'm kind of lost in putting it together in code. I've googled, watched videos, played around with the language. I just can't get the syntax right.

Would really appreciate some help on this.

[ranman256]

you dont need code.
have you tried building the queries in query design? Start with SELECT queries to see if it pulls the correct data.
if so, change it to UPDATE query.

[isladogs]

Agree with ranman about using update queries
However, if this a process you will need to repeat, I would save the queries as SQL and add them to a VBA procedure to run in sequence
BTW - Access doesn't have cells - it has fields and records