DSUM in Update Query - Type Conversion Error

[RiderJohn]

Hi All,

I have 2 tables. One has a detailed Sales transaction by OrderID, CustID (that is unique ID for each customer) Customer. for e.g.

Table Name: tbl_Sales_CUST
CustID Customer Sales
1234 John 20
1234 John 35
5544 Jill 25
5544 Jill 25

I have another table called "tbl_VAR" that has Customer specific data. In that table there is a "SalesToDate" field. How do I update this table with the sum of Sales from tbl_Sales_CUST .... for e.g.

Table Name: tbl_VAR
CustID Customer Sales Country Region ....
1234 John 55
5544 Jill 50


I am using the DSUM function in the "update to" section of an UPDATE QUERY.

DSum("Sales","tbl_SALES_CUST","[tbl_SALES_CUST].[CustID] =" [tbl_VAR].[CustID])
(note, I have tried switching the criteria otehr way round with no luck
DSum("Sales","tbl_SALES_CUST","[tbl_VAR].[CustID] =" [tbl_SALES_CUST].[CustID])


I get a "TYPE CONVERSION ERROR". Please help.

Thank you,
RJ

 

[SkipVought]

RJ,

"...How do I update this table with the sum of Sales from tbl_Sales_CUST ..."

It's not a good idea to store aggregation data. Also you are duplicating data in both tables.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[genomon]

I agree with Skip, but this may solve your syntax problem:

DSum("Sales","tbl_SALES_CUST","[tbl_SALES_CUST].[CustID] = '" & [tbl_VAR].[CustID] & "'")

Before you criticize someone, you should walk a mile in their shoes.
That way, when you criticize them, you're a mile away and you have their shoes.

 

[RiderJohn]

Thanks Skip and genomon. That single quote solved the problem.

This is a very small local database used within a small dept. It does have some data duplication in tables. Plan is to move to another system in future.