Changing data from source tables using queries?

[park.walter]

Hello -

I have been having a frustrating time with what I think must have a
simple solution:

If I have an Access database with 2 tables, run a simple query and
then output all the information from both tables why is it that
sometimes I can manually type in new data whilst viewing the query
output and other times Access just beeps at me and does not allow new
data to be manually entered into the resulting query fields? Is this
because primary keys are not defined or because relationships haven't
been assigned?

wdp

 

[John W. Vinson]

Hello -

I have been having a frustrating time with what I think must have a
simple solution:

If I have an Access database with 2 tables, run a simple query and
then output all the information from both tables why is it that
sometimes I can manually type in new data whilst viewing the query
output and other times Access just beeps at me and does not allow new
data to be manually entered into the resulting query fields? Is this
because primary keys are not defined or because relationships haven't
been assigned?

wdp

Search the Help file for "Updateable". There are many reasons why queries may
not allow updating. The absence of a primary key, as you correctly guess, is a
common one.

John W. Vinson [MVP]

 

[Allen Browne]

It is most likely because there is not a unique index on one side of the
relationship.

For more detail, see:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html

 

[park.walter]

Thanks for the feedback!

Typically none of my tables have primary keys defined because the data
comes from multiple sources that I can't control. However, if I
modify my tables and set a primary key I can link the two tables
together in a query (the link MUST be to a primary key in one table)
and can make the changes within the query output.

This is helpful, however I am unable to create more than one link
between the tables, only one - to a primary key - is allowed if I want
to make changes to a table from within the query output.

Does this make sense? I am surprised that I can't link more than one
field between the tables?

wdp

 

[Allen Browne]

You can join tables on mulitple fields if you wish.

But the results will not be updatable unless you have a unique index on the
combination of fields on the "one" side of the join.

 

[park.walter]

But the results will not be updatable unless you have a unique index on the
combination of fields on the "one" side of the join.

Allen-

Can you clarify what you mean by this (I apologize):

For example:
Table 1:
Customer (primary)
Product
Supplier

Table 2 (no primary key):
Customer
Product
Supplier

If I run a query of these tables and link by Customer it is
updateable. If I link by Customer and Product it is not (only tried
when "link" is = in both tables, not all in one or other). Where does
the "unique index on the combination of fields" come into play?

wdp

 

[Allen Browne]

To create a unique index on the combination of your Customer + Product
fields:

1. Open table 1 in design view.

2. Open the Indexes box (Tools menu.)

3. On a fresh line in the dialog, enter an index name and the first field,
and set the Unique property (lower pane) to Yes.

4. On the next line of the dialog, leave the index name blank, and choose
the 2nd field.

 

[John W. Vinson]

I am surprised that I can't link more than one
field between the tables?

I am too, since Access allows you to use up to ten fields to link tables.

What is preventing you from doing so?

John W. Vinson [MVP]

 

[park.walter]

I am too, since Access allows you to use up to ten fields to link tables.

What is preventing you from doing so?

John W. Vinson [MVP]

John-

My apologies for the confusion. I can set up multiple links between
two tables and run queries. The issue we're discussing arises if I
want to update the data from within the results of a query.

Using default table settings (which the indexing solution that Allen
mentioned above is not) if I use multiple links, then it's not
updateable. This is more than slightly inconvenient because while the
indexing solution that Allen provided is helpful, it appears as though
I'd have to set it for each table?

wdp