Problem using Totals function in a query

[Cowboysooner]

Existing Table:
Cross Cross
Date # of Sale Sale
Closed Spreads (1) (2)
Adams 1/1/03 3 RREM DDA
Adams 1/5/03 2 DDA
Adams 1/6/03 2
Jones 1/1/03 1 RREM
Jones 1/2/03 1 RREM DDA
Jones 1/6/03 1 Sweep

I need to create the following output:
# Closed # of Spreads # of Cross Sales
Adams 3 7 3
Jones 3 3 4

 

[randysmid]

Hi,
I would suggest creating two separate queries. The first one will feed the crosstab. In the first one, you will create the totals. Using the first query as the recordsource for the crosstab should then do the trick. Please reply to this post if you need more specific detail. HTH,
Randy Smith
California Teachers Association

 

[Cowboysooner]

Thanks for your response but I'm having trouble creating the totals query. I am able to get a total for an entire column but can't get it to separate the activity per salesman.
For example, using my previous post, totaling the Date Closed column gives a value of 6, I need to know that Adams had 3 sales and Jones had 3 sales. I have about 50 salesman I need to track and 8 separate sales activities.

 

[Cowboysooner]

Thanks for your response but I'm having trouble creating the totals query. I am able to get a total for an entire column but can't get it to separate the activity per salesman.
For example, using my previous post, totaling the Date Closed column gives a value of 6, I need to know that Adams had 3 sales and Jones had 3 sales. I have about 50 salesmen I need to track and 8 separate sales activities.

 

[hjgoldstein]

Are you using the "Group By" function for the "Salesman" field?

This should do the trick.

 

[Cowboysooner]

Yes, I have "group by" for the [salesman] field but I still have a problem.

I have a [Number of Sales] field with a DCount formula that correctly calculates the total number of sales when set to "group by" (all salesman show the total number of sales for the column, Adams = 6, Jones = 6) but returns incorrect information when changed to "count".

 

[randysmid]

Hi,
I think you need to check your query very carefully. For instance, do you have any more "group by" fields that is skewing the results? HTH,
Randy Smith
California Teachers Association

 

[hjgoldstein]

"I have a [Number of Sales] field with a DCount formula that correctly calculates the total number of sales "

I think that your problem could be this. You are using the Count parameter on an already counted field.

Try doing the count on each of the sales fields individually and then group by the salesmen.

Good luck.