Update Query Across 3 tables

[SeeWard]

Hello..
I am trying to fix an error in the database which spans three tables. I can get at the data I want but can't seem to change the recordset when the query is run. What I hope to accomplish is to change one field based on a conditon. If FieldA = -1 Then FieldB = 0 ..but I don't know how to do this...Do you???
TIA

 

[dhookom]

A database doesn't really "span" three tables. Why do you need to include 3 tables when only one is being updated? If you have a query that you want to update, the query must be updateable.

Can you provide additional information about what you have and what you want to do?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[SeeWard]

The error spans across three tables. I have a query which uses the 3 tables ...it has client_id as it's primary key. What I hope to accomplish is to write a query or function that will reset the values in fields in the tables. If one field (Advocacy) = -1 I need it to be set to 0 IF the field (IANDR) is equal to 0 in a different table. I didn't design this particular database. The person who designed it created three tables...one is the Main Client Table...the other tables are for IANDR and Advocacy...It's seems rather odd to do it that way but it is what I've inherrited..I can only have IANDR True if Advocacy is false and vice versa. There is an erro in the data...I need to change some of these entries. I was hoping to automate it via a query or function rather than having to do it manually by hand. I have created the query and I can see the results but cannot change any of the data manually. I was thinking that was due to the fact that the tables are linked. Clear as mud to me.

 

[dhookom]

About the only way that a query is editable is if the joins are between primary and foreign keys.

You haven't told us anything about your table structures or the SQL view of your query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[SeeWard]

Here is the SQL:
SELECT [Main Intake].[First Name], [Main Intake].[Last Name], [Main Intake].[Street Address], [Client Transactions].Advocacy, [Client Transactions].firstname, [Client Transactions].lastname, AdvocacyOptions.Call, [Client Transactions].transactiondate, [Client Transactions].IandR
FROM (AdvocacyOptions INNER JOIN [Client Transactions] ON (AdvocacyOptions.[Client id] = [Client Transactions].ID) AND (AdvocacyOptions.TransactionNo = [Client Transactions].TransactionNo)) INNER JOIN [Main Intake] ON AdvocacyOptions.[Client id] = [Main Intake].[client id]
WHERE ((([Client Transactions].Advocacy)=Yes) AND ((AdvocacyOptions.Call) Like -1);

TABLE ADVOCACY:
Clinet id (Number)
Call Yes/No
Visit Yes/No
Employment Yes/No
Education Yes/No
...
Transactiondate Date/Time

OH! Yes, You're right. Thank you!
I don't work with any other IT people so sometimes, it just helps to put things down..I see the error!!!! Thanks you!

 

[dhookom]

Does this query allow editing/appending?
Is the primary key of [Main Intake] the [Client ID] field?
Is the primary key of [AdvocacyOptions] the [Client ID] and [TransactionNo] fields?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SeeWard]

No it doesn't allow editing. The primary key is client id. Each table has Client Id as primary key...the transaction no can only be found in Advocacy and Client Transactions. The problem occured because I was thinking that I absolutely had to have the Advocacy table involved when in fact I do not because the Client Transaction table has a field that records the same data. So that being said I can use stictly the Main Intake and Client Transaction tables...Crazy I know..but I only inherrited this and didn't create it...