Can't update fields in query (1 Viewer)

[stormin_norm]

I had this working before but now can not.

How can I change the queries in the attached sample database to update vs. readonly.

I want to be able to view a the courses a student 'can' take, plus the grades he/she already has received. Then naturally update the students record. I do NOT want to have a grade record UNLESS there is a grade (in order to keep the number or rows low).

Thanks
Stormin.

 

[reel knotty]

you can't edit or add text through a query with more than 2 tables directly attached. you can however create another query then create a main query from the other query where you would only have 2 tables or queries in the main query and it should work.

IE

design a new query [studentsubquery] with the student info and the syllabus info

Then create a new query with [studentsubquery] and Grade info. This should allow you to update within the query.

 

[stormin_norm]

Thanks knotty.
I ran through several of my forms and noted the ones which work great are only 2 tables. Or work great as subforms.
And of course the one which failed is four tables. I'll break it up as you suggested.

I was hoping to avoid creating even more queries. My application is filled with so many little queries. oh well.

--------
how 'bout those stripers, they must be runnin' good this time of year.

 

[reel knotty]

I've got the same issues! I really need to go through and get rid of things.

 

[stormin_norm]

I was messing around with the tables, and I need to make this a union in order to pick up students which have no grades entered yet in the table. Which makes it even more ugly with number of queries running around out there.
Does it make sense to have vba code doing some processing to keep it cleaner??

Thanks.
norm.

 

[reel knotty]

Norm I don't think you should have any troubles. When you create your main query add the student query and your grades table and just make the relationship right there in the Query. showing all data from student query and only the grades that apply.

 

[AncientOne]

Stormin

is your db constructed in the same way as your sample? No relationships!!!?

I was interested to read that you can't add or edit text through a query with more than two tables. I haven't seen that documented anywhere- where did the info come from?


Union queries aren't updatable of course...

 

[reel knotty]

I think I read that in Microsoft Access 97 developers handbook.
So I have learned to confuse access by having 2 queries and beating the system.

BTW I am self taught in access which would explain my unconventional solitions to problems

 

[stormin_norm]

AncientOne-
No, the real DB does have relationships, the sample DB does not because I did not think it mattered for creating a quick testdb.
The query is performing the outer left join, which is basically what a relationship is doing. I would think the behavior is the same if you define a relationship or define the join in the query. There is only one or two queries in this small sample db.

If three tables CAN be updated via query, then WHY is it not working?

norm.

 

[AncientOne]

on the general subject of updatability:

http://support.microsoft.com/default.aspx?scid=kb;en-us;148581

 

[AncientOne]

Stormin

I will get back to you later on this. I have to deal with a spate of Breaker viruses on our campus.

 

[AncientOne]

Sorry about that, we've been attacked by the Blaster virus and I had to do some work for a change.

Yes, you can join fields in your query and the results will be the same as if you'd joined then in the relationship window,EXCEPT you can't apply referential integrity. And you can join fields quite arbitrarily.

Try this:
1) Remove the student table from query 1 (and the criteria)and run the query on the two remaining tables. It's not updatable. Why?

2) Remove the syllabus table from Query1 and join Students and grades on StudentID. It's updatable. Why?

3) Go into the table design for Student and remove the primary key from Student ID. Is the query updatable any more?

There are well-defined rules about which fields are updatable in queries and they all hinge around the relationships between your tables and how they are joined. I don't think the number of tables matters.

 

[stormin_norm]

Thanks for your time Oh Ancient One!
I hope you've crushed those infectious worms.

The article on updates is great. Lots of rules. I tried to break down tables to smaller tables and linking them to fix the many-to-many issue. This did not work too good. So I have two other options:
2) Push more data into fewer tables having lots more duplication (and breaking normalization rules)
3) Create temp tables and use a VBA routine to update the corresponding rows.

I am tempted to go for option 3 since it provides the most control.

-stormin'

 

[Pat Hartman]

was interested to read that you can't add or edit text through a query with more than two tables. I haven't seen that documented anywhere- where did the info come from?

- You haven't seen it documented any where because it is simply NOT true. Queries are not updatable for a variety of reasons and seldom is the cause, the "number" of tables. The most frequent issue with multiple table queries not being updatable is that there is more than a single 1-to-many join. An example of this would be students-->parents and students--> teachers. Although parents are related to students and teachers are related to students, parents and teachers are not related to each other so joining those tables does not make any sense. You can create the join with the query builder but the resulting recordset will look like it contains duplicates.

2) Push more data into fewer tables having lots more duplication (and breaking normalization rules)
3) Create temp tables and use a VBA routine to update the corresponding rows.

- neither of these options is acceptable as a solution. You need to gain a better understanding of relational databases and what relationships will result in an updatable join.

Queries are NEVER updatable if:
1. they contain aggregate functions such as Sum()
2. they are union queries
3. they contain a cross join
4. they are a cross tab
5. they contain a group by or distinct clause.

The reason that those query types are never updatable is because there is no way to identify an individual record. Ie, Jet can't take a row from the resulting recordset and identify its specific source row in a table because each row in the recordset is an amalgm of multipls source rows.

 

[stormin_norm]

Pat-
I agree it does not matter how many tables, but how they are joined/related. I tested a three table join and it worked fine for a simple join.

However, you can not avoid the numerous one->many joins in an application. For example, For class=100, find all students and add/change their grades for course=mgt100.

The only way to perform this operation is through the application. Create a cursor over the dataset and perform the add/change via code.
The easiest way would be to create a temp table based on the criteria above, update the temp table with new values, and then have code move data from the temp table to the actual tables.

-stormin.

 

[Pat Hartman]

Of course you could do this update in a single query. Why do you think you would need to make a temp table?

 

[stormin_norm]

Pat, Thanks for your assistance!

If I want to introduce syllabus, then the many-to-many situation happens. Which, technically can be used to filter the course on a parameter form. So this is not big deal. I won't include this table in the sql query.

This query works with three of the tables, no problem updating students with existing records (only 1 student shows up):

SELECT Student.*, Grade.*, Course.*, Course.Course, Student.Class
FROM Student LEFT JOIN (Course RIGHT JOIN Grade ON Course.CourseID = Grade.CourseID) ON Student.StudentID = Grade.StudentID
WHERE (((Course.Course)="MGT100") AND ((Student.Class)="100"));

To enter grades for ALL students of class 100:
I can simply drop the constraint of mgt100, get the students listed, then apply a filter on the form to get unique names.
I've also tried a few sql statements, combining left joins and in (select...),
but only the union will get me all students in class 100 plus those with grades for mgt100.

Then the easiest, but wasteful method is to create null grades for every student in every course, then I will always get a set of grades for all students in class 100.
I don't like this solution because the grade table will grow exponentially.

-norm.

 

[Pat Hartman]

To get all students of Class 100 plus those with grades for MGT100, you will need to use nested queries. You'll need to apply one set of criteria to the first query - Student.Class = "100", then join that query to the Course table with a Left Join and apply the rest of the criteria - Course.Course = "MGT100".

 

[stormin_norm]

okay, not getting very far with trying to debug my join syntax errors. I think I'm close, but no cigar.

SELECT Student.*
From Student Left JOIN
(Select Course.*,Grade.* From Course Join Grade on Course.CourseID=Grade.CourseID Where (Course.Course="MGT100")) on Student.StudentID = Grade.StudentID
WHERE (((Student.Class)="100"));



I did not think you could nest sql this way. I always thought you can only nest sql in the where clause. i.e. "where something in (select.....)"

 

[Pat Hartman]

This may work as a sub query but I haven't tried it. I know it works as a nested query.

query1:
Select ....
Where .....

query2:
Select ..., query1.*
from query1 Left join othertable On query1.field = othertable.field
where .....;