CODEWITHSUNDEEP
sqlms-accessmappingcasestring-matching
Kangze Huang
Kangze Huang

Reputation: 351

How to map a string to a table column in MS Access?

I need an SQL query to map the string content of a column in TableA to a set of columns in TableB. It looks like this:

TableA
| ID | Status |
---------------
| 0  | "full" |
| 1  | "part" |
|    |        |

TableB
| ID | full | part |expire|
--------------------------
| 0  | true | false| false|
| 1  | false| true | false|
|    |      |      |      |

I am using MS Access and currently this is all I've got:

SELECT * FROM TableA INNER JOIN TableB ON TableA.ID = TableB.ID

Which correctly matches the two tables based on ID. However, I do not know where to go from here. Lets say the following is added to TableA:

{ID:2, Status:"Expire"}

How can I map this from TableA to TableB? Ideally this would all be done in 1 query.

Many thanks in advance! :)

Upvotes: 0

Views: 85

Answers (2)

steve biko
steve biko

Reputation: 171

Your question is not clear enough.

If you want to recreate TableB from TableA then use the IIf ( expr , truepart , falsepart ) function in a query. Example below (added status field - you can exclude it):

Select ID,Status, IIf([Status]="full", true, false) as full, IIf([Status]="part", true, false) as part,IIf([Status]="expire", true, false) as expire from TableA

If you meant to join the two tables then use LEFT OUTER join that will include all rows in TableA. In this case row 3 will have nulls for the boolean flag since there is no matching row.

SELECT TableA.ID,Status,full,part,expire FROM TableA LEFT JOIN TableB ON TableA.ID = TableB.ID

Regards, Bikxs

Upvotes: 1

Andre
Andre

Reputation: 27644

Use the IIf() function for each column.

SELECT ID,
    IIf([Status]='full', True, False) AS [full],
    IIf([Status]='part', True, False) AS [part],
    IIf([Status]='expire', True, False) AS [expire]
FROM Table1

Upvotes: 1

Related Questions