Cannot use update query to update records in another query

[Guest]

I need to update a field in one table with data from another table. I have
joined the two tables in a query, but when I try to update the field using an
update query or using VBA, I get an error saying that the query is not
updatable. I have checked several other queries that join multiple tables
and none of them will allow me to update any of the fields. Any thoughts?

Thanks in advance for your assistance,

Dan

 

[Joseph Meehan]

I need to update a field in one table with data from another table.
I have joined the two tables in a query, but when I try to update the
field using an update query or using VBA, I get an error saying that
the query is not updatable. I have checked several other queries
that join multiple tables and none of them will allow me to update
any of the fields. Any thoughts?

Thanks in advance for your assistance,

Dan

Chances are the update being requested in ambiguous. That is the
update, due to the relationships is not clear to Access what record(s) need
to be updated.

 

[John W. Vinson]

I need to update a field in one table with data from another table. I have
joined the two tables in a query, but when I try to update the field using an
update query or using VBA, I get an error saying that the query is not
updatable. I have checked several other queries that join multiple tables
and none of them will allow me to update any of the fields. Any thoughts?

Thanks in advance for your assistance,

Dan

Please post the SQL view of the query. Also indicate which field or fields are
the Primary Key of each table, and whether a relationship has been defined
between the tables (and on which fields).

John W. Vinson [MVP]

 

[Guest]

John,

Joseph is probably right but I don't know how to fix the ambiguity. Below
is the SQL view of the query I am try to update. I have to join the tables on
3 fields in order to make sure the right data gets to the right place. When
I run qryTest which combines all the data, the records are all joined
properly. The tables have a relationship that identifies the 3 fields that
are needed to properly join the data.

FYI: The purpose of all this is to create a budget. I have one table with
all the accounts from our accounting system and for each account there are 12
periods (1 for each month). I have another table with water consumption data
that I use for forcasting and calculating next year's revenue. I need to get
the revenue information into the account table.

Thanks for all the help.

Dan

SELECT [Copy1 Of tblActualBudgetHistory].ACTNUMBR_1, [Copy1 Of
tblActualBudgetHistory].ACTNUMBR_2, [Copy1 Of
tblActualBudgetHistory].PERIODID, [Copy1 Of tblActualBudgetHistory].ACTDESCR,
[Copy1 Of tblActualBudgetHistory].RequestedBudget,
qryRevenueByMonthByRevenueType.SumOfRevenue
FROM [Copy1 Of tblActualBudgetHistory] RIGHT JOIN
qryRevenueByMonthByRevenueType ON ([Copy1 Of
tblActualBudgetHistory].ACTNUMBR_1 =
qryRevenueByMonthByRevenueType.ACTNUMBR_1) AND ([Copy1 Of
tblActualBudgetHistory].ACTNUMBR_2 =
qryRevenueByMonthByRevenueType.ACTNUMBR_2) AND ([Copy1 Of
tblActualBudgetHistory].PERIODID = qryRevenueByMonthByRevenueType.PERIODID);

 

[John W. Vinson]

SELECT [Copy1 Of tblActualBudgetHistory].ACTNUMBR_1, [Copy1 Of
tblActualBudgetHistory].ACTNUMBR_2, [Copy1 Of
tblActualBudgetHistory].PERIODID, [Copy1 Of tblActualBudgetHistory].ACTDESCR,
[Copy1 Of tblActualBudgetHistory].RequestedBudget,
qryRevenueByMonthByRevenueType.SumOfRevenue
FROM [Copy1 Of tblActualBudgetHistory] RIGHT JOIN
qryRevenueByMonthByRevenueType ON ([Copy1 Of
tblActualBudgetHistory].ACTNUMBR_1 =
qryRevenueByMonthByRevenueType.ACTNUMBR_1) AND ([Copy1 Of
tblActualBudgetHistory].ACTNUMBR_2 =
qryRevenueByMonthByRevenueType.ACTNUMBR_2) AND ([Copy1 Of
tblActualBudgetHistory].PERIODID = qryRevenueByMonthByRevenueType.PERIODID);

The SumOfRevenue is the big red flag here. It appears that
qryRevenueByMonthByRevenueType is a Totals query; and no Totals query - nor
any query based on a Totals query - will ever be updateable.

What field or fields are you trying to update?

John W. Vinson [MVP]

 

[Guest]

John,

You are correct. This a totals query. I am trying to update the field
[RequestedBudget]. I have created a table with this same data and linked the
two tables directly and I still have the same problem. I think I may have a
solution. I am going to add a unique identifier to
qryRevenueByMonthByRevenueType that matches the Primary key in the table and
then create a table as I did before and then try to update the tables. At
least this way I eliminate the ambiguous part of the problem.

Again, thanks for your help.

Dan

SELECT [Copy1 Of tblActualBudgetHistory].ACTNUMBR_1, [Copy1 Of
tblActualBudgetHistory].ACTNUMBR_2, [Copy1 Of
tblActualBudgetHistory].PERIODID, [Copy1 Of tblActualBudgetHistory].ACTDESCR,
[Copy1 Of tblActualBudgetHistory].RequestedBudget,
qryRevenueByMonthByRevenueType.SumOfRevenue
FROM [Copy1 Of tblActualBudgetHistory] RIGHT JOIN
qryRevenueByMonthByRevenueType ON ([Copy1 Of
tblActualBudgetHistory].ACTNUMBR_1 =
qryRevenueByMonthByRevenueType.ACTNUMBR_1) AND ([Copy1 Of
tblActualBudgetHistory].ACTNUMBR_2 =
qryRevenueByMonthByRevenueType.ACTNUMBR_2) AND ([Copy1 Of
tblActualBudgetHistory].PERIODID = qryRevenueByMonthByRevenueType.PERIODID);

The SumOfRevenue is the big red flag here. It appears that
qryRevenueByMonthByRevenueType is a Totals query; and no Totals query - nor
any query based on a Totals query - will ever be updateable.

What field or fields are you trying to update?

John W. Vinson [MVP]

 

[John W. Vinson]

You are correct. This a totals query. I am trying to update the field
[RequestedBudget]. I have created a table with this same data and linked the
two tables directly and I still have the same problem. I think I may have a
solution. I am going to add a unique identifier to
qryRevenueByMonthByRevenueType that matches the Primary key in the table and
then create a table as I did before and then try to update the tables. At
least this way I eliminate the ambiguous part of the problem.

That's one way; another is to use the DSum() builtin function - rather than a
totals query - to calculate the sum.

Ideally, you should NOT be storing a sum or any other calculated value in any
table field anyway. Sometimes it's necessary but you should ONLY do so when
there's no other good way to do the job. Here's my blurb on the subject:

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson [MVP]