Crosstab Query 1

[UncleHuckleberry]

Hello,

I am trying to produce a Cross Tab query in Access and wondered if what i was trying to do is possible and if it is if somebody could point me in the right direction.

My Data is:

ApplicationID Successful Date Group
1 YES 01/02/04 Group1
2 NO 01/02/04 Group1
3 NO 01/03/04 Group2

The Query i'm trying to produce trying to get the number of applicants who have been successful each month and break that down by Group, for example:

Percentage Successful.

Feb-04 Mar-04
Group1 33% 0%
Group2 0% 0%

I'm struggling to get my head around this on. Any help would be appreciated!!

 

[lynchg]

Try this:

Create a query and call it qPrelimTotals with the following SQL:

Select Group, DateField, Count(ApplicationID) as TotalRecs, Sum(IIf(Successful = True,1,0)) as SuccessApps
From yourTable
Group By Group, Date;

Then this query:

Transform Format((TotalRecs/SuccessApps)*100,"00.0%") as PctSuccess
Select Group
From qPrelimTotals
Pivot DateField;

I'm not quite sure of the exact syntax of the Format function with percent symbol but I think that will get you close. You can look in Help if necessary.

Good Luck