Crosstab query question

[jb]

Query Help Please-

I want to make:

name date value
lee sept 40
lee oct 27
lee nov 50
dan dec 50
jen jan 30

look like:

name sept oct nov dec jan
lee 40 27 50 0 0
dan 0 0 0 50 0
jen 0 0 0 0 30

I have a crosstab query that puts all of the months as their own field
heading, but I do not need to calculate values. Is there another type
of query that I should be using?

[David Schofield]

Re: Crosstab query question

On 28 Sep 2004 12:04:42 -0700, getyourbiglobst er@hotmail.com (jb)
wrote:
[color=blue]
>Query Help Please-
>
>I want to make:
>
>name date value
>lee sept 40
>lee oct 27
>lee nov 50
>dan dec 50
>jen jan 30
>
>look like:
>
>name sept oct nov dec jan
>lee 40 27 50 0 0
>dan 0 0 0 50 0
>jen 0 0 0 0 30
>
>I have a crosstab query that puts all of the months as their own field
>heading, but I do not need to calculate values. Is there another type
>of query that I should be using?[/color]

Hi

If your original table doesn't have overlaps in the sense that no two
records have the same name and date then the crosstab will do what you
want, eg if you use the sum or the max aggregate function it won't
make any difference it will just give you the one value for this pair.

Note that the crosstab will give nulls where you have zeros, if you
must have zeros you could use the nz function. Also the headings will
be in aphabetic order unless you set them up otherwise; For example,
doing both these things

TRANSFORM nz(Max(Table1.v alue),0) AS MaxOfvalue
SELECT Table1.name
FROM Table1
GROUP BY Table1.name
PIVOT Table1.date In ("sept","oct"," nov","dec","jan ");

This sort of thing (a pseudo crosstab) is often required but the
fathers of sql seem to have forgotten about it. If they hadn't done,
this sort of pivot would be updateable, something which at present you
have to do in code.

David