query and cascading updates?

[Geoff Cox]

Hello

'having successfully created a query with joins between 2 fields in 2
tables I now find that I cannot edit one of the fields when I run the
query.

I believe I need to set cascading updates?

Cannot see how to do this? Help please!

Cheers

Geoff

 

[Allen Browne]

Do you have a one-to-many relationship between your 2 tables (Tools |
Relationships)?

If so, there should be a unique index on the join field from the "one" side
of the relationship. If you don't have this unique index, it will not be
editable.

If that's not the issue, see:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html

 

[Geoff Cox]

Do you have a one-to-many relationship between your 2 tables (Tools |
Relationships)?

If so, there should be a unique index on the join field from the "one" side
of the relationship. If you don't have this unique index, it will not be
editable.

If that's not the issue, see:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html

Allen,

I'm not at all clear re what type of join etc but does the SQL view
help you to tell me?

Cheers

Geoff

SELECT members.ID, members.first_name, members.second_name,
members.centre, members.address_1, members.address_2, members.town,
members.county, members.post_code, members.email, members.user_name,
members.password, members.start1, members.start2, members.start3,
members.start4, members.date_ended, members.type, members.user,
[members-costs].cost
FROM members INNER JOIN [members-costs] ON (members.type =
[members-costs].type) AND (members.user = [members-costs].user)
WHERE (((members.start1) Between #11/1/2006# And #11/30/2006#) AND
((members.date_ended) Is Null)) OR (((members.start2) Between
#11/1/2006# And #11/30/2006#)) OR (((members.start3) Between
#11/1/2006# And #11/30/2006#)) OR (((members.start4) Between
#11/1/2006# And #11/30/2006#));

 

[Allen Browne]

So you have 2 tables:
members
[members-costs]
and both fields have a Type field.

If Type is the primary key of the Members table, you will be able to edit
the fields from both tables in your query. If Members.Type is not uniquely
indexed, you will not be able to edit the fields from both tables in your
query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Do you have a one-to-many relationship between your 2 tables (Tools |
Relationships)?

If so, there should be a unique index on the join field from the "one"
side
of the relationship. If you don't have this unique index, it will not be
editable.

If that's not the issue, see:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html

Allen,

I'm not at all clear re what type of join etc but does the SQL view
help you to tell me?

Cheers

Geoff

SELECT members.ID, members.first_name, members.second_name,
members.centre, members.address_1, members.address_2, members.town,
members.county, members.post_code, members.email, members.user_name,
members.password, members.start1, members.start2, members.start3,
members.start4, members.date_ended, members.type, members.user,
[members-costs].cost
FROM members INNER JOIN [members-costs] ON (members.type =
[members-costs].type) AND (members.user = [members-costs].user)
WHERE (((members.start1) Between #11/1/2006# And #11/30/2006#) AND
((members.date_ended) Is Null)) OR (((members.start2) Between
#11/1/2006# And #11/30/2006#)) OR (((members.start3) Between
#11/1/2006# And #11/30/2006#)) OR (((members.start4) Between
#11/1/2006# And #11/30/2006#));

 

[Geoff Cox]

So you have 2 tables:
members
[members-costs]
and both fields have a Type field.

If Type is the primary key of the Members table, you will be able to edit
the fields from both tables in your query. If Members.Type is not uniquely
indexed, you will not be able to edit the fields from both tables in your
query.

Allen

In fact there are 2 fields whch are joined, type and user but I wish
to add data to another field, the date_ended field.

I cannot have either the type or the user field as a primary key in
the members table as neither contain unique values.

So just not possible to edit the date_ended field?!

Cheers

Geoff

 

[Allen Browne]

No. You need to find a different approach, such as using a main form for
Members, and a subform for members-costs.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

So you have 2 tables:
members
[members-costs]
and both fields have a Type field.

If Type is the primary key of the Members table, you will be able to edit
the fields from both tables in your query. If Members.Type is not uniquely
indexed, you will not be able to edit the fields from both tables in your
query.

Allen

In fact there are 2 fields whch are joined, type and user but I wish
to add data to another field, the date_ended field.

I cannot have either the type or the user field as a primary key in
the members table as neither contain unique values.

So just not possible to edit the date_ended field?!

Cheers

Geoff

 

[Geoff Cox]

No. You need to find a different approach, such as using a main form for
Members, and a subform for members-costs.

OK - will look into that. For the moment I have gone back to just 1
table in the query and

RenewalCost:
IIF([type]="1118",Switch([user]="a",100,[user]="b",400,[user]="c",60,[user]="d",130),
IIF([type]="1116",Switch([user]="a",85,[user]="b",400,[user]="c",50,[user]="d",100),
IIF([type]="1618",
Switch([user]="a",85,[user]="b",400,[user]="c",50,[user]="d",100),
IIF([type]="1118plus",Switch([user]="a",100,[user]="b",400,[user]="c",60,[user]="d",30),
IIF([type]="1116plus",Switch([user]="a",85,[user]="b",400,[user]="c",50,[user]="d",100),0)))))

Cheers

Geoff