Access SQL query with conditions?

[solidfish]

I am trying to update a table using values from a mapping table. I think its an UPDATE and SELECT statement but I cant figure out how to handle conditions. This is in VBA Access.

I have two tables with following fields:

mainTable: [column1],[column2],[mapColumnA],[mapColumnB]
mapTable: [c1],[mapColumnA],[mapColumnB]

I need to do a mapping from mapTable but it has the following condition

If mainTable.[column1] = "000" or mainTable.[column1] = "001" Then
  mainTable.[column2] = mapTable.[c1]
  WHERE mapTable.[mapColumnA] = mainTable.[mapColumnA] 
  AND mapTable.[mapColumnB] = mainTable.[mapColumnB]
Else
  mainTable.[column2] = mapTable.[c1]
  WHERE mapTable.[mapColumnA] = mainTable.[mapColumnA] 
End If

Is there a way to capture this in a single SQL query?

 

[Ranman256]

put BOTH queries in a UNION query to get both results:

select mainTable.[column2] = mapTable.[c1] from maptable
WHERE mapTable.[mapColumnA] = mainTable.[mapColumnA]
AND mapTable.[mapColumnB] = mainTable.[mapColumnB]
and (mainTable.[column1] = "000" or mainTable.[column1] = "001" )

union

select mainTable.[column2] = mapTable.[c1] from maptable
WHERE mapTable.[mapColumnA] = mainTable.[mapColumnA]
and mainTable.[column1] <> "000"
and mainTable.[column1] <> "001"

 

[plog]

This is in VBA Access.

Are you sure? The second code section you posted is neither SQL nor VBA.

  mainTable.[column2] = mapTable.[c1]
  WHERE mapTable.[mapColumnA] = mainTable.[mapColumnA]

Further, just from a logic point of view no matter the language, the above code isn't dependent on the If/Else, it appears no matter what. So to simplify things, this code (whatever langauge it is) should be moved out of the If/Else.

Again, that's not VBA. It might be best if you describe what you are trying to do a little better.

 

[solidfish]

Are you sure? The second code section you posted is neither SQL nor VBA.

Sorry it is but my code was not complete, it was just pseudo code to show you what I was trying to do.

I am attempting the two queries with UNION right now. Many thanks for the quick responses!