can't figure out grand total

[folkie]

I have a report based on a query (which is based on other queries and tables). The output of the query has a record for each employee and subprogram (sprg). Each sprg has more than 1 employee. All but 3 of the many fields come out okay on the report.

The report has detail information for each employee, and is sorted by sprg and employee. The dollar fields are subtotalled at the sprg level. That works fine. I also want grand totals for the whole report. This works fine except for the 3 fields in question. These 3 fields only exist at the sprg level. I put them in the query by using “First” in the “total” row of the design grid. I can have it print the sprg subtotal on the report by putting FirstOfRegTO (where RegTO is the name of one of the 3 fields in question) in the Control Source of a text box in the sprg footer. That works fine.

In my test sample, I have 3 records. The following are the relevant fields:

Name.......Sprg..........FirstOfRegTO
John.........5210.........$5,091.00
Mary.........5210.........$5,091.00
Sue..........5310........$13,713.00

On my report, I correctly show $5,091.00 as the sprg subtotal for sprg 5210 and $13,713.00 as the sprg subtotal for sprg 5310. But I can’t get $18,804 as the grand total of RegTO for the report. I’ve tried many things.

I thought I could do something by summing the sprg subtotals by using the Name of the sprg subtotal text box, but that doesn’t work, or I’m not doing it right. I did “=[Reports]![1FedSprg]![Mark]” where "1FedSprg" is the name of the report and “Mark” is the name of the text box. This gives me the last sprg subtotal, which makes sense but isn't what I want. When I add a Sum to it by doing “=Sum([Reports]![1FedSprg]![Mark])” I get a blank.

If you have any suggestions, I’d appreciate it.

 

[bjackson]

have you tried

=sum([the fieldname of the querythat holds the dollar value])

 

[folkie]

That was one of the first things I tried. That added up all the records in the query, which is more than I wanted. I just want one occurance (which would be one record) for each sprg to be added.

 

[WayneRyan]

Mark,

This is not elaborate, but it will work.

If you want to sum only the amount of the first record of each
sprg:

Put two unbound textboxes in your footer: lastSprg, TotalSprg
LastSprg defaults to ""
TotalSprg defaults to 0

Use the DetailPrint event to:

If Me.Sprg <> LastSprg Then
TotalSprg = TotalSprg + Me.FirstOfRegTO
LastSprg = Me.Sprg
End If

hth,
Wayne

 

[Rich]

=Sum([FirstOfRegTO])

 

[folkie]

Wayne, I guess I need more elaboration. I think I understand what you're trying to say, but I don't know how to do it. I created 2 unbound textboxes and named them like you suggested. In the control source I did =0 and =" ". But I'm not sure that's what you meant. I created a 3rd textbox trying to use your code, but couldn't get it to work. You mention DetailPrint event, but I'm not sure what that means. Plus, I don't know how long it will take me to understand what "Me" is, even though I've seen it a lot.

If you could be more specific to someone who's trying to learn, that would be helpful.

Rich, =Sum([FirstOfRegTO]) was one of the things I tried earlier, but doesn't do what I want.

 

[WayneRyan]

Mark,

The two "unbound" text boxes do not have a control source.
The 0 and "" are their default properties. This makes it so that
you don't have to initialize them. They do not directly relate
to your query or table. They are more or less a scratchpad.

In the design view for your report, look at the Detail Bar.
Right-click on it, select properties, then choose the event
tab.

Select the DetailPrint, choose [EventFunction], then click on
the three dots at the right margin. Now you're coding!

' **************************************
If Me.Sprg <> Me.LastSprg Then
Me.TotalSprg = Me.TotalSprg + Me.FirstOfRegTO
Me.LastSprg = Me.Sprg
End If
' **************************************

What will happen is this:

Every time a detail record prints, this event will be triggered.
If the "scratchpad" Me.LastSprg is not the same as the one
in the printed record, Then you will the printed Me.FirstOfRegTO
to your "scratchpad" total. Then you will save the printed
Me.Sprg to your "scratchpad" Me.LastSprg.

Since you are new to code, try this:

Insert the four lines of code.
click on the left margin of the If statement.
The red dot that you see is a breakpoint.
When you run the report you will see your code each time
a detail record is printed. If you hover over a variable, the
debugger will display its value. F8 moves one line. F5 will
run to the next record. After a few iterations, click on the
red dot to remove it and hit F5 and view your report.

Enjoy,
Wayne

 

[folkie]

Wayne,

I don't know if this matters, but I have Access 97. I deleted what I had in the control source. I should've mentioned earlier that, even after looking in help where it said DefaultValue was on the property sheet, I couldn't find it. I've seen it elsewhere in Access, but not on the property sheet for the textboxes you suggested I create. Can you be more specific as to how I get those default values?

 

[WayneRyan]

Hi Mark,

It is on the Data Tab of the properties sheet.

It is about the 8th one down.



keep me posted,
Wayne

 

[folkie]

Wayne,

I thank you for all your help and putting up with my questions, but on my Data tab in the property sheet for these 2 unbound text boxes in the report footer, I just have 3 options - Control Source, Input Mask, and Running Sum. I've looked in the All tab, and couldn't find anything about default values. Am I looking in the wrong place?

 

[WayneRyan]

Hi Mark,

In design view, right-click on one of your unbound textboxes.
Select properties on the bottom of the list.
Then select the Data tab.

Wayne

 

[bjackson]

the default value in reports is the control source

if you wanted a default value ofsay "tryThis" in a text box
then to get that you would set the control source to
="TryThis"

 

[folkie]

Wayne, I tried pasting the Data tab here in my reply, but it doesn't let me paste it like I could in Word. I cannot find an option for default values anywhere in my property sheet for these unbound textboxes. Earlier, you mentioned something about initializing the values. How can I do that?

If I try to do what bjackson suggested, which I tried earlier, the code in the Detail_Print doesn't like it. It says it can't find FirstOfRegTO. If I comment out the code in Detail_Print, it correctly finds FirstOfRegTO in the Sprg Footer, but doesn't do much in the grand total for the report.

 

[Rich]

Can you post a zipped copy of your db here?

 

[folkie]

Here's a zipped sample of the database. Most of my tries from the past couple weeks for getting the correct subtotal are gone. I got tired of looking at them days ago. If you can find where I should put the default values, or any other ideas, that would be cool.

 

[WayneRyan]

Mark,

Here's your db back. Boy is that a large query!

I hope those are the numbers that you want, otherwise
let me know. I chose a different approach, see the code.

Wayne

 

[folkie]

Wayne,

After unzipping the db, I tried to open it. I get a message saying "unrecognized database format." I'm using Access 97. Did you save it as another version?

Regarding the large query, I couldn't figure out how to do it better, and that's how it ended up. And that's after a few make-table queries. There's probably a more efficient way, but I couldn't figure it out. At least I got past the several "query is too complex" messages I used to get.

 

[WayneRyan]

Mark,

Oops ...

Wayne

 

[WayneRyan]

Mark,

Here it is ...

Wayne

 

[folkie]

Wayne,

Thanks for all your work! I was able to open it. I haven't been able to learn as much as I'd like about Access and VBA, and thus I don't understand everything you did.

I guess I'll be able to adapt your code to take into account around 50 SPRGs (I only used 2 SPRGs, 5210 and 5310, in the sample). Tho if you could let me know if there's something more efficient than repeating some of your code 50 times, that would be helpful. It may be awhile before I can spend more time in detail on this - I've been sidedtracked to something else in this database.