Sums and Counts for group

[DebbieDavis]

Hi there,

I'm stuck. I have a report that is grouped by salesman, then by job #. The report is called from a form where the user enters a date range. I figured out how to show one salesman per page, but I can't figure out how to total up the jobs for each salesman and total sum for the job cost.

I have a report footer that totals the number of jobs and cost for the entire report and that works great, but I need it broken down by salesman.

I created a text box in the group footer for salesman and used this =Count(*) but it gives me some wacked out number that doesn't seem to match anything. Same with the sum.

Any ideas would be appreciated. Many thanks in advance for your input.

 

[adsfx]

i dont think you can use aggregate functions in the footer,
you have to use the reports query to do this

 

[DebbieDavis]

Thanks very much. Please excuse my ignorance, but how do I do that?

 

[PaulBricker]

Debbie, you should be able to get a group count using
=Count(*)

Something else must be an issue. Can you provide a little more info about the report. What the info looks like. It sounds pretty straight forward. Also, what expression are you using to sum your values in the Salesman footer.

Paul

 

[adsfx]

Hi,

think i got the results you expect...

1)-In the reports query, (double click on grey area, then click on 'record source', then click 3dots '...'
this should bring up reports query builder)
-Select table and assign each field to a box
-Close query builder

2) in sorting and grouping I have:
name ascending
jobno ascending
(name has header and footer)

3)in name header there is a txtbox bound to name
4)in detail i put txtboxes for job no/name and amount
5)in name footer i put txt boxes to count jobno and to sum amount, put formula in control source property for respective box:
txtboxjobcount =Count([jobno])
txtboxamountsum =Sum([amt])

6)finally if you want totals in page footer create two more text boxes and bind (link) to the above txtboxes in name footer
e.g txtboxjobcountfooter =[txtboxjobcount] etc...

tried my best to explain clearly let me know if I can help nemore
regards Mark