multiple table problems

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello and thanks for the help. I am using Access XP. I have three tables related to one another. When I use all three tables in a query I am NOT able to type/change/update in any way in any of the fields.

Here is the structure:

Student Table: SSN = Primary Key. Related to the Contacts table with the SSN field. It is a one-to-one relationship.

Contacts Table: SSN = Primary Key. Related to the Infractions table with the SSN field. It is a one-to-many relationsip.

Infractions Table: No Primary Key. But even when it had a primary key the problem still existed.

I put all three tables in the query and asked for a field from each of the tables with the SSN being pulled from the Student Table. I had the proper results for the dynaset but when I tried to update some info I was unable to type. No messages, typing was just not an option. The new records button was not available either. How can I make this work?

THANKS !
 
Sheri,

You have created a query that is not updateable. This is not necessarily a
bad thing; queries representing data in one-to-many relationships are often
not updateable.

What I think you should do is:

1. Make updates to your data only in forms.

2. Create a Main form with your Student data presented in single form
format.

3. Create a subform (in continuous form format) for each of your other
tables - Contacts and Infractions.

4. Add each of the subforms to your Main form using the Wizard and link the
Main Form to each subform using your Primary Keys.

hth,

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Sheri said:
Hello and thanks for the help. I am using Access XP. I have three tables
Click to expand...
related to one another. When I use all three tables in a query I am NOT able
to type/change/update in any way in any of the fields.
Here is the structure:

Student Table: SSN = Primary Key. Related to the Contacts table with the
Click to expand...
SSN field. It is a one-to-one relationship.
Contacts Table: SSN = Primary Key. Related to the Infractions table with
Click to expand...
the SSN field. It is a one-to-many relationsip.
Infractions Table: No Primary Key. But even when it had a primary key the problem still existed.

I put all three tables in the query and asked for a field from each of the
Click to expand...
tables with the SSN being pulled from the Student Table. I had the proper
results for the dynaset but when I tried to update some info I was unable to
type. No messages, typing was just not an option. The new records button was
not available either. How can I make this work?
 
Cheryl - can you elaborate on what makes the query not updateable? I have a
similar problem, and for various reasons it will save a lot of work if I can
get my query to be updateable.

Thanks!
 
Never mind - I found it in the help system...


Steve said:
Cheryl - can you elaborate on what makes the query not updateable? I have a
similar problem, and for various reasons it will save a lot of work if I can
get my query to be updateable.

Thanks!


necessarily unable
Click to expand...
 
Back
Top