Need help with query

Happy Holidays everyone. I have a task to create a report from a data set. I
think the quickest way is to query the data.

fields:
Date, Region (ie BC, AB, SK), Source (ie phone, fax, mail),
Type (alert, notifi., exer.), Quarter (ie Q1, Q2, Q3, Q4) which quarter of
the year the request came in). I also have 4 Yes/No fields Q1, Q2, Q3, Q4
that are automatically checked based on the date of the call.

What I am looking for is a query that will count (give me a total) of types
for all quarters. ie
BC - Phone - Alert Q1-6 Q2-8 Q3-9 Q4-15
BC - Phone - Notif. Q1-0 Q2-0 Q3-0 Q4-1
BC - Phone - Exer. Q1-2 Q2-2 Q3-1 Q4-10
BC - Fax - Alert.....
BC - Fax - Notif....
BC - Fax - Exer......
BC - Mail - Alert.....
BC - Mail - Notif...
BC - Mail - Exer...
SK - Phone - Alert..... Repeating all [source] and [type]

Any suggestions would be greatly appreciated..
Cheers

I think a crosstab query will do what you want.
TRANSFORM Count(DarinConnors.Quarter) AS CountOfQuarter
SELECT DarinConnors.Region, DarinConnors.Source, DarinConnors.Type
FROM DarinConnors
GROUP BY DarinConnors.Region, DarinConnors.Source, DarinConnors.Type
PIVOT "Q " & [Quarter];


You say "Quarter (ie Q1, Q2, Q3, Q4) which quarter of the year the request
came in). I also have 4 Yes/No fields Q1, Q2, Q3, Q4 that are automatically
checked based on the date of the call."
It sounds like you are recording the quarter twice. Why twice?

"Darin Connors" wrote:

Happy Holidays everyone. I have a task to create a report from a data set. I
think the quickest way is to query the data.

fields:
Date, Region (ie BC, AB, SK), Source (ie phone, fax, mail),
Type (alert, notifi., exer.), Quarter (ie Q1, Q2, Q3, Q4) which quarter of
the year the request came in). I also have 4 Yes/No fields Q1, Q2, Q3, Q4
that are automatically checked based on the date of the call.

What I am looking for is a query that will count (give me a total) of types
for all quarters. ie
BC - Phone - Alert Q1-6 Q2-8 Q3-9 Q4-15
BC - Phone - Notif. Q1-0 Q2-0 Q3-0 Q4-1
BC - Phone - Exer. Q1-2 Q2-2 Q3-1 Q4-10
BC - Fax - Alert.....
BC - Fax - Notif....
BC - Fax - Exer......
BC - Mail - Alert.....
BC - Mail - Notif...
BC - Mail - Exer...
SK - Phone - Alert..... Repeating all [source] and [type]

Any suggestions would be greatly appreciated..
Cheers

"KARL DEWEY" wrote:

I think a crosstab query will do what you want.
TRANSFORM Count(DarinConnors.Quarter) AS CountOfQuarter
SELECT DarinConnors.Region, DarinConnors.Source, DarinConnors.Type
FROM DarinConnors
GROUP BY DarinConnors.Region, DarinConnors.Source, DarinConnors.Type
PIVOT "Q " & [Quarter];


I was recording quarter twice in case their was an easier method to display
the data.

I used "Crosstab Query Wizzard and basically created exactly that.

Now one last question. The crosstab query leaves the field blank (null)
where there is no data. Is there a way to force a default 0 in the query or
on the subsequent report?

Thanks for your help.

The best I could do was to have a select query to do it.

SELECT DarinConnors_Crosstab.Region, DarinConnors_Crosstab.Source,
DarinConnors_Crosstab.Type, IIf([Q 1] Is Null,0,1) AS [1st Qtr], IIf([Q 2] Is
Null,0,1) AS [2nd Qtr], IIf([Q 3] Is Null,0,1) AS [3rd Qtr], IIf([Q 4] Is
Null,0,1) AS [4th Qtr]
FROM DarinConnors_Crosstab;


"Darin Connors" wrote:



"KARL DEWEY" wrote:

I think a crosstab query will do what you want.
TRANSFORM Count(DarinConnors.Quarter) AS CountOfQuarter
SELECT DarinConnors.Region, DarinConnors.Source, DarinConnors.Type
FROM DarinConnors
GROUP BY DarinConnors.Region, DarinConnors.Source, DarinConnors.Type
PIVOT "Q " & [Quarter];


I was recording quarter twice in case their was an easier method to display
the data.

I used "Crosstab Query Wizzard and basically created exactly that.

Now one last question. The crosstab query leaves the field blank (null)
where there is no data. Is there a way to force a default 0 in the query or
on the subsequent report?

Thanks for your help.

PMFBI

Will not an NZ function applied to Count work?

TRANSFORM NZ(Count(DarinConnors.Quarter),0)

Apologies again for butting in, especially
if I misunderstood.

"KARL DEWEY"wrote:
The best I could do was to have a select query to do it.

SELECT DarinConnors_Crosstab.Region, DarinConnors_Crosstab.Source,
DarinConnors_Crosstab.Type, IIf([Q 1] Is Null,0,1) AS [1st Qtr], IIf([Q 2]
Is
Null,0,1) AS [2nd Qtr], IIf([Q 3] Is Null,0,1) AS [3rd Qtr], IIf([Q 4] Is
Null,0,1) AS [4th Qtr]
FROM DarinConnors_Crosstab;


"Darin Connors" wrote:



"KARL DEWEY" wrote:

I think a crosstab query will do what you want.
TRANSFORM Count(DarinConnors.Quarter) AS CountOfQuarter
SELECT DarinConnors.Region, DarinConnors.Source, DarinConnors.Type
FROM DarinConnors
GROUP BY DarinConnors.Region, DarinConnors.Source, DarinConnors.Type
PIVOT "Q " & [Quarter];


I was recording quarter twice in case their was an easier method to
display
the data.

I used "Crosstab Query Wizzard and basically created exactly that.

Now one last question. The crosstab query leaves the field blank (null)
where there is no data. Is there a way to force a default 0 in the query
or
on the subsequent report?

Thanks for your help.