Report, Subreport - How do I get multiples items to display once?

[joel009]

I am new to reports. I have a table with multiple services assigned to a user that has subservices also assigned. Think of it as a telephone service with telephone number and caller id, call waiting, call forwarding etc. I can get my report to display the records individually but each line contains the telephone number and charge, I would like it to display only one time with the charge for summation purposes.

Also I don't know how to add additional details or subreports to the report for the subservices and can't find any reference in Access help.

Joel

 

[jsteph]

Check out the 'Hide Duplicates' property of the textbox,
--Jim

 

[joel009]

Did that with no change. Ended up putting in a services header and displaying thr info there which seems to work. Having some trouble getting sums of the subservices and a grand total working. The report keeps asking me to input info for the names of the text boxes that contain the data. I am looking for a naming conflict. Am I on the right path?
Joel

 

[jsteph]

It does sound like a naming issue, you're referring to a textbox and you may have misnamed it, or referred to the textbox name in a sum() function, instead of the actual control source.

However, even though Access' Wizards give textbox names which are just the same as the controlsource--which can even prevent those naming issues--that's still a design flaw on MS' part in my opinion.

The first thing I'd do is come up with a naming convention for the textboxes--I highly dis-recommend using the stock names that Access' gives.

Anyway, each grouping level would have a textbox named, say
txtNumCalls_Detail
txtNumCalls_WeekFooter
txtNumCalls_MonthFooter
txtNumCalls_ReportTotal

This way you can always use aggregate functions in controlsources with no worries about the #error# in the field. Stick with a convention and you'll have an easier time down the road.
--Jim

 

[joel009]

jsteph - How do I address the control source? I have tried the name of the text box and it still prompts me to enter info.
Text box name I'm trying to use: SubTotal in the Groupfooter1.
If I use a text box from Groupheader0 called it works fine with a control source of 'Sum([Monthly Rate])'.
If I try to use '=Sum([SubTotal])' or '=Sum(SubTotal)' it prompts me with a 'Enter Parameter Value' box. Do I need to include the footer call? I have tried many variations with no change, i.e. : '=Sum(GroupFooter1!SubTotal)', =Sum(GroupFooter1.SubTotal)' I even tried brackets with no success.
On another track, can I hide a subservice header if there is no data for the record?
Thanks,
Joel

 

[jsteph]

You can't Sum() a textbox, it has to be the controlSource (or a valid source-table field--not necessarily one shown in a textbox).

So if the report is based on, say, a query, and one of the fields is CallID, then in a group footer, for the total number of calls you'd put =Sum(CallID) in the Controlsource of that textbox--you're referencing a ControlSource of another textbox (or just a query field).

The brackets aren't necessary as long as the fieldname (the controlsource) has no spaces, dashes, or is some reserved word, such as Date).

Don't forget the = sign, that's important too.
--Jim

 

[joel009]

Jim - Thanks for the input. I'll have to rethink this to get the total I need. It seems if the text box is in the same report section I can reference it in caculations in the same section but if I move to another section I hvae to go back to the control souce. It must have to do with how Access loads the headers/footers/details as I can sum from a text box in GroupHeader0 in the ReportFooter but not in GroupHeader1.
Thanks for your help and time.
Joel