SQL Query - DSum - Recordset - VBA

[Acheron]

Hello,

Working on a table counting about 12'000 rows, I have the following issue :
I use a DSum function inside a Make table query adding a column containing a
total based on specific criteria to each of these rows. The thing is that the
query takes a huge amount of time to complete its task and create the new
table. In the past, I transposed the DSum function & result in SQL. But in
this case, I need two identical tables to perform this operation. This
solution can't be used anymore due to size issues.

Then, I am wondering if it would be more "efficient" to work through VBA &
Recordsets. More precisely, would it be possible (and faster) to take the
content of the original table and store it in a recordset, perform the DSum
function and save the result in a new table ?! If expecting a significant
speed difference in the data treatment... how could I do it ? Any suggestions
or related web page would be great. TIA for your cooperation.

Acheron

 

[Duane Hookom]

You might get better performance using either a subquery or a custom
function to replace the DSum(). Without a clue as to your DSum(), it's hard
to give a definitive answer.

 

[Acheron]

Ok. Here we go :

After applying several filters on my records (the first table counts about 1
mio rows) based on several criteria, I make a new table. On this one, I need
to get running totals (and save the result in a new table). The DSum function
I use goes like this :

DSum("[AmountToSum]";"MyTable";"[Year] = " & [Year] & " And CLng([Date]) <="
& CLng([Date]) & " And [MyGroup1] Like " & Chr(34) & [MyGroup1] & Chr(34) & "
And [MyGroup2] Like " & Chr(34) & [MyGroup2] & Chr(34))

That's it !

Hope this will help. TIA

Acheron

 

[Duane Hookom]

Do the MyGroup1 and MyGroup2 values contain wildcards or are they just the
text?
The subquery would be like:
(SELECT Sum([AmountToSum]) FROM MyTable z WHERE z.[Year] = [Year] And
CLng(z.[Date]) <= CLng([Date]) And z.[MyGroup1] Like [MyGroup1] And
z.[MyGroup2] Like [MyGroup2] )

If the group expressions don't contain wildcards, then replace the " Like "
with " = ".


--
Duane Hookom
MS Access MVP

Ok. Here we go :

After applying several filters on my records (the first table counts about
1
mio rows) based on several criteria, I make a new table. On this one, I
need
to get running totals (and save the result in a new table). The DSum
function
I use goes like this :

DSum("[AmountToSum]";"MyTable";"[Year] = " & [Year] & " And CLng([Date])
<="
& CLng([Date]) & " And [MyGroup1] Like " & Chr(34) & [MyGroup1] & Chr(34)
& "
And [MyGroup2] Like " & Chr(34) & [MyGroup2] & Chr(34))

That's it !

Hope this will help. TIA

Acheron