Horizontal grouping

[John]

Hi

I need to "group" figures by month. I only need the totals for each month so
detail is not required. My problem is that I need the months to appear
horizontally and each month's figure underneath it. Here is the example;

Jan Feb Mar ...... Dec
£2345.00 £1626.00 £9484.00 £23939.00

How can I achieve this?

Thanks

Regards

[Phil Hunt]

Re: Horizontal grouping

Have you tried the crosstab wizard under the new Query tab.

"John" <John@nospam.in fovis.co.uk> wrote in message
news:O8p$2I3%23 FHA.2040@TK2MSF TNGP14.phx.gbl. ..[color=blue]
> Hi
>
> I need to "group" figures by month. I only need the totals for each month
> so detail is not required. My problem is that I need the months to appear
> horizontally and each month's figure underneath it. Here is the example;
>
> Jan Feb Mar ...... Dec
> £2345.00 £1626.00 £9484.00 £23939.00
>
> How can I achieve this?
>
> Thanks
>
> Regards
>
>[/color]

[Adam Turner via AccessMonster.com]

Re: Horizontal grouping

John wrote:[color=blue]
>Hi
>
>I need to "group" figures by month. I only need the totals for each month so
>detail is not required. My problem is that I need the months to appear
>horizontally and each month's figure underneath it. Here is the example;
>
> Jan Feb Mar ...... Dec
>£2345.00 £1626.00 £9484.00 £23939.00
>
>How can I achieve this?
>
>Thanks
>[/color]

select case
when Total > 100 Then Total
else
''
end as [January],
case
when [Condition 2] Then Total
else
''
end as [February],
case
when [Condition 3] Then Total
else
''
end as [March]
from MyTable

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200512/1

[John]

Re: Horizontal grouping

Wow, it news for me that you can case within a select statement.

Regards


"Adam Turner via AccessMonster.c om" <u14766@uwe> wrote in message
news:587f89dedb 6cc@uwe...[color=blue]
> John wrote:[color=green]
>>Hi
>>
>>I need to "group" figures by month. I only need the totals for each month
>>so
>>detail is not required. My problem is that I need the months to appear
>>horizontall y and each month's figure underneath it. Here is the example;
>>
>> Jan Feb Mar ...... Dec
>>£2345.00 £1626.00 £9484.00 £23939.00
>>
>>How can I achieve this?
>>
>>Thanks
>>[/color]
>
> select case
> when Total > 100 Then Total
> else
> ''
> end as [January],
> case
> when [Condition 2] Then Total
> else
> ''
> end as [February],
> case
> when [Condition 3] Then Total
> else
> ''
> end as [March]
> from MyTable
>
> --
> Message posted via AccessMonster.c om
> http://www.accessmonster.com/Uwe/For...ccess/200512/1[/color]

[Duane Hookom]

Re: Horizontal grouping

You can create this with a multi-column layout.

--
Duane Hookom
MS Access MVP
--

"John" <John@nospam.in fovis.co.uk> wrote in message
news:O8p$2I3%23 FHA.2040@TK2MSF TNGP14.phx.gbl. ..[color=blue]
> Hi
>
> I need to "group" figures by month. I only need the totals for each month
> so detail is not required. My problem is that I need the months to appear
> horizontally and each month's figure underneath it. Here is the example;
>
> Jan Feb Mar ...... Dec
> £2345.00 £1626.00 £9484.00 £23939.00
>
> How can I achieve this?
>
> Thanks
>
> Regards
>
>[/color]

[Adam Turner via AccessMonster.com]

Re: Horizontal grouping

John wrote:[color=blue]
>Wow, it news for me that you can case within a select statement.
>
>Regards
>[color=green][color=darkred]
>>>Hi
>>>[/color][/color]
>[quoted text clipped - 26 lines][color=green]
>> end as [March]
>> from MyTable[/color][/color]

Sure...actually I just recently discovered this approach myself as I had a
very similar, almost identical, scenario.

It should work fine for you.

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200512/1

[Randy Harris]

Re: Horizontal grouping


"Adam Turner via AccessMonster.c om" <u14766@uwe> wrote in message
news:587f89dedb 6cc@uwe...[color=blue]
> John wrote:[color=green]
> >Hi
> >
> >I need to "group" figures by month. I only need the totals for each month[/color][/color]
so[color=blue][color=green]
> >detail is not required. My problem is that I need the months to appear
> >horizontally and each month's figure underneath it. Here is the example;
> >
> > Jan Feb Mar ...... Dec
> >£2345.00 £1626.00 £9484.00 £23939.00
> >
> >How can I achieve this?
> >
> >Thanks
> >[/color]
>
> select case
> when Total > 100 Then Total
> else
> ''
> end as [January],
> case
> when [Condition 2] Then Total
> else
> ''
> end as [February],
> case
> when [Condition 3] Then Total
> else
> ''
> end as [March]
> from MyTable
>
> --
> Message posted via AccessMonster.c om
> http://www.accessmonster.com/Uwe/For...ccess/200512/1[/color]

Please pardon my confusion. This works with Jet SQL?

[pietlinden@hotmail.com]

Re: Horizontal grouping

crosstab query

[Tim Marshall]

Re: Horizontal grouping

Adam Turner via AccessMonster.c om wrote:
[color=blue]
> Sure...actually I just recently discovered this approach myself as I had a
> very similar, almost identical, scenario.
>
> It should work fine for you.[/color]

The case statement is a (wonderful) ANSI 92 and later construct that is
supported by Oracle, and, I believe, MS SQL. However, I believe you may
have ANSI 92 enabled. This is found under options->Tables/Queries->SQL
COmpatible Syntax (ANSI 92) in the bottom right hand corner.

Otherwise, your solution will not work, as far as I can tell.

I'm not sure what effect the ANSI 92 compatibility has on Jet
performance or whatever. I work mainly in Oracle, so maybe someone else
can comment.

As an aside, When I first started with A2003 last year, I tried
switching to ANSI 92 compaitibility once but Jet SQL still demanded the
cumbersome ANSI join syntax and not the very much easier and older join
syntax of Oracle (called a Theta type join), so I switched back. The
theta joins make it so deliciously easy to write dynamic SQL with VBA code.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me