Updating table based on results of query

[Guest]

Hi Everyone,

I am having a problem with a project I am working on. I need to update a
table with the results of a query, but I am unsuccessful when I try to do
this. Below is some suggesstions from a prior post that I made. Can anyone
help me out with this?

*****************
Thanks for your help Rick.

I have created the transaction total query. Here is the SQL:
SELECT DISTINCTROW Transaction.Cust_ID, Sum(Transaction.Tran_Amount) AS [Sum
Of Tran_Amount]
FROM [Transaction]
GROUP BY Transaction.Cust_ID;

I created the update balance query with the following SQL:
UPDATE Customer INNER JOIN qrytransactiontotal ON
Customer.Cust_ID=qrytransactiontotal.Cust_ID SET Customer.Acct_Balance =
qrytransactiontotal.[Sum Of Tran_Amount];

When I try to run the update query I get a message stating that "operation
must use an updateable query".
What am I doing wrong?

 

[Duane Hookom]

I believe what you are doing wrong is attempting to store a value that can
be derived from other records.

Since your first query is a totals query, when you include it in another
query, this causes the error.

If you think you need to do this then try:

UPDATE Customer
SET Customer.Acct_Balance =
DLookup("[Sum Of Tran_Amount], "qrytransactiontotal", "Cust_ID = " &
Cust_ID);

This assumes Cust_ID is numeric.

--
Duane Hookom
MS Access MVP

Hi Everyone,

I am having a problem with a project I am working on. I need to update a
table with the results of a query, but I am unsuccessful when I try to do
this. Below is some suggesstions from a prior post that I made. Can anyone
help me out with this?

*****************
Thanks for your help Rick.

I have created the transaction total query. Here is the SQL:
SELECT DISTINCTROW Transaction.Cust_ID, Sum(Transaction.Tran_Amount) AS
[Sum
Of Tran_Amount]
FROM [Transaction]
GROUP BY Transaction.Cust_ID;

I created the update balance query with the following SQL:
UPDATE Customer INNER JOIN qrytransactiontotal ON
Customer.Cust_ID=qrytransactiontotal.Cust_ID SET Customer.Acct_Balance =
qrytransactiontotal.[Sum Of Tran_Amount];

When I try to run the update query I get a message stating that "operation
must use an updateable query".
What am I doing wrong?

 

[Guest]

Hi, Duane.

I tried using the query that you posted. I am first prompted to enter a
value for Sum of Tran_Amount. I just click on OK and I get a message stating
that I am about to update my rows. When I click yes I get the following
message, "Microsoft Office Acecss didn't update 10 field(s) due to a type
conversion failure., 0 record(s) due to key violations ,0 record(s) due to
lock violations amd 0 record(s) due to validation ruue violations. Do you
want to continue running this type of action query anyway?"


Perhaps I am going about this all wrong. Is there a better way for me to sum
the values in the transaction table and update the Customer's balance in
Customer?

-Nicole

I believe what you are doing wrong is attempting to store a value that can
be derived from other records.

Since your first query is a totals query, when you include it in another
query, this causes the error.

If you think you need to do this then try:

UPDATE Customer
SET Customer.Acct_Balance =
DLookup("[Sum Of Tran_Amount], "qrytransactiontotal", "Cust_ID = " &
Cust_ID);

This assumes Cust_ID is numeric.

--
Duane Hookom
MS Access MVP

Hi Everyone,

I am having a problem with a project I am working on. I need to update a
table with the results of a query, but I am unsuccessful when I try to do
this. Below is some suggesstions from a prior post that I made. Can anyone
help me out with this?

*****************
Thanks for your help Rick.

I have created the transaction total query. Here is the SQL:
SELECT DISTINCTROW Transaction.Cust_ID, Sum(Transaction.Tran_Amount) AS
[Sum
Of Tran_Amount]
FROM [Transaction]
GROUP BY Transaction.Cust_ID;

I created the update balance query with the following SQL:
UPDATE Customer INNER JOIN qrytransactiontotal ON
Customer.Cust_ID=qrytransactiontotal.Cust_ID SET Customer.Acct_Balance =
qrytransactiontotal.[Sum Of Tran_Amount];

When I try to run the update query I get a message stating that "operation
must use an updateable query".
What am I doing wrong?

Here are some previous links that explain the process. You may need to
copy
and paste thie links. Make sure the whole link is on one like. I got
these
from Google Groups...


http://groups-beta.google.com/group...stomer+balance&rnum=22&hl=en#dbcf11f4d5ddabf1


http://groups-beta.google.com/group...stomer+balance&rnum=36&hl=en#0ce650f75c7544bf

http://groups-beta.google.com/group...s+calculate+customer+balance&start=30&hl=en&;



In short, you will have to create a query that sums all the transactions
for
each given client and returns the client number and the sum.

--
Rick B



Ok, how do I do what you suggest?

By the way - this is a very small (10 records) database that I have to
complete for a class.

:

You don't.

As posted alllll the time, you don't store calculated values. You
would
spend all your time trying to keep this number accurate. Everytime a
new
invoice was added, the number would be wrong.

Instead, you would build a query to calculate the clients' balances
and
then
use that.

--
Rick B



Hi I have a scenario that I need help with:

I have two tables, Transaction and Customer. The transaction table
contains
a field called Amount. A customer can have more than one
transaction.

The Customer table has a field called Balance. I would like for the
balance
to be the sum of all of the customer's transaction amounts.

How do I have the Balance field in the customer table reflect the
total of
the customer's transaction amounts?

 

[Duane Hookom]

I missed a quote. Try this:
UPDATE Customer
SET Customer.Acct_Balance =
DLookup("[Sum Of Tran_Amount]", "qrytransactiontotal", "Cust_ID = " &
Cust_ID);


--
Duane Hookom
MS Access MVP

Hi, Duane.

I tried using the query that you posted. I am first prompted to enter a
value for Sum of Tran_Amount. I just click on OK and I get a message
stating
that I am about to update my rows. When I click yes I get the following
message, "Microsoft Office Acecss didn't update 10 field(s) due to a type
conversion failure., 0 record(s) due to key violations ,0 record(s) due to
lock violations amd 0 record(s) due to validation ruue violations. Do you
want to continue running this type of action query anyway?"


Perhaps I am going about this all wrong. Is there a better way for me to
sum
the values in the transaction table and update the Customer's balance in
Customer?

-Nicole

I believe what you are doing wrong is attempting to store a value that
can
be derived from other records.

Since your first query is a totals query, when you include it in another
query, this causes the error.

If you think you need to do this then try:

UPDATE Customer
SET Customer.Acct_Balance =
DLookup("[Sum Of Tran_Amount], "qrytransactiontotal", "Cust_ID = " &
Cust_ID);

This assumes Cust_ID is numeric.

--
Duane Hookom
MS Access MVP

Hi Everyone,

I am having a problem with a project I am working on. I need to update
a
table with the results of a query, but I am unsuccessful when I try to
do
this. Below is some suggesstions from a prior post that I made. Can
anyone
help me out with this?

*****************
Thanks for your help Rick.

I have created the transaction total query. Here is the SQL:
SELECT DISTINCTROW Transaction.Cust_ID, Sum(Transaction.Tran_Amount) AS
[Sum
Of Tran_Amount]
FROM [Transaction]
GROUP BY Transaction.Cust_ID;

I created the update balance query with the following SQL:
UPDATE Customer INNER JOIN qrytransactiontotal ON
Customer.Cust_ID=qrytransactiontotal.Cust_ID SET Customer.Acct_Balance
=
qrytransactiontotal.[Sum Of Tran_Amount];

When I try to run the update query I get a message stating that
"operation
must use an updateable query".
What am I doing wrong?

:

Here are some previous links that explain the process. You may need
to
copy
and paste thie links. Make sure the whole link is on one like. I got
these
from Google Groups...


http://groups-beta.google.com/group...stomer+balance&rnum=22&hl=en#dbcf11f4d5ddabf1


http://groups-beta.google.com/group...stomer+balance&rnum=36&hl=en#0ce650f75c7544bf

http://groups-beta.google.com/group...s+calculate+customer+balance&start=30&hl=en&;



In short, you will have to create a query that sums all the
transactions
for
each given client and returns the client number and the sum.

--
Rick B



Ok, how do I do what you suggest?

By the way - this is a very small (10 records) database that I have
to
complete for a class.

:

You don't.

As posted alllll the time, you don't store calculated values. You
would
spend all your time trying to keep this number accurate.
Everytime a
new
invoice was added, the number would be wrong.

Instead, you would build a query to calculate the clients'
balances
and
then
use that.

--
Rick B



Hi I have a scenario that I need help with:

I have two tables, Transaction and Customer. The transaction
table
contains
a field called Amount. A customer can have more than one
transaction.

The Customer table has a field called Balance. I would like for
the
balance
to be the sum of all of the customer's transaction amounts.

How do I have the Balance field in the customer table reflect
the
total of
the customer's transaction amounts?

 

[Guest]

I am still getting the same messages... :-(

I missed a quote. Try this:
UPDATE Customer
SET Customer.Acct_Balance =
DLookup("[Sum Of Tran_Amount]", "qrytransactiontotal", "Cust_ID = " &
Cust_ID);


--
Duane Hookom
MS Access MVP

Hi, Duane.

I tried using the query that you posted. I am first prompted to enter a
value for Sum of Tran_Amount. I just click on OK and I get a message
stating
that I am about to update my rows. When I click yes I get the following
message, "Microsoft Office Acecss didn't update 10 field(s) due to a type
conversion failure., 0 record(s) due to key violations ,0 record(s) due to
lock violations amd 0 record(s) due to validation ruue violations. Do you
want to continue running this type of action query anyway?"


Perhaps I am going about this all wrong. Is there a better way for me to
sum
the values in the transaction table and update the Customer's balance in
Customer?

-Nicole

I believe what you are doing wrong is attempting to store a value that
can
be derived from other records.

Since your first query is a totals query, when you include it in another
query, this causes the error.

If you think you need to do this then try:

UPDATE Customer
SET Customer.Acct_Balance =
DLookup("[Sum Of Tran_Amount], "qrytransactiontotal", "Cust_ID = " &
Cust_ID);

This assumes Cust_ID is numeric.

--
Duane Hookom
MS Access MVP


Hi Everyone,

I am having a problem with a project I am working on. I need to update
a
table with the results of a query, but I am unsuccessful when I try to
do
this. Below is some suggesstions from a prior post that I made. Can
anyone
help me out with this?

*****************
Thanks for your help Rick.

I have created the transaction total query. Here is the SQL:
SELECT DISTINCTROW Transaction.Cust_ID, Sum(Transaction.Tran_Amount) AS
[Sum
Of Tran_Amount]
FROM [Transaction]
GROUP BY Transaction.Cust_ID;

I created the update balance query with the following SQL:
UPDATE Customer INNER JOIN qrytransactiontotal ON
Customer.Cust_ID=qrytransactiontotal.Cust_ID SET Customer.Acct_Balance
=
qrytransactiontotal.[Sum Of Tran_Amount];

When I try to run the update query I get a message stating that
"operation
must use an updateable query".
What am I doing wrong?

:

Here are some previous links that explain the process. You may need
to
copy
and paste thie links. Make sure the whole link is on one like. I got
these
from Google Groups...


http://groups-beta.google.com/group...stomer+balance&rnum=22&hl=en#dbcf11f4d5ddabf1


http://groups-beta.google.com/group...stomer+balance&rnum=36&hl=en#0ce650f75c7544bf

http://groups-beta.google.com/group...s+calculate+customer+balance&start=30&hl=en&;



In short, you will have to create a query that sums all the
transactions
for
each given client and returns the client number and the sum.

--
Rick B



Ok, how do I do what you suggest?

By the way - this is a very small (10 records) database that I have
to
complete for a class.

:

You don't.

As posted alllll the time, you don't store calculated values. You
would
spend all your time trying to keep this number accurate.
Everytime a
new
invoice was added, the number would be wrong.

Instead, you would build a query to calculate the clients'
balances
and
then
use that.

--
Rick B



Hi I have a scenario that I need help with:

I have two tables, Transaction and Customer. The transaction
table
contains
a field called Amount. A customer can have more than one
transaction.

The Customer table has a field called Balance. I would like for
the
balance
to be the sum of all of the customer's transaction amounts.

How do I have the Balance field in the customer table reflect
the
total of
the customer's transaction amounts?

 

[John Vinson]

I am still getting the same messages... :-(

Try wrapping the DLookUp in a function to convert to currency:

UPDATE Customer
SET Customer.Acct_Balance =
CCur(DLookup("[Sum Of Tran_Amount]", "qrytransactiontotal",
"Cust_ID = " & Cust_ID);


John W. Vinson[MVP]

 

[Guest]

I am getting the same message...

I am still getting the same messages... :-(

Try wrapping the DLookUp in a function to convert to currency:

UPDATE Customer
SET Customer.Acct_Balance =
CCur(DLookup("[Sum Of Tran_Amount]", "qrytransactiontotal",
"Cust_ID = " & Cust_ID);


John W. Vinson[MVP]

 

[John Vinson]

I am getting the same message...

I am still getting the same messages... :-(

Try wrapping the DLookUp in a function to convert to currency:

UPDATE Customer
SET Customer.Acct_Balance =
CCur(DLookup("[Sum Of Tran_Amount]", "qrytransactiontotal",
"Cust_ID = " & Cust_ID);

Is Cust_ID a Text field? If so wrap it in quotes:

"[Cust_ID] = '" & Cust_ID & "'")


John W. Vinson[MVP]