Hi all,
I'm trying to combine two different data sources. Unfortunately, since I'm quite new to Access, I don't seem to be able to figure it out. I have the following two tables that I need to combine:
Raw Data Table:
<tbody>
</tbody>
LookUp Table:
<tbody>
</tbody>
Now I want to create a Query from both tables, matching the correct Province for each Store ID in my Raw Data table and fill a blank or zero if there's no match. So the result should be something like this:
<tbody>
</tbody>
How can I achieve that? I tried with below, but that doesn't seem to work...
I'm trying to combine two different data sources. Unfortunately, since I'm quite new to Access, I don't seem to be able to figure it out. I have the following two tables that I need to combine:
Raw Data Table:
Store ID | Store Name | Store Type | Retail Channel | SLS RTL |
30001 | NANNING MIXC | FSS | RLS | 2500 |
30001 | NANNING MIXC | FSS | RLS | 1000 |
30001 | NANNING MIXC | FSS | RLS | 500 |
60002 | GRAND GATEWAY | SIS | RLC | 250 |
60002 | GRAND GATEWAY | SIS | RLC | 4500 |
60002 | GRAND GATEWAY | SIS | RLC | 3250 |
60002 | GRAND GATEWAY | SIS | RLC | 150 |
80003 | HANGZHOU XIAOSHAN | FSS | RLE | 675 |
80003 | HANGZHOU XIAOSHAN | FSS | RLE | 550 |
<tbody>
</tbody>
LookUp Table:
Store ID | Provin |
30001 | Guangxi |
80003 | Zhejiang |
<tbody>
</tbody>
Now I want to create a Query from both tables, matching the correct Province for each Store ID in my Raw Data table and fill a blank or zero if there's no match. So the result should be something like this:
Store ID | Store Name | Store Type | Retail Channel | Province | SLS RTL |
30001 | NANNING MIXC | FSS | RLS | Guangxi | 2500 |
30001 | NANNING MIXC | FSS | RLS | Guangxi | 1000 |
30001 | NANNING MIXC | FSS | RLS | Guangxi | 500 |
60002 | GRAND GATEWAY | SIS | RLC | 250 | |
60002 | GRAND GATEWAY | SIS | RLC | 4500 | |
60002 | GRAND GATEWAY | SIS | RLC | 3250 | |
60002 | GRAND GATEWAY | SIS | RLC | 150 | |
80003 | HANGZHOU XIAOSHAN | FSS | RLE | Zhejiang | 675 |
80003 | HANGZHOU XIAOSHAN | FSS | RLE | Zhejiang | 550 |
<tbody>
</tbody>
How can I achieve that? I tried with below, but that doesn't seem to work...
Code:
SELECT D.Store ID, D.Store Name, D.Store Type, D.Retail Channel, L.Province, D.SLS RTLFROM Raw Data AS D, LookUp AS L
INNER JOIN LookUp
ON D.Store ID=L.Store ID