Crosstab query.... I think

[dark11984]

Hi i'm trying to get a count of all the different lengths of products in my table.
I have two columns in the table Site & MaxLength. I want to somehow break up the lengths into increments such as 0-7.999 metres, 8-9.999 metres, 10-12.999metres, 13-14.999 etc then get a count for how many times a length appears for each site.

My Table
Site MaxLength
A 1
A 12
A 7.5
B 10
B 8
C 7
C 9.1

What i want my query to look like.

Site|0-7.999|8-9.999|10-12.999|13-14.999
A |2 |0 |1 |0
B |0 |1 |1 |0
C |1 |1 |0 |0

I'm not sure if i've been clear enough, let me know if you want more info.

Cheers

 

[ajetrumpet]

i don't think you can do this with a crosstab because you need an actual table field to pivot on...or be across the top. You don't have the ranges in the table I don't think. do you ?

 

[mcalex]

Yes, you can, but first make a column in your query to match the ranges you want and use iif statements to populate this from the MaxLength field. This is your Column Heading field

Then, create another column in the query which has the new column you calc'ed as its field. This is your Value field

when that runs you should only have three rows in your query, one for each site.

hth,
mcalex

 

[raskew]

Hi -

Try this, substituting table/field names as necessary:

TRANSFORM nz(Count(Switch([[COLOR="Red"]MaxLen[/COLOR]]<8,"A",[[COLOR="Red"]MaxLen[/COLOR]]<10,"B",[[COLOR="Red"]MaxLen[/COLOR]]<13,"C",True,"D")),0) AS Cat
SELECT
    [COLOR="Red"]Table2[/COLOR].[COLOR="Red"]Site[/COLOR]
FROM
   [COLOR="Red"]Table2[/COLOR]
GROUP BY
   [COLOR="Red"]Table2[/COLOR].[COLOR="Red"]Site[/COLOR]
PIVOT Switch([[COLOR="Red"]MaxLen[/COLOR]]<8,"<8",[[COLOR="Red"]MaxLen[/COLOR]]<10,"<10",[[COLOR="Red"]MaxLen[/COLOR]]<13,"<13",True,"<15");

HTH - Bob

 

[gemma-the-husky]

(gawd, this is slow today)

here's a most excellent alternative

Access has a NATIVE function called PARTITION that does exactly what you want - splits the data into segmented groups

the only thing is all the groups are the same size in partition

so partition(2) (this syntax is not correct)

will split into 0-2, 3-4, 5-6 etc

 

[raskew]

Dave -

You're absolutely correct -- the Partition() function is the way to go,
...provided you have even spacing between the various segments.

Given the OP's problem, Partition() won't cut it unless you modify the
segments, thus returning results that the OP doesn't want/need.

Thus, the Switch() function which allows you to tailor the segments to meet
the OP's requirement.

Best wishes - Bob

 

[dark11984]

Thanks guys, the switch function worked great!