Adding Calcs to a Crosstab? Or Form?

[carriey]

I have finally figured out my first crosstab query and it does about half of
what I need it to do. I get a table that I have put into a form which
displays the number of Enforcement Subtypes per Enforcement Category for each
month. There is then a total number of Inspections at the far right per
catgory.

I still need a total for each month along the bottom (as this would be the
total number of Inspections for the month) and I need each Quarter to total
as well. Ie:

Subtype Categoty Jan Feb March... Total Q1 Q2 Q3 Q4
Wellsite Low Risk 1 5 3 9 9

I have tried a number of different things that just haven't worked and I
suspect that I just can't add them to the crosstab so, I have tried adding
calculations on to the form but I am obviously not really getting it.
Because the months come up in the query as 1, 2, 3.....I have tried even just
adding them in an expression but it doesn't recognize the numbers. Any
advice would be greatly appreciated.

TRANSFORM CLng(NZ(Count([Qry_Count_Inspections_2.Inspection_Date]),0)) AS
CountOfInspection_Date
SELECT Qry_Count_Inspections_2.Enforcement_Subtype,
Qry_Count_Inspections_2.Enf_Category,
Count(Qry_Count_Inspections_2.Inspection_Date) AS [Total Of Inspections_Date]
FROM Qry_Count_Inspections_2
GROUP BY Qry_Count_Inspections_2.Enforcement_Subtype,
Qry_Count_Inspections_2.Enf_Category
PIVOT Qry_Count_Inspections_2.Month;

Additionally, I know that the query currently only goes up to 6 for the
months because there have been no entries in the other months. I put fields
on my form for 7 - 12 because I would rather not have to edit the form every
month as there are multiple users using their own front ends. Of course each
of these months comes up with #Name? I can probably get away with explaining
to the users that until an inspection date is entered for a month, this is
how it will appear but it doesn't look very nice. Is there any way to make
these displays 0's too?

Thanks so much!

 

[KARL DEWEY]

To do all that you ask you need two crosstab queries - one for monthly and
another for quarters.
Use this as you PIVOT in the monthly crosstab query --
PIVOT Format([Qry_Count_Inspections_2].[[Inspection_Date], "mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

For the quarterly use this --
PIVOT Format([Qry_Count_Inspections_2].[[Inspection_Date], "q");

To get your monthly and quarterly totals at the bottom do it in a report
footer.

 

[carriey]

Thanks a lot Karl - It makes total sense to have 2 crosstabs - I never would
have figured out this Pivot Statement though! The only problem is that when
I try to run it, I now get a Syntax Error in my Transform Statement. Any
idea how to correct that?

To do all that you ask you need two crosstab queries - one for monthly and
another for quarters.
Use this as you PIVOT in the monthly crosstab query --
PIVOT Format([Qry_Count_Inspections_2].[[Inspection_Date], "mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

For the quarterly use this --
PIVOT Format([Qry_Count_Inspections_2].[[Inspection_Date], "q");

To get your monthly and quarterly totals at the bottom do it in a report
footer.
--
KARL DEWEY
Build a little - Test a little

I have finally figured out my first crosstab query and it does about half of
what I need it to do. I get a table that I have put into a form which
displays the number of Enforcement Subtypes per Enforcement Category for each
month. There is then a total number of Inspections at the far right per
catgory.

I still need a total for each month along the bottom (as this would be the
total number of Inspections for the month) and I need each Quarter to total
as well. Ie:

Subtype Categoty Jan Feb March... Total Q1 Q2 Q3 Q4
Wellsite Low Risk 1 5 3 9 9

I have tried a number of different things that just haven't worked and I
suspect that I just can't add them to the crosstab so, I have tried adding
calculations on to the form but I am obviously not really getting it.
Because the months come up in the query as 1, 2, 3.....I have tried even just
adding them in an expression but it doesn't recognize the numbers. Any
advice would be greatly appreciated.

TRANSFORM CLng(NZ(Count([Qry_Count_Inspections_2.Inspection_Date]),0)) AS
CountOfInspection_Date
SELECT Qry_Count_Inspections_2.Enforcement_Subtype,
Qry_Count_Inspections_2.Enf_Category,
Count(Qry_Count_Inspections_2.Inspection_Date) AS [Total Of Inspections_Date]
FROM Qry_Count_Inspections_2
GROUP BY Qry_Count_Inspections_2.Enforcement_Subtype,
Qry_Count_Inspections_2.Enf_Category
PIVOT Qry_Count_Inspections_2.Month;

Additionally, I know that the query currently only goes up to 6 for the
months because there have been no entries in the other months. I put fields
on my form for 7 - 12 because I would rather not have to edit the form every
month as there are multiple users using their own front ends. Of course each
of these months comes up with #Name? I can probably get away with explaining
to the users that until an inspection date is entered for a month, this is
how it will appear but it doesn't look very nice. Is there any way to make
these displays 0's too?

Thanks so much!

 

[KARL DEWEY]

I ran it and found an extra open bracket in the PIVOT.
What is the exact error message?

--
KARL DEWEY
Build a little - Test a little

Thanks a lot Karl - It makes total sense to have 2 crosstabs - I never would
have figured out this Pivot Statement though! The only problem is that when
I try to run it, I now get a Syntax Error in my Transform Statement. Any
idea how to correct that?

To do all that you ask you need two crosstab queries - one for monthly and
another for quarters.
Use this as you PIVOT in the monthly crosstab query --
PIVOT Format([Qry_Count_Inspections_2].[[Inspection_Date], "mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

For the quarterly use this --
PIVOT Format([Qry_Count_Inspections_2].[[Inspection_Date], "q");

To get your monthly and quarterly totals at the bottom do it in a report
footer.
--
KARL DEWEY
Build a little - Test a little

I have finally figured out my first crosstab query and it does about half of
what I need it to do. I get a table that I have put into a form which
displays the number of Enforcement Subtypes per Enforcement Category for each
month. There is then a total number of Inspections at the far right per
catgory.

I still need a total for each month along the bottom (as this would be the
total number of Inspections for the month) and I need each Quarter to total
as well. Ie:

Subtype Categoty Jan Feb March... Total Q1 Q2 Q3 Q4
Wellsite Low Risk 1 5 3 9 9

I have tried a number of different things that just haven't worked and I
suspect that I just can't add them to the crosstab so, I have tried adding
calculations on to the form but I am obviously not really getting it.
Because the months come up in the query as 1, 2, 3.....I have tried even just
adding them in an expression but it doesn't recognize the numbers. Any
advice would be greatly appreciated.

TRANSFORM CLng(NZ(Count([Qry_Count_Inspections_2.Inspection_Date]),0)) AS
CountOfInspection_Date
SELECT Qry_Count_Inspections_2.Enforcement_Subtype,
Qry_Count_Inspections_2.Enf_Category,
Count(Qry_Count_Inspections_2.Inspection_Date) AS [Total Of Inspections_Date]
FROM Qry_Count_Inspections_2
GROUP BY Qry_Count_Inspections_2.Enforcement_Subtype,
Qry_Count_Inspections_2.Enf_Category
PIVOT Qry_Count_Inspections_2.Month;

Additionally, I know that the query currently only goes up to 6 for the
months because there have been no entries in the other months. I put fields
on my form for 7 - 12 because I would rather not have to edit the form every
month as there are multiple users using their own front ends. Of course each
of these months comes up with #Name? I can probably get away with explaining
to the users that until an inspection date is entered for a month, this is
how it will appear but it doesn't look very nice. Is there any way to make
these displays 0's too?

Thanks so much!

 

[carriey]

Me Again - I retyped it from scratch and no more syntax error so maybe I had
the extra bracket too. This is exactly what I was looking for. Thanks so
much!!!

I ran it and found an extra open bracket in the PIVOT.
What is the exact error message?

--
KARL DEWEY
Build a little - Test a little

Thanks a lot Karl - It makes total sense to have 2 crosstabs - I never would
have figured out this Pivot Statement though! The only problem is that when
I try to run it, I now get a Syntax Error in my Transform Statement. Any
idea how to correct that?

To do all that you ask you need two crosstab queries - one for monthly and
another for quarters.
Use this as you PIVOT in the monthly crosstab query --
PIVOT Format([Qry_Count_Inspections_2].[[Inspection_Date], "mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

For the quarterly use this --
PIVOT Format([Qry_Count_Inspections_2].[[Inspection_Date], "q");

To get your monthly and quarterly totals at the bottom do it in a report
footer.
--
KARL DEWEY
Build a little - Test a little


:

I have finally figured out my first crosstab query and it does about half of
what I need it to do. I get a table that I have put into a form which
displays the number of Enforcement Subtypes per Enforcement Category for each
month. There is then a total number of Inspections at the far right per
catgory.

I still need a total for each month along the bottom (as this would be the
total number of Inspections for the month) and I need each Quarter to total
as well. Ie:

Subtype Categoty Jan Feb March... Total Q1 Q2 Q3 Q4
Wellsite Low Risk 1 5 3 9 9

I have tried a number of different things that just haven't worked and I
suspect that I just can't add them to the crosstab so, I have tried adding
calculations on to the form but I am obviously not really getting it.
Because the months come up in the query as 1, 2, 3.....I have tried even just
adding them in an expression but it doesn't recognize the numbers. Any
advice would be greatly appreciated.

TRANSFORM CLng(NZ(Count([Qry_Count_Inspections_2.Inspection_Date]),0)) AS
CountOfInspection_Date
SELECT Qry_Count_Inspections_2.Enforcement_Subtype,
Qry_Count_Inspections_2.Enf_Category,
Count(Qry_Count_Inspections_2.Inspection_Date) AS [Total Of Inspections_Date]
FROM Qry_Count_Inspections_2
GROUP BY Qry_Count_Inspections_2.Enforcement_Subtype,
Qry_Count_Inspections_2.Enf_Category
PIVOT Qry_Count_Inspections_2.Month;

Additionally, I know that the query currently only goes up to 6 for the
months because there have been no entries in the other months. I put fields
on my form for 7 - 12 because I would rather not have to edit the form every
month as there are multiple users using their own front ends. Of course each
of these months comes up with #Name? I can probably get away with explaining
to the users that until an inspection date is entered for a month, this is
how it will appear but it doesn't look very nice. Is there any way to make
these displays 0's too?

Thanks so much!