Read Only effect in Query...

[JaedenRuiner]

Hello,
I'm having a little problem with an Access Query. Simple table design, with relationships as normal, using a junction table for a many-to-many link.

Table1

Field: Area (Text)
Field: T1_ID (AutoNumber)​

Table2

Field: T2_ID (AutoNumber)
Field: Value (Double)​

Table3Join

Field: Area_ID (Long Integer) *Rel: Table1.T1_ID
Field: Value_ID (LongInteger) *Rel: Table2.T2_ID​

Table4

Field: T4_ID (AutoNumber)
Field: Area (Number) *Rel: Table1.T1_ID
Field: Description (Text)​

Query1
Sources: Table4, Table3Join, Table2, Table1
Fields: Description, Area, Value

SQL:

SELECT Table4.Description,Table4.Area,Table2.Value
FROM Table2 INNER JOIN ((Table1 INNER JOIN Table4 ON Table1.T1_ID = Table4.Area_ID) INNER JOIN Table3Join ON Table1.T1_ID = Table3Join.Area_ID) ON Table2.T2_ID = Table3Join.Value_ID;

However, with this design as it is, I cannot "edit" the Description field, or change the Area field. The entire Query becomes read only. Why is this? And how should i re-write this to allow me to use the relationships which Link Table2.Value to the appropriate Table4.Area (and display them both in one query), while allowing me to edit all the sub fields.

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

Using: Access XP 2002 (10.4302.4219) SP-2

 

[RV]

Why using 2 tables Table1 and Table4?
Doesn't make sense, in fact, your design now is redundant.
You only need Table1

T1_ID

Use a more meaningfull column name.

RV

 

[JaedenRuiner]

Please don't think about the design...

The design isn't redundant. In access, it is necessary to do 3 tables for a many to many relationship.

Table 1 is the list of Areas.
Table 2 is the List of Values

Table3Join is the Junction table linking all the possible areas to all the possible Values.

I can have "Area 1" at "95", "Area 2" at "90", "Area 3" at "95", and "Area 4" at "85". Thus Table 1 records are "Area 1", "Area 2", "Area 3", "Area 4". Table 2 is "95", "90", "85". Table3Join has two fields that are linked together as the unique Primary Key together. This is a Junction table.
Table 4 is the actual data i wish to store, and it contains a Field that determines the Area for each record in Table 4. Each Record also has a value associated with that Area. So it would be "redundant" to have a Value field in Table 4. The Design is perfect, save for the fact I can't "edit" a query that displays both the information in Table 4 (which includes the Area field) and the Value field from Table 2.

This doesn't make sense why it would prevent me from editing just because of the INNER JOINS. When I remove them from the query it edits fine, but the Value information is no longer displayed.

Thanks

Jaeden "Sifo Dyas" al'Raec Ruiner

 

[ThreeCrow]

you'll need to create 3 queries

first one: one side of your junction
second one: other of your junction
three one: combine one and two

The third is the one that you will use