Why is my qry not updateable

alguzman

Registered User.
Local time
Today, 23:55
Joined
Aug 2, 2001
Messages
63
I have a databse with one main table and other link tables all one- to- many relationships. I made a qry using three tables the main and two of the many side tables. I would like to make some data entry to the qry that will update the main table. When I enter in a new date into the qry it says that recordset is not updateable. why is this. I had to change on table to a one-to-one relationship and I now can update the qry. why one-to-many relationships won't let you update the qry??? Any help would be good. P.S. I did read the help section of Access and tried to look it up using the Knowledge base from Microsoft and couldn't find a clear answer to this....
 
You can't use two 1-to-many relationships in the same query unless they relate to each other. What does the relationship look like:
tbl1 --> tbl2 --> tbl3
rather than
tbl1 --> tbl2
tbl1 --> tbl3

If it looks like the first, the query query "should" be updateable. If it looks like the second, the query will produce a nonsensical recordset and so it should not be updateable.

There are a number of factors that come into play when creating updateable queries that include joins. Some are:
The tables must have unique primary keys.
The linking fields from the tables must be included.

There is an article on this topic available from the Microsoft download center:
http://support.microsoft.com/support/kb/articles/Q148/5/81.asp?FinishURL=%2Fdownloads%2Frelease%2Easp%3FReleaseID%3D13605%26redirect%3Dno
 
Pat Hartman you are right. My tables resemble your second example. I had to make one table within the qry a one-to-one relationship. The tables make up is exhibitorID, address, city, st,zip, the other table is PaymentID (autonumber, Keyfield) exhibitorID, payments and the last table is boothID(autonumber and keyfield) exhibitorID and boothnumbers. I changed the boothnumbers table and took out the autonumber field and left the exhibitorID as a one to one relationship. Is that good database design?? Do you have any suggestions....

Thanks again for your help
 
No! Each table needs its own autonumber primary key.

Your problem is a concept one. The payments table and the booths table each have a relationship with the exhibitor table but THEY HAVE NO RELATIONSHIP TO EACH OTHER. You cannot join these three tables in a single query because they will produce a meaningless cartesian product. Essentially you will end up a recordset comprised of the number of rows in the exhibitor table * the number of rows in the payments table * the number of rows in the booths table.

To present this data in a meaningful way you need to create a main report with two subreports. The main report should be based on a query that draws from the exhibitor table. Sub1 should be based on a query that draws from the payments table and Sub2 should be based on a query that draws from the booths table. You can put the two subreports side-by-side or stacked on top of each other in the detail section of the report. Make sure that the master/child links are properly set and the subreports will stay in sync with the main report.
 

Users who are viewing this thread

Back
Top Bottom