Query Criteria

[Neil]

Hi,

Some of the fields I have in a table which is imported from a csv file are:
Salesperson (has up to 20 names in it)
Status (has Won,Lost,Submitted,Prospect,Did Not Proceed,Estimation)
Status Date (has date)
Value ($ value)

What I am getting stuck on is a query to get the total value per Salesman
for each status and also the count of each status, I would like it to show
in datasheet form something like below.

Salesman $won No. Won $lost No.Lost
A $100000 5 $150000 3
B $75000 2 $10000 1
C $150000 4 $0
0
and so on.

I have been able to get so far with a crosstab query, however I can not get
it to count for each status, it just gives me a total count by salesman,
also I can't work out where to put in a date criteria.

TRANSFORM Sum(tblResults.[Total Value]) AS [SumOfTotal Value]
SELECT tblResults.Salesperson, Sum(tblResults.[Total Value]) AS [Total Of
Total Value], Count(tblResults.Status) AS NoOfJobs
FROM tblResults
GROUP BY tblResults.Salesperson
PIVOT tblResults.Status;


Any help in getting started on this would be appreciated.

Neil

 

[Neil]

Never mind, I have worked out a solution, just need to add a few fields to
the first query.
for example:
won:iff([Status]="Won",1,0)
wonvalue:iif([Status]="Won","[Total Value],0)
won:iff([Status]="Lost",1,0)
wonvalue:iif([Status]="Lost","[Total Value],0)

etc
etc

Should look for the simple method before jumping in.

Neil