How do I assign a name to blank query results?

[clintthorn]

I have a query that is pulling from 2 tables to help categorize the data. I have country fields that I am associating with certain geos. But there is a large list of countries that do not correlate to my geo buckets. Is there a way that I can assign a name (e.g., "other") and have them included in my query? I am using Access 2003. Below is my SQL code.

SELECT [Data_Table].Date, Geo_Table.Geo, [Data_Table].Country, [Data_Table].Product, [Data_Table].Distcode
FROM Geo_Table LEFT OUTER JOIN Data_Table ON Geo_Table.[Country] = [Data_Table].Country;

Thanks in advance!

 

[WayneRyan]

CT,

I'm a little confused about the joins, but ...

SELECT [Data_Table].Date, 
       Geo_Table.Geo, 
       [B]Nz([Data_Table].Country, "Other"),[/B]
       [Data_Table].Product, 
       [Data_Table].Distcode
FROM Geo_Table LEFT OUTER JOIN Data_Table ON 
       Geo_Table.[Country] = [Data_Table].Country;

Wayne

 

[clintthorn]

I am not sure if the join is correct I saw somewhere that I needed to use "outer join" or "left outer join." It would not let me use "outer join."

I tried the Nz (), but it doesn't work. I pull the data into pivot table in excel and it tells me [Microsoft][ODBC Microsoft Access Driver] Undefined function 'Nz' in expression.

 

[WayneRyan]

CT,

If it's entirely within Access (Not SQL Server), the Nz function should work.

Which table has the "missing" data?

If it's the Geo_Table, just change it to a simple "Left Join".

Wayne

 

[clintthorn]

Wayne,

I tried Right Join and it shows the fields that have a blank geo. However, it doesn't populate them with "Other"

Here is the actual code (previous just simplified, probably should have shown the original in the first place):

SELECT [2008_Q3_DLP_Table].DATE, Geo_Table.Geo, [2008_Q3_DLP_Table].CLIENT, [2008_Q3_DLP_Table].COUNTRY, Nz([2008_Q3_DLP_Table].Country,"Other"), [2008_Q3_DLP_Table].LANGUAGE, [2008_Q3_DLP_Table].PRODUCT, [2008_Q3_DLP_Table].BRANDCODE, [2008_Q3_DLP_Table].ACTION, [2008_Q3_DLP_Table].COUNT, [2008_Q3_DLP_Table].DISTCODE
FROM Geo_Table RIGHT JOIN 2008_Q3_DLP_Table ON Geo_Table.[2 digit] = [2008_Q3_DLP_Table].COUNTRY;

Again Thanks for your help.