BigDave06461
New member
- Local time
- Yesterday, 17:53
- Joined
- Feb 21, 2018
- Messages
- 7
I have a query with the following fields:
Topic Duration
Topic_Order
Meeting_ID
Data source: qry_topic_combo
Topic_Duration Topic_Order Meeting_ID
15 1 7
45 2 7
15 3 7
30 4 7
15 1 8
30 2 8
30 3 8
15 4 8
I am trying to generate a running total of topic duration by topic_order and meeting ID for previous topics. So the record for meeting_id 1 with topic_order 1 would have a zero value, topic_order 2 would have a value of 15, etc. The output for the above data should look like this :
Topic_Duration Topic_Order Meeting_ID Running_Total
15 1 7 0
45 2 7 15
15 3 7 60
30 4 7 75
15 1 8 0
30 2 8 15
30 3 8 45
15 4 8 75
So the first expression I wrote got a running total by topic_order but ignored meeting_id:
expression:
Running_Total: DSum(" [Topic_Duration] ","QRY_TOPIC_COMBO","[Topic Order]<" & [Topic Order] & "")
Output:
Topic_Duration Topic_Order Meeting_ID Running_Total
15 1 7 0
45 2 7 30
15 3 7 105
30 4 7 150
15 1 8 0
30 2 8 30
30 3 8 105
15 4 8 150
So far so good. When I tried to add the second criteria, though, My running total just becomes a total for the entire column:
Expression:
Running_Total: DSum(" [Topic_Duration] ","QRY_TOPIC_COMBO","[Topic Order]<" & [Topic Order] & "" And [Meeting_id]=[meeting_id])
Output:
Topic_Duration Topic_Order Meeting_ID Running_Total
15 1 7 195
45 2 7 195
15 3 7 195
30 4 7 195
15 1 8 195
30 2 8 195
30 3 8 195
15 4 8 195
What am I doing wrong?
Topic Duration
Topic_Order
Meeting_ID
Data source: qry_topic_combo
Topic_Duration Topic_Order Meeting_ID
15 1 7
45 2 7
15 3 7
30 4 7
15 1 8
30 2 8
30 3 8
15 4 8
I am trying to generate a running total of topic duration by topic_order and meeting ID for previous topics. So the record for meeting_id 1 with topic_order 1 would have a zero value, topic_order 2 would have a value of 15, etc. The output for the above data should look like this :
Topic_Duration Topic_Order Meeting_ID Running_Total
15 1 7 0
45 2 7 15
15 3 7 60
30 4 7 75
15 1 8 0
30 2 8 15
30 3 8 45
15 4 8 75
So the first expression I wrote got a running total by topic_order but ignored meeting_id:
expression:
Running_Total: DSum(" [Topic_Duration] ","QRY_TOPIC_COMBO","[Topic Order]<" & [Topic Order] & "")
Output:
Topic_Duration Topic_Order Meeting_ID Running_Total
15 1 7 0
45 2 7 30
15 3 7 105
30 4 7 150
15 1 8 0
30 2 8 30
30 3 8 105
15 4 8 150
So far so good. When I tried to add the second criteria, though, My running total just becomes a total for the entire column:
Expression:
Running_Total: DSum(" [Topic_Duration] ","QRY_TOPIC_COMBO","[Topic Order]<" & [Topic Order] & "" And [Meeting_id]=[meeting_id])
Output:
Topic_Duration Topic_Order Meeting_ID Running_Total
15 1 7 195
45 2 7 195
15 3 7 195
30 4 7 195
15 1 8 195
30 2 8 195
30 3 8 195
15 4 8 195
What am I doing wrong?