Report Layout/Pivot Table Query

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

Report Layout/Pivot Table Query

Author

Topic

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-09-11 : 10:01:32

Hello,

How is everyone today?

Seeking some guidance. I am currently designing a report which I wish to display in the following format:

Country January February
2001 2002 Change %CH 2001 2002 Change %CH

USA 200 400 200 100% 200 400 200 100%
CANADA 100 50 50 50% 100 50 50 50%

I use the following query to obtain my results:

SELECT Format([F_ARRV_DAT],"m") AS MonthOrder, Format([F_ARRV_DAT],"mmmm") AS [Month], [Final_Clean Tourists Query].[REGIONS 2], Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=PreviousYear,[Final_Clean Tourists Query].[TRAV-KEY])) AS y2002, Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=CurrentYear,[Final_Clean Tourists Query].[TRAV-KEY])) AS y2003
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].[REGIONS 2]
HAVING ((([Final_Clean Tourists Query].[REGIONS 2])<>'' And ([Final_Clean Tourists Query].[REGIONS 2])<>'Other' And ([Final_Clean Tourists Query].[REGIONS 2])<>'Not stated'));

which gives me a result set looking like:
MonthOrder Month REGIONS 2 y2002 y2003
5 May USA 668 692
5 May CANADA 243 361

I get the report built ok, but the country of residence keeps repeating for every month column, which I don't want since it takes up space.

Is there anyway to prevent this?

As well, I took a look at using a pivot table in a form, which gives me the layout I want (well not sure how to do the necessary calculations using this but will address that next). However I don't see any page breaks when printing, I was wondering if there was a way of forcing a new page using the pivot table, similar to what happens in my report.

Feel free to respond with any requests for clarifications.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-11 : 10:19:12

can you repost using CODE tags with some formatting. I can't make sense of your output, or your SQL statement ....

thanks!

- Jeff

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-09-11 : 12:19:29

Hello,

Sure:

Query-

 
SELECT Format([F_ARRV_DAT],"m") AS MonthOrder, 
Format([F_ARRV_DAT],"mmmm") AS [Month], [Final_Clean Tourists Query].[REGIONS 2], 
Count
(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=PreviousYear,[Final_Clean Tourists Query].[TRAV-KEY])) 
AS y2002,
Count
(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=CurrentYear,[Final_Clean Tourists Query].[TRAV-KEY])) 
AS y2003
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].[REGIONS 2]
HAVING ((([Final_Clean Tourists Query].[REGIONS 2])<>'' 
And ([Final_Clean Tourists Query].[REGIONS 2])<>'Other'
And ([Final_Clean Tourists Query].[REGIONS 2])<>'Not stated'));

Results:


MonthOrder Month REGIONS 2 y2002 y2003
  5          May   USA      668   692
  5          May   CANADA   243   361


Country          January             February
        2001 2002 Change %CH   2001 2002 Change %CH

USA      200 400   200   100%   200 400   200   100%
CANADA   100  50    50    50%   100 50     50    50%

You can probably ignore the Having clause to simplify things.
Let me know if this is any better.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-11 : 12:23:07

can you clean up your SQL, out in a few CR/LF's so that the screen isn't 300 characters wide? (EDIT the previous post. if you leave it as is, the rest of the messages in this thread will have their formatting screwed up and be 4 screens wide).

and, now, format the RESULTS in the way you'd like the report to display them.

Thanks!

- Jeff

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-09-11 : 12:45:22

Hello,

Sorry about that. Hope that makes it clearer, I didn't want to break up the IIf clause but if necessary I can.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-11 : 13:33:49

Did you look at the hide duplicates property in the properties box?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-11 : 14:25:54

could it be that simple? I think maybe !

- Jeff

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-09-11 : 15:18:06

Hello,

I had tried that, but when I checked the Help:

"If the value of a control or the data it contains is the same as in the preceding record, the control is hidden."

So if I had

Canada
Canada

then only one Canada would be shown, but in this case my query takes care of grouping so this doesn't occur.

Would it be possible to set a variable,

1. which would be initially false
2. column would be written once variable is false
3. variable would then be flagged true
4. column would not be written subsequently

The only challenge to this approach would be when a new row is created on the page:

[CODE]
Country January February
2001 2002 Change %CH 2001 2002 Change %CH

USA 200 400 200 100% 200 400 200 100%
CANADA 100 50 50 50% 100 50 50 50%

March April
2001 2002 Change %CH 2001 2002 Change %CH

USA 200 400 200 100% 200 400 200 100%
CANADA 100 50 50 50% 100 50 50 50%

[/CODE]

One would need to track if a new row is created and flag the variable as false again.

Your thoughts?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-11 : 15:24:13

I may be dense, but I still have no idea what you are asking us. The last thing you showed us, is that what you WANT or what you are GETIING?

you can use VB and use the ON_FORMAT() events of various sections in your report to store and check variables. For example, in the header section, you reset the value, and on the detail section, you check the value with an if and take the appropriate action (such as saying "text.visible = false" or something along those lines)

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-11 : 15:28:25

You've got me...How does your query look like the final results that you are trying to display?

Also Access is a pretty powerful reporting tool..don't try and build a rocket ship..

There might even be 2 up page formatting..don't know..but check in to it.

my advise is to make your result set simple and clean and let the report do the formatting...

How, btw did you come up with the final report based on your query?

Or is that what you're striving for?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-09-11 : 16:18:21

Hello,

What I am currently getting is:

[Code]
Country January February
2001 2002 Change %CH 2001 2002 Change %CH

USA 200 400 200 100% 200 400 200 100%
CANADA 100 50 50 50% 100 50 50 50%
[/Code]

but I only have two months worth of data right now.

I was just saying that given the size of my paper (8.5 x 11 or x 14) and depending on the number of months that I have data on, then more than 2 months would fit on a page (actually I went into page set up and specified 3 columns, so 6 months should fit on the page if I have that much data)

"How, btw did you come up with the final report based on your query?"

Well it was the reverse, I built my query based on the final report that I wished to generate. Looking at the result set, my query counts and return the number of times that [Region 2] (USA for e.g) appears for a given month (May) in a given year (2002 or 2003).

When designing the report I then group on the month, and in the detail section I place [Region 2], [y2002] and [y2003], carrying out the necessary calculations to come up with the Change and % Change.

Will research the VB approach, thanks.

I hope this is clearer, but feel free to ask for clarifications.

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-11 : 16:37:14

quote:
Originally posted by blinton25

Hello,

What I am currently getting is:

Country January February
2001 2002 Change %CH 2001 2002 Change %CH

USA 200 400 200 100% 200 400 200 100%
CANADA 100 50 50 50% 100 50 50 50%

but I only have two months worth of data right now.

I was just saying that given the size of my paper (8.5 x 11 or x 14) and depending on the number of months that I have data on, then more than 2 months would fit on a page (actually I went into page set up and specified 3 columns, so 6 months should fit on the page if I have that much data)

"How, btw did you come up with the final report based on your query?"

Well it was the reverse, I built my query based on the final report that I wished to generate. Looking at the result set, my query counts and return the number of times that [Region 2] (USA for e.g) appears for a given month (May) in a given year (2002 or 2003).

When designing the report I then group on the month, and in the detail section I place [Region 2], [y2002] and [y2003], carrying out the necessary calculations to come up with the Change and % Change.

Will research the VB approach, thanks.

I hope this is clearer, but feel free to ask for clarifications.

Well I guess that's my point...it's like putting a tie on before your shirt...

I gues you could use IIF around the country, and if it's not one of the months you consider a the left margin label you could pass out an empty string...

IIF(MONTH,'January',COUNTRY)

or something....is there a SQL Server CASE in Access 2000 yet?

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-09-15 : 09:57:07

Hello,

For now I am using a pivot table to achieve the desired layout. Thanks for the assistance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources