Having trouble with Outer Join

[Beeawwb]

Hi everybody,

Just having some difficulty with an Outer Join, and after numerous readings
of 'how-to' guides and database design primers I thought it might just be
time to ask for some assistance.

Essentially I'm trying to make a grading system. I've broken everything down
to its most basic, and I'll scale it up once I get the query working.

I have a series of classes.
1 class has many users; 1 class has many subjects.
1 user has many reviews.
1 review has many subjects to be rated.

So there is a linking table to cross reference Classes and Subjects.

The goal being: Run a query, and then show all the subjects relevant to a
user, as well as the open reviews, and the ratings applicable.

E.g.
42247 - Motor Class - Review 17/08/2009 - Motor Subject 1 - Rating (would be
null)
42247 - Motor Class - Review 17/08/2009 - Motor Subject 2 - Rating (would be
null)
42247 - Motor Class - Review 17/08/2009 - Motor Subject 3 - Rating (would be
null)

The problem is, I can either get a read only query (which is useless since I
can't then rate the subjects, even though I know which ones need rating) or I
get "an ambiguous outer join" error when attempting to set things up.

Probably easiest to deal with the Read-Only query, as that's at least giving
me the information I want...

Query3: (I've started this over from scratch, so I'm not messing with live
data)

SELECT tbl_User.Name, tbl_Portfolio.PortfolioName,
tbl_CapabilityPortfolio.CapabilityLink, tbl_Review.ReviewDate,
tbl_Review.id_Review
FROM ((tbl_Portfolio INNER JOIN tbl_CapabilityPortfolio ON
tbl_Portfolio.id_Portfolio = tbl_CapabilityPortfolio.PorfolioLink) INNER JOIN
tbl_User ON tbl_Portfolio.id_Portfolio = tbl_User.User_Portfolio) INNER JOIN
tbl_Review ON tbl_User.id_Payroll = tbl_Review.ReviewUser;

Query 4:
SELECT Query3.*, tbl_Rating.Rating
FROM Query3 LEFT JOIN tbl_Rating ON Query3.id_Review = tbl_Rating.UserRating;


This returns a read only query which shows the users, their reviews, and the
Null ratings to be filled in.

Now I understand that there are a number of factors which can cause a query
to become Read-Only. So I thought I'd start even more simple. I've selected
just id_Portfolio, PortfolioName and CapabilityLink. This returns a writeable
query which lists all portfolios and links. As soon as I add tbl_User to the
query, it becomes read only.

I just keep going in circles with this one. I've even sat down and attempted
to see if there may be a problem with the design of my tables (hence starting
from scratch) and I can't see where a problem exists. Surely this sort of
grading system is pretty standard (at least, I would have thought so, since
it's partially referenced in a guide by Crystal Long I found online) that it
wouldn't be too complex.

Any thoughts on where I can start tweaking to get this working?

Thanks in advance for your time and assistance,

Bob

 

[Sylvain Lafontaine]

Well, you don't show us the design of your table, so it's hard for anyone
here to try reproducing your result.

As a suggestion, you're right when saying that there is a number of factor
who can make a query read-only but of all of the possible reasons, having a
query too complex is probably at the top of the list.

In your case, instead of putting everything into a single query, you should
consider using subforms. Setting the UniqueTable property of the form could
also help as well as checking that tbl_User has a primary key defined.

You should also get rid of all these tbl_ prefixes. Bring nothing to the
code but make it harder to read.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)