Report summing results

[kastaman]

I need help in generating a report that'll show the sum (count) of certain records in my query. I would like the results of the sum sorted (descending).

any tip would be appreciated.

Kastaman.

 

[jebry]

Hi Kastaman!

You need to make the count(sum) part of the query which acts as the record source for your report. Then you will be able to sort and group by it. You should be able to make a query which groups by your PK and sums on the field you want. Then you can use this query as a table in the query you use for the record source. You will need to join them by the PK and add the sum in the query as a field.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[kastaman]

jebry,

The records I need summed up are not numbers but sales people. So I should have a query where the sales person has a count coulumn, then have another query where the sales person's total count is summed?

What's a pk? I'm sort of confused so if you don't mind giving me the process in step by step fashion.

Kastaman

 

[jebry]

Hi Kastaman!

If you could give me more information on the fields in your table and how you are counting the sales people that would help me give you a better answer. If I had to guess, it sounds to me like you want to count how many orders each sales person had(and maybe sum the order totals?). So you would need something this:

Select Count(OrderNumber) As NumberOfOrders, Sum(OrderTotal) As TotalSales, SalesmanID From Orders Group By SalesmanID

Then the second query would be:

Select (List your fields from Orders), NumberOfOrders, TotalSales From Orders Inner Join Query1 On Orders.SalesmanID = Query1.SalesmanID Order By NumberOfOrders, TotalSales

Of course you can establish the sequence and grouping(if necessary) in your report instead of in the query. Let me know if I am way off base here.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[kastaman]

jebry - Each record for the sales group represents 1 count.
Fields qould be:
Sales
Region
Province
City
I would like to have report where the sum by sales person then sorted by sum per city->province->region.

For example the report would look like:

Don: Total Orders = 100
Orders by Volume
Mississauga - Ontario - Central = 60
Montreal - Quebec - Eastern = 30
Richmond - Vancouver - Western = 10

I'm sortof confused with:
"Select Count(OrderNumber) As NumberOfOrders, Sum(OrderTotal) As TotalSales, SalesmanID From Orders Group By SalesmanID"
Is this from a query or a script?

Thanks,

Kastaman