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]

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

"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:O8****************@TK2MSFTNGP14.phx.gbl...

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

[Adam Turner via AccessMonster.com]

John wrote:

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

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.com
http://www.accessmonster.com/Uwe/For...ccess/200512/1

[John]

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

Regards
"Adam Turner via AccessMonster.com" <u14766@uwe> wrote in message
news:587f89dedb6cc@uwe...

John wrote:

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

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.com
http://www.accessmonster.com/Uwe/For...ccess/200512/1

[Duane Hookom]

You can create this with a multi-column layout.

--
Duane Hookom
MS Access MVP
--

"John" <Jo**@nospam.infovis.co.uk> wrote in message
news:O8****************@TK2MSFTNGP14.phx.gbl...

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

[Adam Turner via AccessMonster.com]

John wrote:

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

Regards

Hi

[quoted text clipped - 26 lines]

end as [March]
from MyTable

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.com
http://www.accessmonster.com/Uwe/For...ccess/200512/1

[Randy Harris]

"Adam Turner via AccessMonster.com" <u14766@uwe> wrote in message
news:587f89dedb6cc@uwe...

John wrote:

Hi

I need to "group" figures by month. I only need the totals for each month sodetail 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

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.com
http://www.accessmonster.com/Uwe/For...ccess/200512/1

Please pardon my confusion. This works with Jet SQL?

[pietlinden]

crosstab query

[Tim Marshall]

Adam Turner via AccessMonster.com wrote:

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

It should work fine for you.

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