Update query using DSum and 3 (three) criteria

[mcmuppet]

Hello

I have two tables, one with the lowest level of data and one with summary data. Both have a date and ID field and I have linked these together in the query. I want to update a field in the summary table based on the sum of the data in the low level table. Here is my SQL:

UPDATE tblSummary INNER JOIN [tblLowLevel] ON (tblSummary .Date = [tblLowLevel].Date) AND (tblSummary .LU = [tblLowLevel].LU) SET tblSummary .[Segment] = DSum("[Deposit Count]","[tblLowLevel]","[tblLowLevel]![LU] = " & [tblSummary ]![LU] & " AND [Segment] = 'Personal' And [tblLowLevel]![Date]= " & [tblSummary ]![Date]);

It runs but I get an error message saying it cant update the table due to type conversions but I am updating a numeric value into a number field so I am guessing I have my quotes in the wrong place.

Can anyone help me please?

 

[plog]

Why do you have a seperate table for summary data? You shouldn't store redundant or calculated data. Why not use a query to get the summary data?

 

[mcmuppet]

Because my low level data has c.5 million records and I need users to be able to query that when they need to but mostly they will only need high level data so thats why I am creating the summary table, this will only be a few thousand records

 

[plog]

Total records shouldn't be a factor, time should be. How long does a typical query take them?

Assuming you should do this, why are you doing it like you are doing? You've built a query on the 2 datasources you need, however, you're doing a DSUM back into one of the sources.

In that DSum you essentially you have 2 links and 1 criteria. The tables are already linked on the [Date] field (fyi, bad choice for a field name--its a reserved word), so you just need to move the [LU] criteria to the INNER JOIN and then make [Segment]="Personal" a criteria in the query. No need for a Dsum.

 

[mcmuppet]

Essentially the difference between the two tables is the larger one has the data split by date and segment, the smaller table just has date. So I am doing the DSum to update the smaller table with the data from the larger table.

I have two links, one on date and one on LU and the criteria is the segment.

If I dont do a Dsum then the query will just update the smaller table with just on of the values from the larger table i.e. it wont sum up for all the records for the segment on that date and LU. I dont see how else I can do this without a DSum? Is the SQL completely wrong do you think or is it close?