Complex Access Join (For Me Anyway)

[jimjawn]

Okay. I've run aground and don't know how create this query. If anyone could help me, i'd be much obliged. I guess the real problem that I'm having in my inability to decipher how Access defines its JOIN statements. I can't seem to figure it out. Anyways, any help would be appreciated.

I have four tables, condensed for posterity, attached below. I have an ID number for the HomeState, and I have a Join Table for Multiple State Licenses

tblCE
-----
CEID - PK
Name - Data
HomeStateID - FK

tblState
----------
StateID - PK
StateAbbreviation - Data

tblCEJoinLicense
----------------
CELicenseID - PK
CEID - FK
StateID - FK
LicenseNumber - Data

Basically, the problem I have is this: I need the StateAbbreviation pulled for both the HomeState in tblCE and for Each of the StateId's in tblCEJoin License and I can't for the life of me figure it out. I can JOIN a table twice successfully, and I can JOIN a many to many relationship too, but I can't seem to get the two to meet up.

Data I'm trying to get...
-------------------------
CEID HomeState Licensed In
1 'PA' 'MD'
1 'PA' 'PA'
1 'PA' 'NJ'

I have 5 many to many relationships in this query as well, so if you could point out the Access multiple inner join syntax, you'd be the bomb!

Thanks for any help.

Jim Jawn

 

[RickSpr]

Try this:

SELECT tblCE.CEID, tblState.StateAbbreviation As HomeState,
tblState1.StateAbbreviation As [Licensed In]
FROM ((tblState INNER JOIN tblCE ON tblState.StateID =
tblCE.HomeStateID) INNER JOIN tblCEJoinLicense ON tblCE.CEID =
tblCEJoinLicense.CEID) INNER JOIN tblState AS tblState1
ON tblState1.StateID = tblCEJoinLicense.StateID
ORDER BY tblCE.CEID

Perhaps your confusion comes from mixing up the concepts of a join and a many-to-many relationship, or the associative table that represents a many-to-many relationship (such as tblCEJoinLicense). A join is an operation on two tables; it is not an associative table. (Well, a join is a table in a way, since it yields a result table, but that's not a "real" table--it's not stored in the database. Associative tables are real.)

As for the multiple join syntax, the statement above illustrates it. You join the first two tables (any two will do), surround that in parentheses, and use that as one of the tables for the next join. You should always have one less JOIN operation than you have tables in your query. In this case, you have to treat tblState as two different tables, because you need information from two different rows to form one row of output. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[jimjawn]

Sweet! Thank you.

I realized after I posted the message that there really wasn't a many to many relationship in the example I gave, although I do have a number of them in the query.

You helped me solve the problem though, and the sytax you used for you queries definitley helped me. The problem was my Aliasing of the tables. That's what threw me off.

Thank you very much.