Calculating Totals

[mvpejp]

I want to build a query on five reps, in this query it will show each product they have sold and how many. What I have done thus far is 'groupby' for sales rep and then product code (there are five) count (I have the product codes in a selectable control) and I am using five expressions one for each product code however it seems that it doesn't work any suggestions.

Again five reps five products and I want find out how many of each product the reps have sold. I look forward to any help that is out there thank you.

MVPEJP

 

[IfButOnly]

When you say it doesn't work - what is happening. Nothing selected?, wrong result?...

You have a different expression for each category - can you provide a sample of the expression you are using.

 

[mvpejp]

Hi Peter as requested,

My five reps are stored in a table called "SalesReps" and it is defined as SalesRepID (autonumber) and SalesRep (text). My five product codes are in a Product table same format as sales rep table.

My five products are, ProdA, ProdB...ProdE.

My query looks something like this:

Sales Rep
from table Sales Rep
Group By

ProdA
from table Products
GroupBy

ProdA
from table Products
Count

.
.
.
.

ProdE
from table Products
Group By

ProdE
from table Products
Count

any help is appreciated thanks.

MVPEJP

 

[IfButOnly]

I don't see the connection between the 'Sales Rep' and 'Product Code' tables. you say the Sales Rep file has two fields - SalesRepID, SalesRep. I assume from your description that the Product file has two fields - ProductID, ProductDesc.

To get the information you are after there must be a third table, eg 'Sales Data' which would have fields like - RepID, ProductID, AmountSold, Cost etc.

Am I missing something?