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.