By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,505 Members | 1,517 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,505 IT Pros & Developers. It's quick & easy.

access query help

P: n/a
I have two tables:

Assignment Table
AsignmentID, AssignmentName, CourseID

Submit Table
SubmitID, StudentID,AssignmentID, Point

For a particular student in a particular class, I want it to display:

Assignid, AssignmentName, Point, SubmitID

It should list all assignments for a course and 0 for point if not
exist in the submit table and 0 for nonexistence submit ID.

Hence,

if Assignment table has:
111,Ex1,100
222,Ex2,100
333,Ex3,100

and Submit table has
777,111,80,1
777,333,90,2

It should display

111,Ex1,80,1
222,Ex2,0,0
333,Ex3,90,2

Should I create a union query and select out of that?

Feb 13 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Feb 13, 12:51 pm, Loc...@gmail.com wrote:
I have two tables:

Assignment Table
AsignmentID, AssignmentName, CourseID

Submit Table
SubmitID, StudentID,AssignmentID, Point

For a particular student in a particular class, I want it to display:

Assignid, AssignmentName, Point, SubmitID

It should list all assignments for a course and 0 for point if not
exist in the submit table and 0 for nonexistence submit ID.

Hence,

if Assignment table has:
111,Ex1,100
222,Ex2,100
333,Ex3,100

and Submit table has
777,111,80,1
777,333,90,2

It should display

111,Ex1,80,1
222,Ex2,0,0
333,Ex3,90,2

Should I create a union query and select out of that?
You'll actually want an outer join query instead of a union query.
Something like

select Assignment.AsignmentID,
Assignment.AssignmentName,
Submit.Point,
Submit.SubmitID
from Assignment
left outer join Submit
on Assignment.AsignmentID = Submit.AssignmentID

will return all records on the left side of the join (Assignment). If
there are no matching records on the right side of the join (Submit),
Null will be returned for those fields. To return 0's instead of
nulls, wrap the fields from Submit in Nz(), e.g.

select Assignment.AsignmentID,
Assignment.AssignmentName,
Nz(Submit.Point,0),
Nz(Submit.SubmitID,0)
from Assignment
left outer join Submit
on Assignment.AsignmentID = Submit.AssignmentID

Bruce

Feb 13 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.