I can't enter data into a query based on two tables in a one to many relationship.

July 16, 2006 1:17 PM

I have two tables in access, linked by a one to many relationship. I've made a query that pulls the information I want from both tables into one place. But now I can't type any data into the query or the form I made based on the query. Why not?

In other queries I've made (based on un-linked/relationshiped tables) I've been able to enter data into the query or a form based on a query no problem. The data was then updated back in the original table.

But that's not happening this time and I'm baffled as to why.

The whole point of this was to not enter data more than once, but if I can't enter the data into the query/form that's pulling information from the other tables, what's the point?

Am I missing something?

Any help appreciated as now I'm obsessing over this instead of doing actual work. Thanks!

posted by misanthropicsarah to computers & internet (5 comments total)

i'm guessing it's the one to many relationship that is preventing this. access doesn't know which record to update.
posted by lester at 2:14 PM on July 16, 2006

subforms are your friend.

From help:

About subforms
A subform is a form that is inserted in another form. The primary form is called the main form, and the form within the form is called the subform. A form/subform combination is often referred to as a hierarchical form, a master/detail form, or a parent/child form.

Subforms are especially effective when you want to show data from tables or queries with a one-to-many relationship. For example, you could create a form with a subform to show data from a Categories table and a Products table. The data in the Categories table is the "one" side of the relationship. The data in the Products table is the "many" side of the relationship — each category can have more than one product.

The main form shows data from the "one" side of the relationship.
The subform shows data from the "many" side of the relationship.
posted by hmca at 3:12 PM on July 16, 2006

The is the classic header - detail situation. Display a list of entries from the "header" table. Pick an entry. Pull up a list of entries from the "detail". Update the detail.
posted by SPrintF at 3:57 PM on July 16, 2006

There are several possible reasons for this - hmca is correct in pointing out that in some cases the one-to-many relationship makes the query recordset non-updatable, because Access wouldn't know where to put the new data on the "many" side.

Not knowing all that much about what you actually want to do, it sounds like using subforms would be helpful, as they allow you to segregate the one and the many sides.
posted by jasper411 at 4:52 PM on July 16, 2006

I'm a little confused as to why you would want to enter data into a query in the first place (are you using Select Query for this?). I think a better way to do it would be to enter the data into a new temp table and then use an Append Query to transfer the data into the respective tables in which you want them, which then circumvents the one-to-many issue.
posted by The Michael The at 5:36 AM on July 17, 2006