I'm stuck on this query with multiple joins.

[cydewaze]

The database is MS Access.

I have a page for an online nomination system that allows nominations to be entered online, judges to score those nominations, and admins to view the nominations and the scores. The problem is with the admin page that shows a summary of what's been entered.

I have tables for:

users (these are people submitting nominations, the judges, and the admin)
nominees (these are the nominations, with a column for nomination ID and a column for the ID of who submitted the nom)
judgenom (column for user (judge) IDs and the nomination IDs to which they've been assigned)
scores (the scores for each nomination, including columns for the nomination ID and the ID of the user (judge)

My query looks like this:

SELECT nominees.nom_id
		, nominees.nom_title
		, nominees.tracking_no
		, nominees.nom_firstname & ' ' &  nom_lastname AS nominee
		, nominees.finalized
		, judges.user_id AS judge_id
		, judges.user_name AS judge
		, users.user_name AS nominator
		, users.user_org
FROM ( ( ( 
       nominees
INNER JOIN users
		ON users.user_id = nominees.submit_id
		)
LEFT OUTER JOIN judgenom
		ON judgenom.nomination_id = nominees.nom_id
		)
LEFT OUTER JOIN users AS judges
		ON judges.user_id = judgenom.judge_id
		)

And that works perfectly. The problem is when I try to join the scores table, using this:

LEFT OUTER JOIN scores
		ON scores.nomination_id = nominees.nom_id

As soon as one judge enters a score, the other judge assigned to that nomination vanishes from the table. And I can't for the life of me figure out why.

[r937]

if you were to provide us with some comprehensive sample data, i'm sure we can figure it out

(by comprehensive, i don't mean just sample rows for each table, but rather, data that illustrates the multiple judges and multiple scores for a nomination, with correct key relationships between related rows)

[cydewaze]

You mean like a URL? Without making an account for people to log in (which would be frowned upon by my office) the best I could probably do is a screenshot.

[r937]

can you not write up some CREATE TABLE and INSERT statements?

i realize that this is a lot harder to do in msaccess (in mysql, you can generate them both with one command, mysqldump)

but unless i can see which judges are related to which scores and which nominations, i don't think i can debug your query

[cydewaze]

The closest thing I can find in Access gives me a 38 page text dump.

[r937]

not sure what else to suggest

i can't help you if i can't see your data

[SpacePhoenix]

What version of MS Access are you using?

[guido2004]

Please also post the query (the exact query, not a look-alike) that is giving you the problem.

[cydewaze]

Ok, here's my original query (hurray for backups!):

SELECT nominees.nom_id
		, nominees.nom_title
		, nominees.tracking_no
		, nominees.nom_summary
		, nominees.nom_firstname & ' ' &  nom_lastname AS nominee
		, nominees.finalized
		, judges.user_id AS judge_id
		, judges.user_name AS judge
		, users.user_name AS nominator
		, users.user_org
		, scores.cpi_score 
		, scores.inno_score 
		, scores.pc_score 
		, scores.int_score
		, scores.equ_score
		, scores.ls_score
		, scores.drer_score
		, scores.iis_score
		, scores.css_score
FROM ( ( ( (
       nominees
INNER JOIN users
		ON users.user_id = nominees.submit_id
		)
LEFT OUTER JOIN judgenom
		ON judgenom.nomination_id = nominees.nom_id
		)
LEFT OUTER JOIN users AS judges
		ON judges.user_id = judgenom.judge_id
		)
LEFT OUTER JOIN scores
		ON scores.nomination_id = nominees.nom_id
		)
WHERE scores.judge_id = judgenom.judge_id
OR scores.judge_id IS NULL
ORDER BY nominees.nom_id, judgenom.judge_id

And here's what the db looks like (the ... means that there are more columns,. but they're not involved in the joins or where clauses):

[nominees]
nom_id (primary key, auto number)
submit_id (user ID of person submitting the nomination)
nom_title
nom_summary
...

[users]
user_id (primary key, auto number)
user_name
user_email
...

[judgenom] (assigns nominations to judges)
jn_id (primary key, auto number)
judge_id (user ID of judge)
nomination_id (nom_id of nomination)

[scores]
score_id (primary key, auto number)
nomination_id (nom_id of nomination that this score is for)
judge_id (user ID of judge who submitted this score)
...

My original problem is that when viewing the summary page (that's generated from the query above), you can see all the judges assigned to each nomination. But as soon as one of those judges submits a score, the other two judges vanish. It's almost like I have the wrong type of join, but Access seems very limited to what I can do (i.e. no full outer joins).

Let me know if I need to provide any more info.

[guido2004]

Try:

SELECT nominees.nom_id
		, nominees.nom_title
		, nominees.tracking_no
		, nominees.nom_summary
		, nominees.nom_firstname & ' ' &  nom_lastname AS nominee
		, nominees.finalized
		, judges.user_id AS judge_id
		, judges.user_name AS judge
		, users.user_name AS nominator
		, users.user_org
		, scores.cpi_score 
		, scores.inno_score 
		, scores.pc_score 
		, scores.int_score
		, scores.equ_score
		, scores.ls_score
		, scores.drer_score
		, scores.iis_score
		, scores.css_score
FROM ( ( ( (
       nominees
INNER JOIN users
		ON users.user_id = nominees.submit_id
		)
LEFT OUTER JOIN judgenom
		ON judgenom.nomination_id = nominees.nom_id
		)
LEFT OUTER JOIN users AS judges
		ON judges.user_id = judgenom.judge_id
		)
LEFT OUTER JOIN scores
		ON scores.nomination_id = nominees.nom_id
                AND scores.judge_id = judgenom.judge_id
		)
ORDER BY nominees.nom_id, judgenom.judge_id

[cydewaze]

Guido, that was actually one of the first things I tried, but it results in this:

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Join expression not supported.

So I assumed it was an Access limitation.

[guido2004]

I guess that has to do with the psychopathic way Access uses those brackets. You'll probably have to do something really crazy like

LEFT OUTER JOIN scores
		ON ((scores.nomination_id = nominees.nom_id)
                AND (scores.judge_id = judgenom.judge_id))
		)

[cydewaze]

UGH! I hate Access. I've tried several paren variations, but I feel like I'm trying to guess a strong password or something. Why did they make it like this?

[r937]

Why did they make it like this?

psychopathic

try saving the query, then running it as a saved query

[cydewaze]

try saving the query, then running it as a saved query

I'm not completely sure how to do this.

[r937]

presumably you know how to save a query, right?

start by saving it, then open it in design view, and check the diagram to make sure it's joining on both columns

[cydewaze]

I've actually never used the Access WYSIWYG for anything more than setting up the database or copy/pasting data in datasheet mode. My queries have always been done in the web apps.