Chart

#1

Hi
i have created a chart based on a query

TRANSFORM Sum([Net ]) AS [SumOfNet] SELECT [Equip Code] FROM
[TblNet_potential joined area] GROUP BY [Equip Code] PIVOT
[weeknum];

this works fine puts weeknum in order ie 4 5 6 7 8 9

the question is when we move into another year how do i get the
weeknum to stay in weeknum order

ie 48 49 50 51 52 1 2

what i am getting is 1 2 48 49 50 51 52

#2

wouldn't something like
.... PIVOT [year], [weeknum]
do the job?

Michiel

"kevcar40" <kevcar40 (AT) btinternet (DOT) com> wrote

Quote:

Hi
i have created a chart based on a query

TRANSFORM Sum([Net ]) AS [SumOfNet] SELECT [Equip Code] FROM
[TblNet_potential joined area] GROUP BY [Equip Code] PIVOT
[weeknum];

this works fine puts weeknum in order ie 4 5 6 7 8 9

the question is when we move into another year how do i get the
weeknum to stay in weeknum order

ie 48 49 50 51 52 1 2

what i am getting is 1 2 48 49 50 51 52

--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

#3

On 13 May, 09:01, "Michiel Rapati-Kekkonen" <no-m... (AT) nonsense (DOT) zz>
wrote:

Quote:

wouldn't something like
... PIVOT [year], [weeknum]
do the job?

Michiel

"kevcar40" <kevca... (AT) btinternet (DOT) com> wrote in message

news:14915a56-e429-4ed7-bb44-cf5dbbbaff8a (AT) l28g2000yqd (DOT) googlegroups.com...

Hi
i have created a chart based on *a query

TRANSFORM Sum([Net ]) AS [SumOfNet] SELECT [Equip Code] FROM
[TblNet_potential joined area] * GROUP BY [Equip Code] PIVOT
[weeknum];

this works fine puts weeknum in order * ie * *4 *5 *6 * 7 *8 *9

the question is when we move into another year how do i get the
weeknum to stay in weeknum *order

ie *48 *49 *50 51 *52 * 1 * 2

what i am getting is * * 1 * *2 * *48 *49 *50 51 *52

--- news://freenews.netfront.net/ - complaints: n... (AT) netfront (DOT) net ---- Hide quoted text -

- Show quoted text -

i get syntax error when i put comma between fields

#4

nah, I don't know the right syntax, but what I wanted to point out is that
your query is obviously sorted on 'weeknum'
and that sorting on year and then on weeknum would give you the desired
result...

"kevcar40" <kevcar40 (AT) btinternet (DOT) com> wrote

On 13 May, 09:01, "Michiel Rapati-Kekkonen" <no-m... (AT) nonsense (DOT) zz>
wrote:

Quote:

wouldn't something like
... PIVOT [year], [weeknum]
do the job?

Michiel

"kevcar40" <kevca... (AT) btinternet (DOT) com> wrote in message

news:14915a56-e429-4ed7-bb44-cf5dbbbaff8a (AT) l28g2000yqd (DOT) googlegroups.com...

Hi
i have created a chart based on a query

TRANSFORM Sum([Net ]) AS [SumOfNet] SELECT [Equip Code] FROM
[TblNet_potential joined area] GROUP BY [Equip Code] PIVOT
[weeknum];

this works fine puts weeknum in order ie 4 5 6 7 8 9

the question is when we move into another year how do i get the
weeknum to stay in weeknum order

ie 48 49 50 51 52 1 2

what i am getting is 1 2 48 49 50 51 52

--- news://freenews.netfront.net/ - complaints: n... (AT) netfront (DOT) net ----
Hide quoted text -

- Show quoted text -

i get syntax error when i put comma between fields

--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

#5

On 13/05/2010 09:40:19, kevcar40 wrote:

Quote:

On 13 May, 09:01, "Michiel Rapati-Kekkonen" <no-m... (AT) nonsense (DOT) zz
wrote:
wouldn't something like
... PIVOT [year], [weeknum]
do the job?

Michiel

"kevcar40" <kevca... (AT) btinternet (DOT) com> wrote in message

news:14915a56-e429-4ed7-bb44-cf5dbbbaff8a (AT) l28g2000yqd (DOT) googlegroups.com...

Hi
i have created a chart based on *a query

TRANSFORM Sum([Net ]) AS [SumOfNet] SELECT [Equip Code] FROM
[TblNet_potential joined area] * GROUP BY [Equip Code] PIVOT
[weeknum];

this works fine puts weeknum in order * ie * *4 *5 *6 * 7
*8 *9

the question is when we move into another year how do i get the
weeknum to stay in weeknum *order

ie *48 *49 *50 51 *52 * 1 * 2

what i am getting is * * 1 * *2 * *48 *49 *50 51 *52

--- news://freenews.netfront.net/ - complaints: n... (AT) netfront (DOT) net ---- Hi
de quoted text -

- Show quoted text -

i get syntax error when i put comma between fields

Can't you add the year (DateText) and nomth so that you get "200950" "200951"
"200952" "201001" 201002" which should deal with your sort order
Then use Right(DateText,2) to extract the week no

Phil

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Chart

comp.databases.ms-access comp.databases.ms-access

Chart - 05-13-2010 , 02:47 AM

Re: Chart - 05-13-2010 , 03:01 AM

Re: Chart - 05-13-2010 , 03:40 AM

Re: Chart - 05-13-2010 , 10:21 AM

Re: Chart - 05-14-2010 , 07:47 AM