Problem with expression in Group Footer 1

[VickyC]

hello to all

My report skills are a bit rusty, so I need a bit of help.

The DETAIL section of my report has three columns of numbers for each GroupID from 1 to 200. I've shown the case where GroupID = 20.


[tt]
GroupID Header
GroupID = 20
Detail
P Q R
0 2 -1
-2 3 4
1 -2 3
3 -3 1

GroupID Footer
10 12 10
[/tt]

My problem is filling the text boxes in the GroupID Footer.
Here's how those values are determined:

Column P: ABS(1st value) + ABS(all of the differences) + ABS(last value)
= 0 + 2 + 3 + 2 + 3 = 10

Column Q: 2 + 1 + 5 + 1 + 3 = 12

Column R: 1 + 5 + 1 + 2 + 1 = 10


I can't get the correct values to show in the three text boxes in the GroupID Footer for each value of GroupID.

Thanks
Vicky C

 

[dhookom]

Congratulations on a unique question/puzzle. All of this assumes your records have a particular order but you haven't provided a value/field that describes the order.

I expect you will need to use code to process the values sequentially. Can you provide the field name that identifies the order? I assume the grouping field is GroupID.

Duane
Hook'D on Access
MS Access MVP

 

[VickyC]

Thank you for responding. The report is drawn from a table that looks like this:

[tt]
GroupID Position P Q R
1 1 1 5 -3
1 2 0 -4 1
1 3 4 0 -3
1 4 -2 2 0
2 1 4 2 0
2 2 4 2 -1
2 3 0 0 3
2 4 0 -2 2
3 1 4 2 0
.....
20 1 0 2 -1
20 2 -2 3 4
20 3 1 -2 3
20 4 3 -3 1
.....
200 4 3 -2 0[/tt]

Each GroupID has records for Position 1 to 4. Records are grouped by GroupID ascending, and, within each group, records are sorted byPosition ascending. The results shown for GroupID = 20 are the ones I discussed in my original post.

Here's how to interpret the values.... In GroupID = 20, Q travels from home position (ie, 0) to 2, then to 3, then to -2. then to -3, then back home. This entails trips of distance 2, 1, 5, 1, 3. So, I want the total distance travelled (ie, 12) to appear in the GroupID = 20 footer under the 'Q' column.

Thanks in advance for any assistance.
Vicky C.

 

[dhookom]

You create a query with SQL like (I only used P for this one):

SELECT tblTTVickyC.GroupID, tblTTVickyC.Position, tblTTVickyC.P,
 Val(Nz(Abs(tblTTVickyC_1.P-tblTTVickyC.P),tblTTVickyC.P)) AS DistP,
 tblTTVickyC.Q, tblTTVickyC.R
FROM tblTTVickyC LEFT JOIN tblTTVickyC AS tblTTVickyC_1 ON (tblTTVickyC.GroupID = tblTTVickyC_1.GroupID) AND (tblTTVickyC.Position = tblTTVickyC_1.Position +1)
ORDER BY tblTTVickyC.GroupID, tblTTVickyC.Position;

You get a recordset like:
[tt][blue]
GroupID Position P DistP Q R
======= ======== === ===== === ===
1 1 1 1 5 -3
1 2 0 1 -4 1
1 3 4 4 0 -3
1 4 -2 6 2 0
2 1 4 4 2 0
2 2 4 0 2 -1
2 3 0 4 0 3
2 4 0 0 -2 2
3 1 4 4 2 0
20 1 0 0 2 -1
20 2 -2 2 3 4
20 3 1 3 -2 3
20 4 3 2 -3 1
200 4 3 3 -2 0
[/blue][/tt]
Then you use this to create a report. Group by GroupID and order by Postion. Add a text box under the DistP column with a control source of:

=Sum([DistP])+[P]

Duane
Hook'D on Access
MS Access MVP

 

[VickyC]

Duane - that's an excellent solution. I like how you LEFT JOINed the table to a copy of itself in order to 'get at' the previous values when calculating differences. That is precisely where I was stymied. I'll try to remember that!

thanks a bunch
Vicky C.