Calculate Total/% in Query with Grouping

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Calculate Total/% in Query with Grouping

Author

Topic

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-10-07 : 03:16:16

Hello,

How are you today?

To generate this report:


CLASSIFICATION   	January	   February     March
HOTELS	                  829	    1462	 1374
VILLAS	                  177	     239	  258

I run the following SQL Statement:


SELECT Format([F_ARRV_DAT],"m") AS MonthOrder, 
Format([F_ARRV_DAT],"mmmm") AS [Month], 
[Final_Clean Query].[CLASSIFICATION], 
Count([Final_Clean Query].[TRAV-KEY]) AS [CountOfTRAV-KEY]
FROM [Final_Clean Query]
WHERE (((Format([F_ARRV_DAT],"yyyy"))=Year))
GROUP BY Format([F_ARRV_DAT],"m"), 
Format([F_ARRV_DAT],"mmmm"), 
[Final_Clean Query].[CLASSIFICATION]
HAVING ((([Final_Clean Query].[CLASSIFICATION])<>''));

which produces a result set which looks like:


MonthOrder	Month	CLASSIFICATION	CountOfTRAV-KEY

1	       January	  HOTELS	   829
1	       January	  VILLAS	   177
2	       February	  HOTELS	  1462
2	       February	  VILLAS 	   239

I wish to modify my report to produce the following:


CLASSIFICATION   	January	   February     March   Total  %
HOTELS	                  829	    1462	 1374   3665  84.5
VILLAS	                  177	     239	  258    674  15.5
Grand Total              1006       1701         1632   4339  100.0

Can I calculate the Totals and % in my query?

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-07 : 05:40:27

Let's say your last query is named 'q'. Create these three queries:

q1
=====
TRANSFORM sum([CountOfTRAV-KEY])
SELECT CLASSIFICATION, DSum("[CountOfTRAV-KEY]", "q",
"CLASSIFICATION=" & "'" & CLASSIFICATION & "'") AS Total,
100*Total/DSum("[CountOfTRAV-KEY]", "q") AS [%]
FROM q
GROUP BY CLASSIFICATION
PIVOT [Month] in ("January", "February", "March", "April");

q2
=====
TRANSFORM sum([CountOfTRAV-KEY])
SELECT "Grand Total" AS CLASSIFICATION,
DSum("[CountOfTRAV-KEY]", "q") AS Total, 100.00 AS [%]
FROM q
GROUP BY 1
PIVOT [Month] in ("January", "February", "March", "April");

qq - this query produces what you need
=====
select
CLASSIFICATION, [January], [February], [March], [April], Total, [%] from q1
UNION ALL
select
CLASSIFICATION, [January], [February], [March], [April], Total, [%] from q2;

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-10-07 : 13:54:01

Hello,

Thanks, that worked. The only challenge is that I receive the following error message:

The expression you entered as a query parameter produced this error: 'DbTest can't find the name 'Year' you entered in the expression'

How do I pass the year parameter to q?

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-10-07 : 17:20:06

Hello,

I modified the queries as follows:

q1


TRANSFORM sum([CountOfTRAV-KEY])
SELECT [Month], [CLASSIFICATION], DSum("[CountOfTRAV-KEY]", "q",
"[CLASSIFICATION]=" & "'" & [CLASSIFICATION] & "'") AS Total,
100*Total/DSum("[CountOfTRAV-KEY]", "q") AS [%]
FROM q
GROUP BY [CLASSIFICATION], [Month]
PIVOT Format([Month],"mmm");


TRANSFORM sum([CountOfTRAV-KEY])
SELECT "Grand Total" AS [CLASSIFICATION], DSum("[CountOfTRAV-KEY]", "q") AS Total, 
100.00 AS [%], [Month]
FROM q
GROUP BY 1, [Month]
PIVOT Format([Month],"mmm");


select
[CLASSIFICATION], [Month], Total, [%] from q1
UNION ALL select
[CLASSIFICATION], [Month], Total, [%] from q2;

The query runs and produces a result set which I require, but I am getting repeating values:


CLASSIFICATION	 Month	   Total        %
HOTELS	        February   3665	  38.1810605271382
HOTELS	        January    3665	  38.1810605271382
HOTELS	        March	   3665	  38.1810605271382

I tried using a distinct but Access is not saving this change to the query.

Any ideas how to prevent this?

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-07 : 22:22:04

>How do I pass the year parameter to q?

1. By writing this (e.g. 2003, NOT word 'Year') into popping Input Box.

2. Wrap this parameter in 'q' query text with square brackets.

3. Write into your query 'q', as the first line, the following:

PARAMETERS [Year] TEXT;

Try it again, without your amendments (you added pivoting field
into GROUP BY list, why?)

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-10-07 : 23:24:58

Hello,

I tried the changes (without the ammendments).

1. The error message appears twice, then I am prompted for the year, which I enter, then the error message appears 5 more times, then I receive an empty result set. If I hardcode the Year value, the query runs ok.

2. I made the ammendments because I want a situation where if I only have data for a few months, say January to March, then the other months wouldn't appear.

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-10-07 : 23:37:40

Hello,

Weird, I tried changing to the parameter name to [Year1], and got the same error message, but in addition, got:

The expression you entered as a query parameter produced this error: 'The object doesn't contain the Automation object 'Year1." This error appears a couple of times, and then the values start appearing in the result set. Actually if I scroll through the result set the values appear in a cell if I click in the cell, but then disapears when the cell loses focus.

Changed the parameter name back to Year and got the same error that I have just described.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-08 : 02:22:14

Sorry, blinton :) Only now I understood what you meant. Really,
D-functions are unwilling to work with parameterized queries...
Try this (qq query stays unchanged):

q1
TRANSFORM sum([CountOfTRAV-KEY])
SELECT CLASSIFICATION, sum([CountOfTRAV-KEY]) AS Total,
100*sum([CountOfTRAV-KEY])/(select sum([CountOfTRAV-KEY]) from q) AS [%]
FROM q
GROUP BY CLASSIFICATION
PIVOT [Month] in ("January", "February", "March", "April");

q2
TRANSFORM sum([CountOfTRAV-KEY])
SELECT "Grand Total" AS CLASSIFICATION,
sum([CountOfTRAV-KEY]) AS Total, 100.00 AS [%]
FROM q
GROUP BY 1
PIVOT [Month] in ("January", "February", "March", "April");

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-10-08 : 07:32:57

Hello,

Yes, that worked. Thanks a lot for your assistance.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-08 : 07:53:01

LOL
But now MS Access does not allow to remove IN(...) from
PIVOT [Month] in ("January", "February", "March", "April");

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-10-08 : 08:01:48

Hello,

Well the mechanism I was trying

PIVOT Format([Month],"mmm")

does it, but the repeating values are affecting the accuracy of the results returned. Will keep hacking (hacking is good, cracking is bad) away at it.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-08 : 08:21:40

Hm.. I still get this warning (with Format([Month],"mmm")):

Multi-level GROUP BY clause is not allowed in a subquery

Anyway, keep on hacking!! :)

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-10-08 : 19:21:01

Hello,

How could I modify the above to produce the following:



CLASSIFICATION 	      January	                Total          %

                2002  2003   Diff  %Chg       2002  2003   2002  2003 
HOTELS	         10    20     10    50         10    20     33.3  40
VILLAS	         20    30     10    33         20    30     66.6  60
Grand Total      30    50     20    66         30    50    100.0 100.0

The query which I use for Q would be:


SELECT Format([F_ARRV_DAT],"m") AS MonthOrder,
Format([F_ARRV_DAT],"mmmm") AS [Month],
[Final_Clean Tourists Query].[CLASSIFICATION CODE2], 
Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=PreviousYear,[Final_Clean Tourists Query].[TRAV-KEY])) 
AS PYEAR, 
Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=CurrentYear,[Final_Clean Tourists Query].[TRAV-KEY])) 
AS CYEAR, 
CYEAR- PYEAR AS [ABS Ch], 
((CYEAR- PYEAR)/PYEAR) AS [% Ch]

FROM [Final_Clean Tourists Query]
WHERE (((DatePart('yyyy',[F_ARRV_DAT],1,0))=[PreviousYear] 
Or (DatePart('yyyy',[F_ARRV_DAT],1,0))=[CurrentYear]))
GROUP BY Format([F_ARRV_DAT],"m"), Format([F_ARRV_DAT],"mmmm"),
[Final_Clean Tourists Query].[CLASSIFICATION CODE2]
HAVING ((([Final_Clean Tourists Query].[CLASSIFICATION CODE2])<>''));

which produces a result set like:


MonthOrder Month CLASSIFICATIONCODE2	PYEAR	CYEAR	ABS Ch	% Ch
3	  March	     HOTELS	        1374	1805	431    0.3136

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-15 : 14:05:57

Strange anti-relational construction: multi-level column heading...

All this for exporting into Excel?

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-10-15 : 14:45:28

Hello,

Well for the purpose of reporting, users wish to compare the change between the two years (and now I am hearing talk of comparing several years :) ).

Using the query above I actually get it done, but without calculating the Totals and % in my query.

The export to Excel part allows users to further interact with the reports produced, as well as taking advantage of the printing capabilities of this program.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-15 : 15:35:34

And is it still actual - calculating the Totals and % in the query?

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-10-15 : 16:07:26

Hello,

I am sorry, I don't understand your question.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-16 : 02:00:38

LOL & Sorry
I meant one of the meanings of 'actual' word used in Russian.
Should be: is your question still pressing / topical / urgent?

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-10-16 : 05:09:08

Hello,

Oh yes, it definitely is. I have at least 50 reports to generate which involve year on year comparisions (don't worry, I don't have to create 50 individual reports, based on user input the appropriate report is produced)

My result set returned is like this:


MonthOrder Month CLASSIFICATIONCODE2	PYEAR	CYEAR	ABS Ch	% Ch
3	  March	     HOTELS	        1374	1805	431    0.3136
3	  March	     VILLA	        1000	2000	1000   1
4	  April	     HOTELS	        1374	1805	431    0.3136
4	  April	     VILLA	        1000	2000	1000   1

So then I use a pivot table, where by placing the Month in the Column Drop Area, and PYEAR, CYEAR, ABS CH and %CH in the Detail DROP Area, the appropriate report is produced


CLASSIFICATION 	      January	                

               2002  2003   Diff %Chg        
HOTELS	        10    20     10    50       
VILLAS	        20    30     10    33       
Grand Total     30    50     20    66

Challenge being how to calculate the Grand Totals.


CLASSIFICATION 	      January	                Total          %

               2002  2003   Diff %Chg       2002  2003   2002  2003 
HOTELS	        10    20     10    50         10    20     33.3  40
VILLAS	        20    30     10    33         20    30     66.6  60
Grand Total     30    50     20    66         30    50    100.0 100.0

I guess one approach could be to sum PYEAR and CYEAR in Q1

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-16 : 06:25:16

Let's for the sake of simplification assume the following:

you have table 't' with the following fields: dt, cl, trav


dt              cl          trav
---------------------------------
11/11/2002      hotel       3
11/12/2002      hotel       5

Next thing. Is it right that when starting executing the query an user
must enter into input boxes the three following parameters:

[pyear], [cyear], [myMonth]

and the query should result like this:

CL           2002  2003   Diff  %Chg       2002  2003   2002   2003 
---------------------------------------------------------------------
HOTELS	     10    20     10    50         10    20      33.3   40
VILLAS	     20    30     10    33         20    30      66.6   60
Grand Total  30    50     20    66         30    50     100.0  100.0

Is it so?
Note I don't talk about a report, only about the query.

One more thing. Why should you enter PYEAR if you've entered CYEAR?
Is not PYEAR = CYEAR - 1 ???

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-10-16 : 07:51:42

Hello,

There isn't a need to enter the month, Format([dt],"mmmm") will produce the month.

A user can compare any two years, 1998 vs 2002 for example.

I don't think it would be possible to get the query result in that format, probably:


Month	      bl	PYEAR	CYEAR	ABS Ch	% Ch
January	     Hotel	3	4	1	0.33
January	     Villa	1	1	0	0
Grand Total             4       5       1       0.33

The Report writer would handle formating and displaying the above.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources