Non Repeating Element in Group Footer

[ShredDude]

I have a report that details data elements sorted by day, and grouped by week. In each of the Week grouping's footers, I have a bound text box for a running sum of one data element, and two unbound text boxes that contain the result of some calculations based upon the bound running sum. This all works well.

However, I have the need to incorporate a variable from an unbound textbox in the report header, into a calculation in only the first week's grouping footer. I don't need a control to repeat in each weekly grouping footer. I just need a calculation to be different in the first week's grouping footer.

Any ideas for me out there?

I may be able to modify the report's recordsource query to include this variable for the days within the first week only. I guess then I could use that field throughout the report and it would only impact the calculation the first week.

Would that be the approach? Or am I missing something when it comes to features within creating Reports?

Thanks,

Shred

 

[vbaInet]

Is the first week always going to be a fixed number, for example 1? If so just use an IIF() function and check against that number.

If not, number your footer and check against 1.

 

[ShredDude]

The first "week" will result in a varying number of days based on the start date of the report. For example, if the Start date is a Wednesday, the report's first "weekly" grouping will end up with 4 rows. The following groups will contain 7 rows, until the last group which will contain however many rows are necessary from the last Sunday until the last date of the report.

It's only in this first grouping that I need to have a calculation be different.

Are you suggesting that perhaps I calculate the week number of the date contained in each record and only do the extra calculation if that week number is the minimum week number in the recordset?

Could use sometihing like this in the query I guess, to have an extra field to use the IIF against:

WeekNum: Int((([dtWork] - DateSerial(Year([dtWork]), 1, 0)) + 6) / 7)

That might work. I'll give it a try.

As for the numbering your footer suggestion, could you point me in the right direction on how to do that? I'm new to Access' reporting.

Thanks,

Shred

 

[stopher]

To do vbaInet's second suggestion - numbering the footer - do the folowing:

• add a new text box to your footer
• set the Control Source to =1
• set the Running Sum property Over Group
• give the text box an appropriate name e.g. ftrNum

When you run the report you should see that this text box increments for each footer occurrence. You can make the text box invisible if you like.

Then you can do your formula like this:

=IIF(ftrNum=1, formula for first week, formula for other weeks)

hth
Chris

 

[ShredDude]

I"ve achieved my objective by including an extra field in the temporary table that I'm populating with denormalized data for this report. Then when populating the recordset that updates this temp table, on the first record, I include the needed value for the firs week's calculation.

Then on the Report, I modified the Control source of the Text boxes in the the Group Footer to use that field in their equation. For all records but he first, the value is zero and has no effect on the results.

That ends up with a different result in the first week's group footer as needed.

Thanks for the feedback.

 

[ShredDude]

stopher:

Thank you for the insight on using an invisible textbox with a running sum to number the footer. I learned something new!

I'll give that try as an exercise. I've accomplished my goal by including an additional field in the underlying recordset and only populating the first record of the rs with the needed value. This seems wasteful to carry an extra field in the recordset when I only need a value from it on the first record. that same value is available to me in a text box in the header of the report.

Your method would allow me to pull that variable from the header text box into the first footer's formula. I guess there's always multiple ways to accomplish something!