Crosstab, Quarter Sum isn't working

  • Thread starter Thread starter carriey
  • Start date Start date
C

carriey

I have a Crosstab query that gives a sum of the total number of locations
(this is the field Locations) by month for each category. I also need it to
give the total number of locations for the quarter but that part of my query
isn't working. I think it is doing a count instead but I have tried a bunch
of stuff and can't seem to get the quarters to total properly. Can someone
see what I'm doing wrong?

TRANSFORM Sum(Qry_Count_Obligations_Locations.Locations) AS TotalLocations
SELECT Qry_Count_Obligations_Locations.Obligation_Type,
Sum(Qry_Count_Obligations_Locations.Locations) AS SumOfLocations,
Abs(Sum(DatePart("q",[Oblig_Date])=1)) AS Q1,
Abs(Sum(DatePart("q",[Oblig_Date])=2)) AS Q2,
Abs(Sum(DatePart("q",[Oblig_Date])=3)) AS Q3,
Abs(Sum(DatePart("q",[Oblig_Date])=4)) AS Q4
FROM Qry_Count_Obligations_Locations
GROUP BY Qry_Count_Obligations_Locations.Obligation_Type
PIVOT Format([Qry_Count_Obligations_Locations].[Oblig_Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Thank you so much!!!
 
I have a Crosstab query that gives a sum of the total number of locations
(this is the field Locations) by month for each category.  I also need it to
give the total number of locations for the quarter but that part of my query
isn't working.  I think it is doing a count instead but I have tried a bunch
of stuff and can't seem to get the quarters to total properly.  Can someone
see what I'm doing wrong?

TRANSFORM Sum(Qry_Count_Obligations_Locations.Locations) AS TotalLocations
SELECT Qry_Count_Obligations_Locations.Obligation_Type,
Sum(Qry_Count_Obligations_Locations.Locations) AS SumOfLocations,
Abs(Sum(DatePart("q",[Oblig_Date])=1)) AS Q1,
Abs(Sum(DatePart("q",[Oblig_Date])=2)) AS Q2,
Abs(Sum(DatePart("q",[Oblig_Date])=3)) AS Q3,
Abs(Sum(DatePart("q",[Oblig_Date])=4)) AS Q4
FROM Qry_Count_Obligations_Locations
GROUP BY Qry_Count_Obligations_Locations.Obligation_Type
PIVOT Format([Qry_Count_Obligations_Locations].[Oblig_Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Thank you so much!!!
Click to expand...



Can it be assumed that the underlying data structure is like this?

ObligationType,
Location,
ObligationDate

And further that the desired output would count the number of
ObligationType/Location pairs in each calendar quarter?

If so, consider:

SELECT ObligationType,
Location,
sum( iif( DatePart( "q", ObligationDate ) = 1, 1, null)) as
FirstQuarter,
sum( iif( DatePart( "q", ObligationDate ) = 2, 1, null)) as
SecondQuarter,
sum( iif( DatePart( "q", ObligationDate ) = 3, 1, null)) as
ThirdQuarter,
sum( iif( DatePart( "q", ObligationDate ) = 4, 1, null)) as
FourthQuarter,
Count(*) as [Total Obligation/Location Pairs]
From Table1
group by ObligationType, Location

If these assumptions are correct, would you need to perform the
TRANSFORM operation?
 
Hi Lou,

The table structure you indicated is correct but I don't think I made clear
that Locations is actually a number field that the user enters a total number
in for the record, ie. 1, 5, 314....

So what I need is a sum of the Locations Field by month and by quarter. The
way I have my query set up is giving a total of the number of locations by
category in Obligation_Type for the month but the quarter seems to be giving
a count of the Obligation_Type instead.

Thanks for your help!

Lou said:
I have a Crosstab query that gives a sum of the total number of locations
(this is the field Locations) by month for each category. I also need it to
give the total number of locations for the quarter but that part of my query
isn't working. I think it is doing a count instead but I have tried a bunch
of stuff and can't seem to get the quarters to total properly. Can someone
see what I'm doing wrong?

TRANSFORM Sum(Qry_Count_Obligations_Locations.Locations) AS TotalLocations
SELECT Qry_Count_Obligations_Locations.Obligation_Type,
Sum(Qry_Count_Obligations_Locations.Locations) AS SumOfLocations,
Abs(Sum(DatePart("q",[Oblig_Date])=1)) AS Q1,
Abs(Sum(DatePart("q",[Oblig_Date])=2)) AS Q2,
Abs(Sum(DatePart("q",[Oblig_Date])=3)) AS Q3,
Abs(Sum(DatePart("q",[Oblig_Date])=4)) AS Q4
FROM Qry_Count_Obligations_Locations
GROUP BY Qry_Count_Obligations_Locations.Obligation_Type
PIVOT Format([Qry_Count_Obligations_Locations].[Oblig_Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Thank you so much!!!
Click to expand...



Can it be assumed that the underlying data structure is like this?

ObligationType,
Location,
ObligationDate

And further that the desired output would count the number of
ObligationType/Location pairs in each calendar quarter?

If so, consider:

SELECT ObligationType,
Location,
sum( iif( DatePart( "q", ObligationDate ) = 1, 1, null)) as
FirstQuarter,
sum( iif( DatePart( "q", ObligationDate ) = 2, 1, null)) as
SecondQuarter,
sum( iif( DatePart( "q", ObligationDate ) = 3, 1, null)) as
ThirdQuarter,
sum( iif( DatePart( "q", ObligationDate ) = 4, 1, null)) as
FourthQuarter,
Count(*) as [Total Obligation/Location Pairs]
From Table1
group by ObligationType, Location

If these assumptions are correct, would you need to perform the
TRANSFORM operation?
Click to expand...
 
You are correct, your expression was counting. You wanted a sum of the
location field by quarter.

Your query does rely on you only getting one year's worth of data.

TRANSFORM Sum(Qry_Count_Obligations_Locations.Locations) AS TotalLocations
SELECT Qry_Count_Obligations_Locations.Obligation_Type,
Sum(Qry_Count_Obligations_Locations.Locations) AS SumOfLocations,
Sum(IIF(DatePart("q",[Oblig_Date])=1),[Locations],0) AS Q1,
Sum(IIF(DatePart("q",[Oblig_Date])=2),[Locations],0) AS Q2,
Sum(IIF(DatePart("q",[Oblig_Date])=3),[Locations],0) AS Q3,
Sum(IIF(DatePart("q",[Oblig_Date])=4),Locations,0) AS Q4
FROM Qry_Count_Obligations_Locations
GROUP BY Qry_Count_Obligations_Locations.Obligation_Type
PIVOT Format([Qry_Count_Obligations_Locations].[Oblig_Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Thanks John. I have changed my query to what you posted and I get an error
message:
Wrong number of arguments used with function in query expression
'Sum(IIF(DatePart("q",[Oblig_Date])=1),[Locations],0)'.

I repasted my sql below and I think it's the same as yours except that I
tried the Locations on Q4 both with and without [ ] . I'm not sure what
could be the matter?

TRANSFORM Sum(Qry_Count_Obligations_Locations.Locations) AS TotalLocations
SELECT Qry_Count_Obligations_Locations.Obligation_Type,
Sum(Qry_Count_Obligations_Locations.Locations) AS SumOfLocations,
Sum(IIF(DatePart("q",[Oblig_Date])=1),[Locations],0) AS Q1,
Sum(IIF(DatePart("q",[Oblig_Date])=2),[Locations],0) AS Q2,
Sum(IIF(DatePart("q",[Oblig_Date])=3),[Locations],0) AS Q3,
Sum(IIF(DatePart("q",[Oblig_Date])=4),[Locations],0) AS Q4
FROM Qry_Count_Obligations_Locations
GROUP BY Qry_Count_Obligations_Locations.Obligation_Type
PIVOT Format([Qry_Count_Obligations_Locations].[Oblig_Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

John Spencer said:
You are correct, your expression was counting. You wanted a sum of the
location field by quarter.

Your query does rely on you only getting one year's worth of data.

TRANSFORM Sum(Qry_Count_Obligations_Locations.Locations) AS TotalLocations
SELECT Qry_Count_Obligations_Locations.Obligation_Type,
Sum(Qry_Count_Obligations_Locations.Locations) AS SumOfLocations,
Sum(IIF(DatePart("q",[Oblig_Date])=1),[Locations],0) AS Q1,
Sum(IIF(DatePart("q",[Oblig_Date])=2),[Locations],0) AS Q2,
Sum(IIF(DatePart("q",[Oblig_Date])=3),[Locations],0) AS Q3,
Sum(IIF(DatePart("q",[Oblig_Date])=4),Locations,0) AS Q4
FROM Qry_Count_Obligations_Locations
GROUP BY Qry_Count_Obligations_Locations.Obligation_Type
PIVOT Format([Qry_Count_Obligations_Locations].[Oblig_Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a Crosstab query that gives a sum of the total number of locations
(this is the field Locations) by month for each category. I also need it to
give the total number of locations for the quarter but that part of my query
isn't working. I think it is doing a count instead but I have tried a bunch
of stuff and can't seem to get the quarters to total properly. Can someone
see what I'm doing wrong?

TRANSFORM Sum(Qry_Count_Obligations_Locations.Locations) AS TotalLocations
SELECT Qry_Count_Obligations_Locations.Obligation_Type,
Sum(Qry_Count_Obligations_Locations.Locations) AS SumOfLocations,
Abs(Sum(DatePart("q",[Oblig_Date])=1)) AS Q1,
Abs(Sum(DatePart("q",[Oblig_Date])=2)) AS Q2,
Abs(Sum(DatePart("q",[Oblig_Date])=3)) AS Q3,
Abs(Sum(DatePart("q",[Oblig_Date])=4)) AS Q4
FROM Qry_Count_Obligations_Locations
GROUP BY Qry_Count_Obligations_Locations.Obligation_Type
PIVOT Format([Qry_Count_Obligations_Locations].[Oblig_Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Thank you so much!!!
Click to expand...
Click to expand...
 
Sum(IIF(DatePart("q",[Oblig_Date])=1),[Locations],0) AS Q1,

Probably a misplaced parentheses, so let's build it one step at a time

DatePart("q",[Oblig_Date])=1

IIF(DatePart("q",[Oblig_Date])=1,[Locations],0)

Sum(IIF(DatePart("q",[Oblig_date])=1,[Locations],0))

And look there, we apparently had a closing parenthesis misplaced.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Thanks John. I have changed my query to what you posted and I get an error
message:
Wrong number of arguments used with function in query expression
'Sum(IIF(DatePart("q",[Oblig_Date])=1),[Locations],0)'.

I repasted my sql below and I think it's the same as yours except that I
tried the Locations on Q4 both with and without [ ] . I'm not sure what
could be the matter?

TRANSFORM Sum(Qry_Count_Obligations_Locations.Locations) AS TotalLocations
SELECT Qry_Count_Obligations_Locations.Obligation_Type,
Sum(Qry_Count_Obligations_Locations.Locations) AS SumOfLocations,
Sum(IIF(DatePart("q",[Oblig_Date])=1),[Locations],0) AS Q1,
Sum(IIF(DatePart("q",[Oblig_Date])=2),[Locations],0) AS Q2,
Sum(IIF(DatePart("q",[Oblig_Date])=3),[Locations],0) AS Q3,
Sum(IIF(DatePart("q",[Oblig_Date])=4),[Locations],0) AS Q4
FROM Qry_Count_Obligations_Locations
GROUP BY Qry_Count_Obligations_Locations.Obligation_Type
PIVOT Format([Qry_Count_Obligations_Locations].[Oblig_Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

John Spencer said:
You are correct, your expression was counting. You wanted a sum of the
location field by quarter.

Your query does rely on you only getting one year's worth of data.

TRANSFORM Sum(Qry_Count_Obligations_Locations.Locations) AS TotalLocations
SELECT Qry_Count_Obligations_Locations.Obligation_Type,
Sum(Qry_Count_Obligations_Locations.Locations) AS SumOfLocations,
Sum(IIF(DatePart("q",[Oblig_Date])=1),[Locations],0) AS Q1,
Sum(IIF(DatePart("q",[Oblig_Date])=2),[Locations],0) AS Q2,
Sum(IIF(DatePart("q",[Oblig_Date])=3),[Locations],0) AS Q3,
Sum(IIF(DatePart("q",[Oblig_Date])=4),Locations,0) AS Q4
FROM Qry_Count_Obligations_Locations
GROUP BY Qry_Count_Obligations_Locations.Obligation_Type
PIVOT Format([Qry_Count_Obligations_Locations].[Oblig_Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a Crosstab query that gives a sum of the total number of locations
(this is the field Locations) by month for each category. I also need it to
give the total number of locations for the quarter but that part of my query
isn't working. I think it is doing a count instead but I have tried a bunch
of stuff and can't seem to get the quarters to total properly. Can someone
see what I'm doing wrong?

TRANSFORM Sum(Qry_Count_Obligations_Locations.Locations) AS TotalLocations
SELECT Qry_Count_Obligations_Locations.Obligation_Type,
Sum(Qry_Count_Obligations_Locations.Locations) AS SumOfLocations,
Abs(Sum(DatePart("q",[Oblig_Date])=1)) AS Q1,
Abs(Sum(DatePart("q",[Oblig_Date])=2)) AS Q2,
Abs(Sum(DatePart("q",[Oblig_Date])=3)) AS Q3,
Abs(Sum(DatePart("q",[Oblig_Date])=4)) AS Q4
FROM Qry_Count_Obligations_Locations
GROUP BY Qry_Count_Obligations_Locations.Obligation_Type
PIVOT Format([Qry_Count_Obligations_Locations].[Oblig_Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Thank you so much!!!
Click to expand...
Click to expand...
Click to expand...
 
A million thank you's to you this morning! And not only for helping me make
this query work but for the break down too - that actually helped me to
understand a lot better.

John Spencer said:
Sum(IIF(DatePart("q",[Oblig_Date])=1),[Locations],0) AS Q1,

Probably a misplaced parentheses, so let's build it one step at a time

DatePart("q",[Oblig_Date])=1

IIF(DatePart("q",[Oblig_Date])=1,[Locations],0)

Sum(IIF(DatePart("q",[Oblig_date])=1,[Locations],0))

And look there, we apparently had a closing parenthesis misplaced.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Thanks John. I have changed my query to what you posted and I get an error
message:
Wrong number of arguments used with function in query expression
'Sum(IIF(DatePart("q",[Oblig_Date])=1),[Locations],0)'.

I repasted my sql below and I think it's the same as yours except that I
tried the Locations on Q4 both with and without [ ] . I'm not sure what
could be the matter?

TRANSFORM Sum(Qry_Count_Obligations_Locations.Locations) AS TotalLocations
SELECT Qry_Count_Obligations_Locations.Obligation_Type,
Sum(Qry_Count_Obligations_Locations.Locations) AS SumOfLocations,
Sum(IIF(DatePart("q",[Oblig_Date])=1),[Locations],0) AS Q1,
Sum(IIF(DatePart("q",[Oblig_Date])=2),[Locations],0) AS Q2,
Sum(IIF(DatePart("q",[Oblig_Date])=3),[Locations],0) AS Q3,
Sum(IIF(DatePart("q",[Oblig_Date])=4),[Locations],0) AS Q4
FROM Qry_Count_Obligations_Locations
GROUP BY Qry_Count_Obligations_Locations.Obligation_Type
PIVOT Format([Qry_Count_Obligations_Locations].[Oblig_Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

John Spencer said:
You are correct, your expression was counting. You wanted a sum of the
location field by quarter.

Your query does rely on you only getting one year's worth of data.

TRANSFORM Sum(Qry_Count_Obligations_Locations.Locations) AS TotalLocations
SELECT Qry_Count_Obligations_Locations.Obligation_Type,
Sum(Qry_Count_Obligations_Locations.Locations) AS SumOfLocations,
Sum(IIF(DatePart("q",[Oblig_Date])=1),[Locations],0) AS Q1,
Sum(IIF(DatePart("q",[Oblig_Date])=2),[Locations],0) AS Q2,
Sum(IIF(DatePart("q",[Oblig_Date])=3),[Locations],0) AS Q3,
Sum(IIF(DatePart("q",[Oblig_Date])=4),Locations,0) AS Q4
FROM Qry_Count_Obligations_Locations
GROUP BY Qry_Count_Obligations_Locations.Obligation_Type
PIVOT Format([Qry_Count_Obligations_Locations].[Oblig_Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


carriey wrote:
I have a Crosstab query that gives a sum of the total number of locations
(this is the field Locations) by month for each category. I also need it to
give the total number of locations for the quarter but that part of my query
isn't working. I think it is doing a count instead but I have tried a bunch
of stuff and can't seem to get the quarters to total properly. Can someone
see what I'm doing wrong?

TRANSFORM Sum(Qry_Count_Obligations_Locations.Locations) AS TotalLocations
SELECT Qry_Count_Obligations_Locations.Obligation_Type,
Sum(Qry_Count_Obligations_Locations.Locations) AS SumOfLocations,
Abs(Sum(DatePart("q",[Oblig_Date])=1)) AS Q1,
Abs(Sum(DatePart("q",[Oblig_Date])=2)) AS Q2,
Abs(Sum(DatePart("q",[Oblig_Date])=3)) AS Q3,
Abs(Sum(DatePart("q",[Oblig_Date])=4)) AS Q4
FROM Qry_Count_Obligations_Locations
GROUP BY Qry_Count_Obligations_Locations.Obligation_Type
PIVOT Format([Qry_Count_Obligations_Locations].[Oblig_Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Thank you so much!!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top