Create Query to combine data

[esbencito]

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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Store ID[/TD]
[TD]Store Name[/TD]
[TD]Store Type[/TD]
[TD]Retail Channel[/TD]
[TD]SLS RTL[/TD]
[/TR]
[TR]
[TD]30001[/TD]
[TD]NANNING MIXC[/TD]
[TD]FSS[/TD]
[TD]RLS[/TD]
[TD]2500[/TD]
[/TR]
[TR]
[TD]30001[/TD]
[TD]NANNING MIXC[/TD]
[TD]FSS[/TD]
[TD]RLS[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]30001[/TD]
[TD]NANNING MIXC[/TD]
[TD]FSS[/TD]
[TD]RLS[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]60002[/TD]
[TD]GRAND GATEWAY[/TD]
[TD]SIS[/TD]
[TD]RLC[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]60002[/TD]
[TD]GRAND GATEWAY[/TD]
[TD]SIS[/TD]
[TD]RLC[/TD]
[TD]4500[/TD]
[/TR]
[TR]
[TD]60002[/TD]
[TD]GRAND GATEWAY[/TD]
[TD]SIS[/TD]
[TD]RLC[/TD]
[TD]3250[/TD]
[/TR]
[TR]
[TD]60002[/TD]
[TD]GRAND GATEWAY[/TD]
[TD]SIS[/TD]
[TD]RLC[/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]80003[/TD]
[TD]HANGZHOU XIAOSHAN[/TD]
[TD]FSS[/TD]
[TD]RLE[/TD]
[TD]675[/TD]
[/TR]
[TR]
[TD]80003[/TD]
[TD]HANGZHOU XIAOSHAN[/TD]
[TD]FSS[/TD]
[TD]RLE[/TD]
[TD]550[/TD]
[/TR]
</tbody>[/TABLE]

LookUp Table:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Store ID[/TD]
[TD]Provin[/TD]
[/TR]
[TR]
[TD]30001[/TD]
[TD]Guangxi[/TD]
[/TR]
[TR]
[TD]80003[/TD]
[TD]Zhejiang[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Store ID[/TD]
[TD]Store Name[/TD]
[TD]Store Type[/TD]
[TD]Retail Channel[/TD]
[TD]Province[/TD]
[TD]SLS RTL[/TD]
[/TR]
[TR]
[TD]30001[/TD]
[TD]NANNING MIXC[/TD]
[TD]FSS[/TD]
[TD]RLS[/TD]
[TD]Guangxi[/TD]
[TD]2500[/TD]
[/TR]
[TR]
[TD]30001[/TD]
[TD]NANNING MIXC[/TD]
[TD]FSS[/TD]
[TD]RLS[/TD]
[TD]Guangxi[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]30001[/TD]
[TD]NANNING MIXC[/TD]
[TD]FSS[/TD]
[TD]RLS[/TD]
[TD]Guangxi[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]60002[/TD]
[TD]GRAND GATEWAY[/TD]
[TD]SIS[/TD]
[TD]RLC[/TD]
[TD][/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]60002[/TD]
[TD]GRAND GATEWAY[/TD]
[TD]SIS[/TD]
[TD]RLC[/TD]
[TD][/TD]
[TD]4500[/TD]
[/TR]
[TR]
[TD]60002[/TD]
[TD]GRAND GATEWAY[/TD]
[TD]SIS[/TD]
[TD]RLC[/TD]
[TD][/TD]
[TD]3250[/TD]
[/TR]
[TR]
[TD]60002[/TD]
[TD]GRAND GATEWAY[/TD]
[TD]SIS[/TD]
[TD]RLC[/TD]
[TD][/TD]
[TD]150[/TD]
[/TR]
[TR]
[TD]80003[/TD]
[TD]HANGZHOU XIAOSHAN[/TD]
[TD]FSS[/TD]
[TD]RLE[/TD]
[TD]Zhejiang[/TD]
[TD]675[/TD]
[/TR]
[TR]
[TD]80003[/TD]
[TD]HANGZHOU XIAOSHAN[/TD]
[TD]FSS[/TD]
[TD]RLE[/TD]
[TD]Zhejiang[/TD]
[TD]550[/TD]
[/TR]
</tbody>[/TABLE]

How can I achieve that? I tried with below, but that doesn't seem to work...

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

 

[Joe4]

If you have spaces in table or field names, you need to surround them in square brackets (best practice says to NOT use blank spaces in object or field names, but use something like underscores instead).
So query would need to look something like:

SELECT D.[Store ID], D.[Store Name], D.[Store Type], D.[Retail Channel], L.Province, D.[SLS RTL] 
FROM [Raw Data] AS D
INNER JOIN LookUp AS L
ON D.[Store ID]=L.[Store ID];

 

[esbencito]

Just tried it with your code, but I still get a "Type mismatch in expression" error? Unfortunately, it doesn't seem to locate the error more specifically...

 

[stumac]

Sound like one of the STORE ID fileds is text and the other is a number - the best way to sort it is to change them both to be the same. one may be set to Number or AutoNumber - the other should just be number.

Test it like this and see if it works:

SELECT D.[Store ID], D.[Store Name], D.[Store Type], D.[Retail Channel], L.Province, D.[SLS RTL] 
FROM [Raw Data] AS D
INNER JOIN LookUp AS L
ON D.[Store ID][COLOR=#FF0000]*1[/COLOR]=L.[Store ID][COLOR=#FF0000]*1[/COLOR];

This should run - but you wont be able to edit the query in design view - therefore I would recommend always using the same datatypes.

 

[esbencito]

You're absolutely right! one field is stored as text the other one as number! Thanks!!

Though, your solution doesn't seem to work and I also cannot change the text field to a number field without losing data? When I import the data I chose "Integer" and it returns a list of "ImportErrors" and I'm only left with the actual numbers. The problem is, some of the Store IDs are e.g. "Store 1" some are e.g. "3001" and since I'm not handling the original database, I cannot change that...

 

[stumac]

you Should then create a subquery and use that to join - selecting only the numeric values (as that is the only ones that could match anyway).

SELECT D.[Store ID], D.[Store Name], D.[Store Type], D.[Retail Channel], L.Province, D.[SLS RTL] 
FROM [Raw Data] AS D
INNER JOIN LookUp AS L
ON D.[Store ID][COLOR=#ff0000]&""[/COLOR]=L.[Store ID][COLOR=#ff0000]&""[/COLOR];

Does this work?

 

[esbencito]

Yes!! That works! even though I'm not sure it always will, as the data might have text values as well at some point (currently it does not).

I just noticed, the query only returns the rows where it finds a match, and leaves out the other ones. How can I include everything and just keep Province blank for each store that doesn't have an assigned province?? The store still has sales data, so it cannot be left out...

 

[stumac]

You should definitely try to get the datatype to be text or numeric on both.

That will make it much easier if you make changes etc.

SELECT D.[Store ID], D.[Store Name], D.[Store Type], D.[Retail Channel], L.Province, D.[SLS RTL]
FROM [Raw Data] AS D LEFT JOIN LookUp AS L ON D.[Store ID]&"" = L.[Store ID]&"";

This will work if the join fields have text in them, however you cant edit in design mode and you may find it slow with a large number of records.

 

[esbencito]

Thanks a lot!! This seems to work great!!