Relationship problem, query data with undesired results, please help!!

dean20 · Apr 7, 2000

First of all, Thanks DougP and Elizabeth for your help! I have another question.  I have studentID in student table 1:n grade table  I have assignmentID in Assignment table 1:n in grade table I cannot get a full student listing on the subform. It only lists those students that already have studentID and AssignmentID linked in one record in the grade table. How can I get a full student listing (those with and those without stored grades) on the subform when linking to the master form by assignmentID? How do I get the unique records to automatically be created in the Grade Table?    EX. Student1 Assignment1        Student1 Assignment2        Student2 Assignment1        Student2 Assignment2 I would like these records added when an assignment is added. I hope this is a good explanation. Thanks In advance, Dean

DougP · Apr 7, 2000

This somehow sounds familiar. Right thats they way a query works. It only returns the records that match both tables. Is it possible to add a student to both tables and leave the grades blank In other words StudentID would be added to both. Then it will pull up everyone.

DougP · Apr 7, 2000

you could use a Save Button which you click and it would save the forms contents to 2 or more tables here's some code I gave someone else awhile ago ----------------------------------------------     DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70     Dim db As Database, rst1, rst2, rst3 As Recordset     Set db = CurrentDb     Set rst1 = db.OpenRecordset("Table1&quot

Set rst2 = db.OpenRecordset("Table2&quot

Set rst3 = db.OpenRecordset("Table3&quot

rst1.AddNew     rst2.AddNew     rst3.AddNew     rst1![employee number] = Me![employee number]     rst2![employee number] = Me![employee number]     rst3![employee number] = Me![employee number]     rst1.Update     rst2.Update     rst3.Update     db.Close

Guest_imported · Apr 9, 2000

A JOIN gives a union. A LEFT JOIN gives all the rows on from the left table and all rows on the right table that match the condition. A RIGHT JOIN gives all the left rows that match the condition and all the rows from the right. Given: "Student" table id, int name, varchar(?) 1,"fred" 2,"daisy" 3,"chris" "Assignment" table id, int dsc, text 1,"Write Paper" 2,"Read Book" 3,"Watch Movie" "Grade" table Student, int (FK from Student) assignment, int (FK from Assignment) grade, char(2) 1,1,"B" 1,2,"A-" 2,2,"B+" SELECT s.name, a.dsc, g.grade from grade g join student s on s.id = g.student join assignment a on a.is = g.assignment gives graded assignments "fred","Write Paper","B" "fred","Read Book","A-" "daisy","Write Paper","B+" SELECT s.name, a.dsc, g.grade from grade g LEFT join student s on s.id = g.student join assignment a on a.is = g.assignment gives all students, regardless of grades "fred","Write Paper","B" "fred","Read Book","A-" "daisy","Write Paper","B+" "chris", Null, Null SELECT s.name, a.dsc, g.grade from grade g join student s on s.id = g.student RIGHT join assignment a on a.id = g.assignment gives all assignments regardless of grades "fred","Write Paper","B" "fred","Read Book","A-" "daisy","Write Paper","B+" Null, "Watch Movie",Null

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Relationship problem, query data with undesired results, please help!!

dean20

Technical User

DougP

MIS

DougP

MIS

Guest_imported

New member

Similar threads

Part and Inventory Search

Sponsor