Help with Self-Referencing DSUM

[Bill Bisco]

I've been trying to change my form to be based off of a query that uses DSum rather than Sum. There are 2 tables, tblProcesses and tblElements. Each Process has many Elements.

However, I'm confused on how to reference DSUM to get the same results that I did with using Totals and Sum.

The form that this query will be used for displays several different Processes. I need all the Element times that belong to each particular Process, summed up for that particular Process.

I have tried:

MySum: DSum("ElementTime","tblElements","[Process ID] = Forms!frmProcessSelectLSRS!frmProcessSelectLSSubform.Form.[Process ID] ")

But it's not working. Any help is appreciated.

 

[Alc]

I've been trying to change my form to be based off of a query that uses DSum rather than Sum. There are 2 tables, tblProcesses and tblElements. Each Process has many Elements.

However, I'm confused on how to reference DSUM to get the same results that I did with using Totals and Sum.

The form that this query will be used for displays several different Processes. I need all the Element times that belong to each particular Process, summed up for that particular Process.

I have tried:

MySum: DSum("ElementTime","tblElements","[Process ID] = Forms!frmProcessSelectLSRS!frmProcessSelectLSSubform.Form.[Process ID] ")

But it's not working. Any help is appreciated.

Would splitting out the form's data from the rest help? e.g.
MySum: DSum("ElementTime","tblElements","[Process ID] = " & Forms!frmProcessSelectLSRS!frmProcessSelectLSSubform.Form.[Process ID])

 

[Bill Bisco]

Thanks for the reply Alc,

My problem isn't the syntax in this case. It's that my usual way of thinking of working with queries won't work with DSUM.

In a normal query option, tblElements, and tblProcesses would be connected. And when my query asked to total up ElementTime, then ElementTime would be different for every process

Take this Picture to help illustrate.

The Time for each Process is actually the sum of all its Elements. And each sum is different.

The Problem with DSUM is that I don't know how to get 1 DSUM field to show up as different sums depending on my query.

Make sense?

 

[Alc]

I think I get it, but bear with me

Would all the elements always start with the same word? In the example you gave, 'Jump' is a common thread, but is it always the case that the first four characters link the 'theme'?

If so, you coud try using these characters, as opposed to the ID.
If there is a field on the form that displays the common process name, it would look something like this

Dim str_Process as String
Str_Process = Left(Forms!frmProcessSelectLSRS!frmProcessSelectLSSubfo rm.Form[I].[B]fieldname[/B],[/I]4)
DSum(("ElementTime","tblElements[I]","[B]name of process[/B] =[/I] '" & str_Process & "'"[I])[/I]

 

[Bill Bisco]

Thanks for the reply Alc,

The Process Names will not always be the same, or start with the same 4 words, but thanks for the attempt.

Let me show you a little bit more, and perhaps you can understand my dilemma.

In the Design view, as you can see, there is only 1 field for SumOfElementTime

The only way I know to use DSUM, currently is to use a code like this:

MySum: DSum("ElementTime","tblElements","[Process Name] = Jump Lostways)"

However, when I do that, I will get a result like this:

When I want a result like this:

With Every SumofElementTime being different. Now, I don't know if that's possible to do or not, and I was hoping to find out if it was here.

Your post did inspire me though, and I tried a New Formula for DSUM, I tried making a copy of the form I wanted to use, and refer to the ProcessNames of the other form.

MySum: DSum("ElementTime","tblElements","ProcessName = Forms!frmProcessSelectLSSubformCopy.ProcessName")

However, it does not display the Sum of ElementTime for each process, rather it looks at the first record selected (in this case Jump up and Down), and then uses that value for every field.

Now, I could create about 30 extra forms, 30 extra queries, and 200 or so extra Query Fields, however, I was hoping for a more dynamic, elegant, and less memory intensive solution.

I know that long posts are usually a death sentence, but any help or ideas are appreciated.

Sincerely,
Bill