query not allowing me to update

[susie]

can i update two tables through 1 query. one table has all students and the
other table has status per year the query pulls all students for 2004 but
won't let me modify the data.

 

[Dirk Goldgar]

can i update two tables through 1 query. one table has all students
and the other table has status per year the query pulls all students
for 2004 but won't let me modify the data.

Post the SQL of the query. Queries involving two tables related one to
many are usually updatable, but there are circumstances that can prevent
that.

 

[susie]

the sql ms produced:
SELECT students.Student, students.[First Name], students.[Last Name],
students.[Beginning Date], students.DOB, [student info].ID, [student
info].keyword, [student info].Returning, [student info].[Date Tuition
Agreement Sent], [student info].[Date Enrollment Agreement Sent], [student
info].Term, [student info].Program, [student info].Status, [student
info].[Conference Tim], [student info].[Multiple Address], [student
info].[Trinity Parking], [student info].[Pick Up Notes], [student info].[Car
Pool Number], [student info].[Car Pool Members], [student info].[App Rec'd],
[student info].[App Fee Paid], [student info].[Letter Sent], [student
info].[Accept Sent], [student info].[Deposit Amount], [student info].Balance,
[student info].Tuition, [student info].[Tuition Agreement Rec'd], [student
info].[Enrollment Rec'd], [student info].Notes, [student info].[General Info
Form], [student info].[Supp Emergency Form], [student info].[Photographic
Release], [student info].[Medical Release], [student info].Transportation,
[student info].[Semester 1 Tuition], [student info].[Semester 2 Tuition], *
FROM [student info] INNER JOIN students ON [student info].Student =
students.Student
WHERE ((([student info].Term)="fall 04-05") AND (([student
info].Status)="accept"));

 

[Dirk Goldgar]

the sql ms produced:
SELECT students.Student, students.[First Name], students.[Last Name],
students.[Beginning Date], students.DOB, [student info].ID, [student
info].keyword, [student info].Returning, [student info].[Date Tuition
Agreement Sent], [student info].[Date Enrollment Agreement Sent],
[student info].Term, [student info].Program, [student info].Status,
[student info].[Conference Tim], [student info].[Multiple Address],
[student info].[Trinity Parking], [student info].[Pick Up Notes],
[student info].[Car Pool Number], [student info].[Car Pool Members],
[student info].[App Rec'd], [student info].[App Fee Paid], [student
info].[Letter Sent], [student info].[Accept Sent], [student
info].[Deposit Amount], [student info].Balance, [student
info].Tuition, [student info].[Tuition Agreement Rec'd], [student
info].[Enrollment Rec'd], [student info].Notes, [student
info].[General Info Form], [student info].[Supp Emergency Form],
[student info].[Photographic Release], [student info].[Medical
Release], [student info].Transportation, [student info].[Semester 1
Tuition], [student info].[Semester 2 Tuition], * FROM [student info]
INNER JOIN students ON [student info].Student = students.Student
WHERE ((([student info].Term)="fall 04-05") AND (([student
info].Status)="accept"));

I don't see that you've included [student info].Student in the field
list. Try including that field. Also, make sure that field is indexed.

 

[susie]

i tried including the missing field and then scrapped ms sql and did my own
with no inner join and i still can't modify any data when the query executes.
i've searched permissions and updates but nothing seems to be working.

here's my shortened sql
SELECT Students.*,[student info].*
FROM Students, [student info]
where Students.student = [student info].student and [student info].term =
"fall 04-05" and [student info].status = "accept";

the sql ms produced:
SELECT students.Student, students.[First Name], students.[Last Name],
students.[Beginning Date], students.DOB, [student info].ID, [student
info].keyword, [student info].Returning, [student info].[Date Tuition
Agreement Sent], [student info].[Date Enrollment Agreement Sent],
[student info].Term, [student info].Program, [student info].Status,
[student info].[Conference Tim], [student info].[Multiple Address],
[student info].[Trinity Parking], [student info].[Pick Up Notes],
[student info].[Car Pool Number], [student info].[Car Pool Members],
[student info].[App Rec'd], [student info].[App Fee Paid], [student
info].[Letter Sent], [student info].[Accept Sent], [student
info].[Deposit Amount], [student info].Balance, [student
info].Tuition, [student info].[Tuition Agreement Rec'd], [student
info].[Enrollment Rec'd], [student info].Notes, [student
info].[General Info Form], [student info].[Supp Emergency Form],
[student info].[Photographic Release], [student info].[Medical
Release], [student info].Transportation, [student info].[Semester 1
Tuition], [student info].[Semester 2 Tuition], * FROM [student info]
INNER JOIN students ON [student info].Student = students.Student
WHERE ((([student info].Term)="fall 04-05") AND (([student
info].Status)="accept"));

I don't see that you've included [student info].Student in the field
list. Try including that field. Also, make sure that field is indexed.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

i tried including the missing field and then scrapped ms sql and did
my own with no inner join and i still can't modify any data when the
query executes. i've searched permissions and updates but nothing
seems to be working.

here's my shortened sql
SELECT Students.*,[student info].*
FROM Students, [student info]
where Students.student = [student info].student and [student
info].term = "fall 04-05" and [student info].status = "accept";

You must have the join, if the query is to be updatable. Are you saying
that this query:

SELECT Students.*, [student info].*
FROM Students INER JOIN [student info]
ON Students.Student = [student info].Student
WHERE [student info].term = "fall 04-05"
AND [student info].status = "accept";

is not updatable? Exactly how did you test it? Do both tables have a
primary key? Is the Student field the primary key of the Students
table? Is the the Student field in the [student info] table indexed?

 

[susie]

genious!!!! whatever your sql had (i quess that would be the inner join) it
worked!! thank you for helping this school secretary that was about to throw
her computer out the window....

i tried including the missing field and then scrapped ms sql and did
my own with no inner join and i still can't modify any data when the
query executes. i've searched permissions and updates but nothing
seems to be working.

here's my shortened sql
SELECT Students.*,[student info].*
FROM Students, [student info]
where Students.student = [student info].student and [student
info].term = "fall 04-05" and [student info].status = "accept";

You must have the join, if the query is to be updatable. Are you saying
that this query:

SELECT Students.*, [student info].*
FROM Students INER JOIN [student info]
ON Students.Student = [student info].Student
WHERE [student info].term = "fall 04-05"
AND [student info].status = "accept";

is not updatable? Exactly how did you test it? Do both tables have a
primary key? Is the Student field the primary key of the Students
table? Is the the Student field in the [student info] table indexed?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

genious!!!! whatever your sql had (i quess that would be the inner
join) it worked!! thank you for helping this school secretary that
was about to throw her computer out the window....

LOL I'm glad to have saved the school district some money.