Tables - trouble editing tables

[Mike]

Hi All,

I'm having an issue where I can't edit the tables through
querying. First I created two tables, user, and
information. In the user table, I have a field name "user
ID" and it is a primary key. In the information table, I
have the same field "user Id" and it is also the primary
key. When I create a query to retrieve the datas from both
tables, I can edit the retrieved data from the querying
table. But when I changed the primary key in the
information table to "informationID" and "User ID" is
being as a foriegn key referencing from the user table. I
can not edited the retrieved data from the querying table.

Is there a way that I can edit the retreived datas from
the querying table with userID as a foreign key, instead
of being a primary?

Thanks for the help...
Mike

 

[John Vinson]

Hi All,

I'm having an issue where I can't edit the tables through
querying. First I created two tables, user, and
information. In the user table, I have a field name "user
ID" and it is a primary key. In the information table, I
have the same field "user Id" and it is also the primary
key.

Since a Primary Key is, by definition, unique, this means that there
can be one and only one record for [Information] for a given user. I
suspect that this is not what you want!

When I create a query to retrieve the datas from both
tables, I can edit the retrieved data from the querying
table. But when I changed the primary key in the
information table to "informationID" and "User ID" is
being as a foriegn key referencing from the user table. I
can not edited the retrieved data from the querying table.

Having two fields - an autonumber InformationID and a UserID of the
same datatype as [User].[UserID] (or a Long Integer if UserID is an
autonumber), as a foreign key - is in fact the normal way to do this.

Open the Relationships window and drag the UserID field from User to
Information; check the "Enforce Relational Integrity" checkbox on the
join properities. The Query should now be updateable. BUT SEE BELOW!

Is there a way that I can edit the retreived datas from
the querying table with userID as a foreign key, instead
of being a primary?

As above, but... typically one would want to edit data in a Form, not
in a table or a query datasheet. Create a Form based on User and a
Subform based on Information, using the UserID as the master/child
link field. This will let you edit and enter data in both tables,
maintaining the link correctly, and making it unnecessary to display
any meaningless autonumber values.

 

[Tim Ferguson]

In the user table, I have a field name "user
ID" and it is a primary key. In the information table, I
have the same field "user Id" and it is also the primary
key.

This makes a 1:1 relationship, which is probably unneccessary. It is
usually easier, and appropriate, to keep all the fields in one table.

When I create a query to retrieve the datas from both
tables, I can edit the retrieved data from the querying
table.
Okay.

But when I changed the primary key in the
information table to "informationID" and "User ID" is
being as a foriegn key referencing from the user table. I
can not edited the retrieved data from the querying table.

If I understand this correctly, you have changed the relationship to a
1:many (one user has many informations). This may cause difficulties in the
queries, and you might have to fiddle with which value of UserID to keep in
the query. If you keep the Users.UserID, then you will be able to edit the
user data but not change which user the information belongs to. If you keep
the Information.UserID, you can transfer the information, but not edit it.
You can keep both but you'll need to do some renaming.

For the "best" thing to do, you need to go back and examine what task the
users are trying to complete. Where are they getting the data from and in
what order do they want to put it into the form? Would it be easier to give
them two forms (one to enter Information stuff, and one to update the Users
fields)? Would a simple form/ subform be sufficient?

Hope that helps


Tim F