DSum in Update Query

A

Arvin Villodres

Good day to everyone.

I have three tables in an update query and their fields are
as follows:

tblTransactionDetails tblTransactions tblCustomers
TransNo TransNo(PK) CustomerID(PK)
SubtotalSales CustomerID TotalSales
QtySales
Amount

I want to update TotalSales in tblCustomers with the sum of
SubtotalSales from tblTransactionDetails.

The Update To looks like this:
DSum("[SubtotalSales]","tblTransactionDetails",_
"[CustomerID]=" & [CustomerID])

but if I run this query this error appears "The specified
field '[CustomerID]' could refer to more than one table
listed in the FROM clause of your SQL statement."

I tried using ..."tblTransaction.[CustomerID] =" _
& tblCustomer.[CustomerID]) but the same error happens or
lock key or validation key error happens.

What should I do? Thanks for your help.
 
D

Duane Hookom

Paste your full SQL. Maybe this will work:
DSum("[SubtotalSales]","tblTransactionDetails", "[CustomerID]=" &
tblCustomers.[CustomerID])
You had forgotten the "s" in tblCustomers.
 
A

Arvin Villodres

Thanks for the quick response.

I did exactly what was written here. The query seemed to
work but i still got this error message.

"Access didn't update 2 field(s) due to a type conversion
failure, 0 records for key, lock, validation rule violation."

What does this mean? When I looked at the table no updated
record appeared (null).

thanks for the help.

-----Original Message-----
Paste your full SQL. Maybe this will work:
DSum("[SubtotalSales]","tblTransactionDetails", "[CustomerID]=" &
tblCustomers.[CustomerID])
You had forgotten the "s" in tblCustomers.

--
Duane Hookom
MS Access MVP


Good day to everyone.

I have three tables in an update query and their fields are
as follows:

tblTransactionDetails tblTransactions tblCustomers
TransNo TransNo(PK) CustomerID(PK)
SubtotalSales CustomerID TotalSales
QtySales
Amount

I want to update TotalSales in tblCustomers with the sum of
SubtotalSales from tblTransactionDetails.

The Update To looks like this:
DSum("[SubtotalSales]","tblTransactionDetails",_
"[CustomerID]=" & [CustomerID])

but if I run this query this error appears "The specified
field '[CustomerID]' could refer to more than one table
listed in the FROM clause of your SQL statement."

I tried using ..."tblTransaction.[CustomerID] =" _
& tblCustomer.[CustomerID]) but the same error happens or
lock key or validation key error happens.

What should I do? Thanks for your help.
Click to expand...


.
Click to expand...
 
D

Duane Hookom

Again, "Paste your full SQL" into a reply. It would also help to know the
field types.

--
Duane Hookom
MS Access MVP


Arvin Villodres said:
Thanks for the quick response.

I did exactly what was written here. The query seemed to
work but i still got this error message.

"Access didn't update 2 field(s) due to a type conversion
failure, 0 records for key, lock, validation rule violation."

What does this mean? When I looked at the table no updated
record appeared (null).

thanks for the help.

-----Original Message-----
Paste your full SQL. Maybe this will work:
DSum("[SubtotalSales]","tblTransactionDetails", "[CustomerID]=" &
tblCustomers.[CustomerID])
You had forgotten the "s" in tblCustomers.

--
Duane Hookom
MS Access MVP


Good day to everyone.

I have three tables in an update query and their fields are
as follows:

tblTransactionDetails tblTransactions tblCustomers
TransNo TransNo(PK) CustomerID(PK)
SubtotalSales CustomerID TotalSales
QtySales
Amount

I want to update TotalSales in tblCustomers with the sum of
SubtotalSales from tblTransactionDetails.

The Update To looks like this:
DSum("[SubtotalSales]","tblTransactionDetails",_
"[CustomerID]=" & [CustomerID])

but if I run this query this error appears "The specified
field '[CustomerID]' could refer to more than one table
listed in the FROM clause of your SQL statement."

I tried using ..."tblTransaction.[CustomerID] =" _
& tblCustomer.[CustomerID]) but the same error happens or
lock key or validation key error happens.

What should I do? Thanks for your help.
Click to expand...


.
Click to expand...
Click to expand...
 
A

Arvin Villodres

CustomerID - Text Primary Key in tblCustomer
TotalReceivables and SubtotalOrder - Number, Double, Standard


UPDATE (tblCustomers INNER JOIN tblTransaction ON
tblCustomers.CustomerID = tblTransaction.CustomerID) INNER
JOIN tblTransactionDetails ON tblTransaction.TransNo =
tblTransactionDetails.TransNo SET
tblCustomers.TotalReceivables =
DSum("[SubtotalOrder]","tblTransactionDetails","[CustomerID]="
& [tblCustomers].[CustomerID]);

Sorry for the late reply. we had trouble with our
connection yesterday.

Thanks for the reply.
-----Original Message-----
Again, "Paste your full SQL" into a reply. It would also help to know the
field types.

--
Duane Hookom
MS Access MVP


Thanks for the quick response.

I did exactly what was written here. The query seemed to
work but i still got this error message.

"Access didn't update 2 field(s) due to a type conversion
failure, 0 records for key, lock, validation rule violation."

What does this mean? When I looked at the table no updated
record appeared (null).

thanks for the help.

-----Original Message-----
Paste your full SQL. Maybe this will work:
DSum("[SubtotalSales]","tblTransactionDetails", "[CustomerID]=" &
tblCustomers.[CustomerID])
You had forgotten the "s" in tblCustomers.

--
Duane Hookom
MS Access MVP


Good day to everyone.

I have three tables in an update query and their fields are
as follows:

tblTransactionDetails tblTransactions tblCustomers
TransNo TransNo(PK) CustomerID(PK)
SubtotalSales CustomerID TotalSales
QtySales
Amount

I want to update TotalSales in tblCustomers with the sum of
SubtotalSales from tblTransactionDetails.

The Update To looks like this:
DSum("[SubtotalSales]","tblTransactionDetails",_
"[CustomerID]=" & [CustomerID])

but if I run this query this error appears "The specified
field '[CustomerID]' could refer to more than one table
listed in the FROM clause of your SQL statement."

I tried using ..."tblTransaction.[CustomerID] =" _
& tblCustomer.[CustomerID]) but the same error happens or
lock key or validation key error happens.

What should I do? Thanks for your help.



.
Click to expand...
Click to expand...


.
Click to expand...
 
D

Duane Hookom

Is there a reason you have included three tables in the update query when
you are only updating tblCustomers and the DSum() only references
tblCustomers from the query?
UPDATE tblCustomers
SET tblCustomers.TotalReceivables =
DSum("[SubtotalOrder]","tblTransactionDetails","[CustomerID]="""
& [tblCustomers].[CustomerID] & """");


--
Duane Hookom
MS Access MVP


Arvin Villodres said:
CustomerID - Text Primary Key in tblCustomer
TotalReceivables and SubtotalOrder - Number, Double, Standard


UPDATE (tblCustomers INNER JOIN tblTransaction ON
tblCustomers.CustomerID = tblTransaction.CustomerID) INNER
JOIN tblTransactionDetails ON tblTransaction.TransNo =
tblTransactionDetails.TransNo SET
tblCustomers.TotalReceivables =
DSum("[SubtotalOrder]","tblTransactionDetails","[CustomerID]="
& [tblCustomers].[CustomerID]);

Sorry for the late reply. we had trouble with our
connection yesterday.

Thanks for the reply.
-----Original Message-----
Again, "Paste your full SQL" into a reply. It would also help to know the
field types.

--
Duane Hookom
MS Access MVP


Thanks for the quick response.

I did exactly what was written here. The query seemed to
work but i still got this error message.

"Access didn't update 2 field(s) due to a type conversion
failure, 0 records for key, lock, validation rule violation."

What does this mean? When I looked at the table no updated
record appeared (null).

thanks for the help.


-----Original Message-----
Paste your full SQL. Maybe this will work:
DSum("[SubtotalSales]","tblTransactionDetails",
"[CustomerID]=" &
tblCustomers.[CustomerID])
You had forgotten the "s" in tblCustomers.

--
Duane Hookom
MS Access MVP


"Arvin Villodres" <[email protected]>
wrote in message
Good day to everyone.

I have three tables in an update query and their fields are
as follows:

tblTransactionDetails tblTransactions tblCustomers
TransNo TransNo(PK)
CustomerID(PK)
SubtotalSales CustomerID TotalSales
QtySales
Amount

I want to update TotalSales in tblCustomers with the sum of
SubtotalSales from tblTransactionDetails.

The Update To looks like this:
DSum("[SubtotalSales]","tblTransactionDetails",_
"[CustomerID]=" & [CustomerID])

but if I run this query this error appears "The specified
field '[CustomerID]' could refer to more than one table
listed in the FROM clause of your SQL statement."

I tried using ..."tblTransaction.[CustomerID] =" _
& tblCustomer.[CustomerID]) but the same error happens or
lock key or validation key error happens.

What should I do? Thanks for your help.



.
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

eliminating duplicate records in select query 1
Query question involving AutoNumber data type 1
Two table Query 2
CASE statement in query 3
Using the results from one query as the filter for another. 1
FormBased On A Query 2
Computing total sales with a query 2
Update query question 3

Top