SQL - Left Join not working with switch statement

Question

I am using MS Access 2007. Whenever I type Left Join, I get an error that says Left Join is not supported. Here's the SQL

TRANSFORM Count([Letter Status].Customer_ID) AS CountOfCustomer_ID 
SELECT Switch(
            [Race_1]=1,"White",
            [Race_1]=2,"Black",
            [Race_1]=3,"Asian",
            [Race_1]=4,"Hispanic/Latino",
            [Race_1]=5,"American Indian/ Alaskan Native",
            [Race_1]=6,"Native Hawaiian or Pacific Islander",
            [Race_1]=7,"Multiracial",
            [Race_1]=8,"Other",
            [Race_1]=9,"Unknown"
        ) AS Race 
FROM Demographics 
    INNER JOIN (
        [Status] 
        INNER JOIN 
            Research 
                ON [Status].Customer_ID = Research.Customer_ID
        ) 
        ON (Demographics.ID = [Letter Status].Customer_ID) 
        AND (Demographics.ID = Research.Customer_ID) 
WHERE ((([Status].Count)=1)) 
GROUP BY [Status].Count, Demographics.Race_1 
ORDER BY Research.Store_site 
PIVOT Research.Store_site In (1,2,3,4,5,6,7,8,9,10);

Could someone please use the above code to show me where to place the Left Join in order to allow the rows for each Race to show in the table? Also, could you please show me how to replace Null Values with 0 in this code? I have tried to implement NZ() but have been unsuccessful.

Please help.

HansUp · Accepted Answer · 2011-06-27 13:08:47Z

3

Look closely at your FROM clause.

FROM Demographics 
    INNER JOIN (
        [Status] 
        INNER JOIN 
            Research 
                ON [Status].Customer_ID = Research.Customer_ID
        ) 
        ON (Demographics.ID = [Letter Status].Customer_ID) 
        AND (Demographics.ID = Research.Customer_ID)

You have an ON condition which refers to [Letter Status], but [Letter Status] isn't included among your source tables .... you do have one called [Status].

Start by building the JOINs in the query designer, so you know you're starting with SQL which Access will accept.

And simplify this thing until you get the JOINs sorted out. Get rid of Switch, TRANSFORM, PIVOT, and GROUP BY until after you have JOINs working.

edited Jun 27, 2011 at 13:08

answered Jun 27, 2011 at 13:02

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL - Left Join not working with switch statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related