Updateable Query

[Guest]

Why can't access just read my mind. It would be easier.

I have 2 table
tblStudents-Stores Name, ID#s, Ethnicity, Etc
tblTests - Stores which particular tests the students should take Math,
Reading, Science Etc.

Currently tblTests is empty. I would like to set up a query that links the
tblStudents with tblTests via the LocID number with all records from
tblStudent showing. LocIDis the primary key for both tables. When I tried
to link them it makes the query uneditable. I looked on the site and tried
to include all the fields from tblTests and only last and first name from
tblStudents, to no avail.

Anyone willing to attempt it?

SELECT tblStudents.Last, tblStudents.First, tblWatch.ID, tblWatch.LocID,
tblWatch.StudentWatch, tblWatch.MathWatch, tblWatch.ReadWatch,
tblWatch.WriteWatch, tblWatch.SciWatch, tblWatch.SSWatch,
tblWatch.NewStuWatch, tblWatch.Watch2, tblWatch.Watch3
FROM tblWatch RIGHT JOIN tblStudents ON tblWatch.LocID = tblStudents.LocID;

 

[Guest]

First off if LocID is the PK for both tables and joined on that field, you'll
have a 1-1 relationship. In other words, you'll only be able to have one
matching Test record for each Student. Is this what you want?

Next are both tables joined together in the Relationships window with
Referiential Integrity enabled? You won't have much of a chance creating an
updateable query if you don't.

Finally you'd be much better off creating a form based on tblStudents and on
it a subform based on tblTests. If you have the relationship defined between
the two tables in the Relationships window, the wizard will make this easy to
do.

 

[John W. Vinson]

Why can't access just read my mind. It would be easier.

I have 2 table
tblStudents-Stores Name, ID#s, Ethnicity, Etc
tblTests - Stores which particular tests the students should take Math,
Reading, Science Etc.

You need THREE tables, not two, if each student should take multiple tests.

tblStudents
StudentID <Primary Key>
LastName
FirstName
<other biographical data>

tblTests
TestID <primary Key>
TestName <e.g. Math, Reading, ...>
<other info about the test itself>

tblRequirements
StudentID <link to tblStudents>
TestID <link to tblTests>
<other info about THIS student and THIS test, e.g. date needed, date taken,
score on the test, etc.>

You could use a Form based on tblStudents with a subform based on
tblRequirements to enter multiple tests per student.

John W. Vinson [MVP]