Count Stages in a contract

[Avick]

I hope someone can help me. It’s a bit of a strange one.

I have a query with information that I want to count.
Here are the details.

The query is for a reps database.
At a given time the rep will change data in a dropdown box with 6 options to indicate what stage of the prospect he is at. For example:

Stage 1
Stage 2
Stage 3

The rep could have anything from 30 to 50 prospects on the go at any one time with all of them at different stages.

What I am trying to do is count the contracts at each stage. Example

Rep name Stage1 Stage2 Stage3
Joe Bloggs 7 9 14

This will tell his employers how many companies are at Stage 1 and how many are at stage 2 and so on.

This information will then be send to a report that will display the information in the same order as the query.

Can this be done!!!!!

 

[namliam]

Yes and easy too.... Use a crosstab query...

 

[Avick]

I have tried that with no success.
I cant get it to show the records or even get the query to work.

I have a single box that I want the information to come from.

Each record has one field called stage_name

I would need 6 of the same field with a filter on each
Stage_name=Stage1
Stage_name=Stage2
Stage_name=Stage3

And so on.
The end result would give me the reps name and 6 stage_name fields.

 

[namliam]

Paste below sql into the sql window of a query...

TRANSFORM Count(STAGE_NAME) AS CountOfSTAGE_NAME
SELECT [REP NAME], Count(STAGE_NAME) AS TotalContracts
FROM ImportHistory
GROUP BY [REP NAME]
PIVOT STAGE_NAME;

That (or a variation of it...) should do what you are looking for....

 

[Avick]

Hi Namliam

I think you have cracked it. It’s calculating the information as expected and separating the items nicely. Well done.

Can this be used in a report or even exported to Excel in this format.

One more thing. The headings on the fields are 1, 2, 3,
Is there any way to name them stage1 stage2 stage3


Thank you again. You have made my day

 

[namliam]

Hi Namliam

I think you have cracked it. It’s calculating the information as expected and separating the items nicely. Well done.

Can this be used in a report or even exported to Excel in this format.

Yes every query "as is" can be exported to excel either manualy, using a macro or using some VBA...

One more thing. The headings on the fields are 1, 2, 3,
Is there any way to name them stage1 stage2 stage3

Uhm... RTFM??

Well its a nice day and all is going my way today, so I will give you a break as well.... Try something like below, note the (small) difference with the previous SQL...
TRANSFORM Count(STAGE_NAME) AS CountOfSTAGE_NAME
SELECT [REP NAME], Count(STAGE_NAME) AS TotalContracts
FROM ImportHistory
GROUP BY [REP NAME]
PIVOT "Stage" & STAGE_NAME;

Thank you again. You have made my day

Happy to help, even if its a little detailed....