Table joins

  • Thread starter Thread starter Warrio
  • Start date Start date
W

Warrio

Hello!!

When I create a query on multiple tables, it becomes sometime impossible to
edit the result of the query.

I know that it has to deal with the index of the table that are joined, they
have to be both set to the same way or stuff like that..

How does it work? What Should I take care of in order to be always able to
edit my queries

Thanks for any suggestion
 
Often times when you join fields together, one of the fields should be
the primary key of one of the tables. If one or more of the tables you
are using either does not have a primary key, or you are not using the
primary key in the join, then in a lot of cases you cannot edit the
data. Another thing, if you are using outer joins, then that could be
a cause to not being able to update the data.

Hope that helps!
 
Thanks Jeff for your answer!

I'm in the case where I cannot include all the primary keys into my join
statement in one table, its PK can be unique whereas in the other table it
can't be unique.
But, only for my own curiosity and to understand better Access, what makes
Access unable the updatability of the query?

and I have another question, there is really not a way to have un updatable
query for such a structure?

Thanks again!!
 
In Access Help type the following in the Answer Wizard tab
When can I update data from a query
Select that from the list for an explanation of some of the causes and
alternative solutions.

Access MVP Allen Browne has summarized **many** of the reasons:

Query results will be read-only if any of the following apply:
.. It has a GROUP BY clause (totals query).
.. It has a TRANSFORM clause (crosstab query).
.. It contains a DISTINCT predicate.
.. It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause
(performs aggregation).
.. It involves a UNION.
.. It has a subquery in the SELECT clause.
.. It uses JOINs of different directions on multiple tables in the FROM
clause.
.. The query's Recordset Type property is set to Snapshot.
.. The query is based on another query that is read-only (stacked query.)
.. Your permissions are read-only (Access security.)
.. The database is opened read-only, or the file attributes are read-only, or
the database is on read-only media (e.g. CD-ROM, network drive without write
privileges.)
 
Hi Jhon,

Thanks again for all the answers and time you're spending on this! it is
really appreciated.

Among all the reason you gave below, my query fits in non of these
conditions!
my query looks like:

SELECT Table1.Field1 FROM
(Table1 INNER JOIN Table2 ON Table1.Field1=Table2.Field2) INNER JOIN Table3
ON Table2.Field2=Table3.Field3

and each of the data are tables not loked and nothing special in them,
except about the primary keys:
Table1 has 2 primary keys: Field1 and Field1b
and Table3 has also 2 primary keys: Field3 and Field3b

but when I try to add to the join clause a fake conection between Table2 and
Table3 :
SELECT Table1.Field1 FROM
(Table1 INNER JOIN Table2 ON Table1.Field1=Table2.Field2) INNER JOIN Table3
ON Table2.Field2=Table3.Field3 AND Table2.Field2=Table3.Field3b

The query becomes updatable!!! how come? I don't know

Sorry if I made complicated, but that the simplest way I found
 
Back
Top