Order of inner joins on SQL statement...

[lorirobn]

Hi,

I created a query in Design mode, and now see (after getting unexpected results) the SQL generated is different than what I would have expected.

I have a main table, tblSpaceUse, and 4 lookup tables that I need to read for descriptions.

I add tblSpaceUse first in design mode, and then add the 4 lookup tables.

The SQL generated lists one of the lookup tables first, after the FROM clause. I would have expected my main table, tblSpaceUse, to be listed first. This is creating problems in the results. I have tried to change the SQL on my own, but am having no luck (I get Join errors). Also tried adding tables one by one in design mode, but ACCESS always changes the SQL to having a lookup table first, and tblSpaceUse last.

Here is the SQL generated. Notice tlkpDesignType is first after the FROM.

SELECT tblSpaceUse.*, tlkpRoomCategory.RoomCategoryDesc, tlkpRoomType.RoomTypeDesc, tlkpView.ViewDesc, tlkpDesignType.DesignDescription
FROM tlkpDesignType INNER JOIN (tlkpView INNER JOIN (tlkpRoomType INNER JOIN (tblSpaceUse INNER JOIN tlkpRoomCategory ON tblSpaceUse.RoomCategoryID=tlkpRoomCategory.RoomCategoryID) ON tlkpRoomType.RoomTypeID=tblSpaceUse.RoomTypeID) ON tlkpView.ViewCode=tblSpaceUse.ViewID) ON tlkpDesignType.DesignType=tblSpaceUse.DesignTypeID;

Can someone help me change this around so tblSpaceUse is first? (and why is the SQL generator making it like this?)
Thank you very much in advance.

 

[Golom]

Access loves parentheses

SELECT U.*, R.RoomCategoryDesc, T.RoomTypeDesc, V.ViewDesc, D.DesignDescription
FROM (((tblSpaceUse U INNER JOIN tlkpRoomCategory R 
        ON U.RoomCategoryID=R.RoomCategoryID)

        INNER JOIN tblDesignType D 
        ON D.DesignType=U.DesignTypeID)

        INNER JOIN tlkpView V 
        ON V.ViewCode=U.ViewID)

        INNER JOIN tlkpRoomType T 
        ON T.RoomTypeID=U.RoomTypeID

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[lorirobn]

Thanks, Golom. I put in that code, and it works. I did get a message after I executed the query the first time, saying the joins couldn't be converted for design mode, but I ignored it, and don't get it anymore. I am assuming that's ok (I think it was because of using the letters rather than table names?).

It really helps to see it written out on separate lines like you have done, and using letters in place of long table names. Unfortunately the query editor compressed the lines together again and took away the nice alignment and spacing,the next time I went in to edit the query.

Thanks for teaching me a little today, and for helping me get this done.
Lori