Problem editing data in queries

[inglesp]

I was wondering if anybody could help me with the following... which is related I think to my question here
http://www.thescripts.com/forum/thread780631.html

I have two queries which are based on three tables - q1 is based on t1 and t3, q2 is based on q2 and q3. Each table has the fields {HID, yr, mo} and then some data fields. Each has {HID, yr, mo} as the primary key.

The two queries are structurally identical, with the following SQL:

SELECT t1.HID, t1.yr, t1.mo, t1.somedata1, ..., ..., t3.somedatax
FROM t1 INNER JOIN t3 ON (t1.mo = t3.mo) AND (t1.yr = t3.yr) AND (t1.HID = t3.HID);

The problem is that I can sometimes only open one of the queries for editing, and the other one is locked for editing, but then sometimes, the other one is editable and the other is not. Would anybody be able to tell me (a) why this might be, and (b) how I can work around it?

[DLN]

It has been my experience that Inner Joins will cause this problem.

I don’t know of a work around, except don’t use an Inner Join.

[dbpros]

It may be the joins... Do all your tables have primary keys?

http://www.db-pros.com

[DLN]

I looked in my Access 2000 book and it says
The following properties of a query prevent you from appending and updating records.

The Unique Values property is set to Yes in the query properties window.

Self-Joins are used in the query.

Access SQL aggregate functions, such as Sum(); are employed in the query.
Crosstab queries, for example, use SQL aggregate functions.

No Primary-key fields with a unquie (No Duplicates) Index exist for the one table in a one-to-many relationship.

[inglesp]

Thank you for your ideas...

@dbpros - yes, all the tables have primary keys.

@DLN - none of the reasons your book suggests for not being able to update records match in my case. Unique values is set to no; I don't know what self joins are, but if they're what they sound like, I haven't used them (and you can check the SQL above); there are no aggregate functions in the SQL; each table has a primary key defined.

You suggest avoiding inner joins - how would I do this?


If anybody has any more ideas I'd be very grateful - I'm tearing my hair out at this seemingly pathological behaviour from Access.

[Stewart Ross]

Thank you for your ideas...

@dbpros - yes, all the tables have primary keys.

@DLN - none of the reasons your book suggests for not being able to update records match in my case. Unique values is set to no; I don't know what self joins are, but if they're what they sound like, I haven't used them (and you can check the SQL above); there are no aggregate functions in the SQL; each table has a primary key defined.

You suggest avoiding inner joins - how would I do this?

If anybody has any more ideas I'd be very grateful - I'm tearing my hair out at this seemingly pathological behaviour from Access.

Hi. In my experience Access table joins behave soundly, and the behaviour you describe is not in any way normal or routine. I would be checking two things: firstly, that the relationships between tables is defined appropriately, with referential integrity on and cascade updates set, and secondly, that the key fields from the joined tables in your misbehaving query are included from the one-side of the relationships and not the many-side.

We would need to see the actual SQL (or at least a suitable extract which includes all the joins, and all the key fields) and an extract from the field structure of the underlying tables to give an informed opinion of what could be wrong.

Inner joins - otherwise known as equi-joins - cannot of themselves cause such problems. Without equi-joins SQL as we know it could not function at all and you should not try to avoid them.

The only times I have ever had unexplained behaviour with updatable queries has been when I have not taken the key query fields from the 1-side tables. I have production databases in daily use, both single-user and multi-user, and this just does not arise in a fully-relational table structure with relationships set correctly from the start.

Although Access is not perfect (what is?) and has known bugs it simply does not behave randomly when it comes to table joins.

-Stewart

[DLN]

Hi. In my experience Access table joins behave soundly, and the behaviour you describe is not in any way normal or routine. I would be checking two things: firstly, that the relationships between tables is defined appropriately, with referential integrity on and cascade updates set, and secondly, that the key fields from the joined tables in your misbehaving query are included from the one-side of the relationships and not the many-side.

-Stewart

I got to thinking about this last night and you're right, the problems I had in the past with inner-joins had to do with the one-to-many relationships.