Grouping problem with 25M records

[mikko tainio via AccessMonster.com]

This my data:

Number, Type, Amount
1513, XX, 15
1513, YY, 10
1513, ZZ, 10
1351, QQ, -5
1351, AA, 10
1937, XX, 135
1937, YY, 124
1937, PP, 12

I'd like to create a query to group it like this (into 4 different columns):

Number, XX and YY, ZZ and QQ, AA
1513, 25, 10, 0
1351, 0, -5, 10
1937, 259, 0, 0

Note that the Type PP is not added in any of the lists

My database has only this one table and it has a total of 25M records: does
access still handle all that?

Any help would be appreciated!

 

[Guest]

It sounds like you want a cross-tab query. Just change the query type from a
Select query to a Crosstab query, and in the 'Crosstab' row of the query
designer set [Number] as a row heading, set [Type] as the column heading, and
use the function Sum(Amount) as the crosstab Value; (note that this also has
to be set as an expression in the 'Total' row of the query designer)

This will create as many columns are there are unique values in the [Type]
column. If you want to exclude records with a [Type] of "PP" then add a
criteria for the [Type] column that specifies Type<>"PP"

As to the ability to handle, 25M records would be no problem at all

Good luck,

TK

 

[Guest]

Thanks, would it still be possible to group the different columns so that
they would include the sum of 2 different kinds of "types"?

Like:
[Number], [Sum of XX and YY], [Sum of AA and PP], [ZZ]
1513, 25, 0, 10
1351 ... ...
and so on


thank you very much!

It sounds like you want a cross-tab query. Just change the query type from a
Select query to a Crosstab query, and in the 'Crosstab' row of the query
designer set [Number] as a row heading, set [Type] as the column heading, and
use the function Sum(Amount) as the crosstab Value; (note that this also has
to be set as an expression in the 'Total' row of the query designer)

This will create as many columns are there are unique values in the [Type]
column. If you want to exclude records with a [Type] of "PP" then add a
criteria for the [Type] column that specifies Type<>"PP"

As to the ability to handle, 25M records would be no problem at all

Good luck,

TK

This my data:

Number, Type, Amount
1513, XX, 15
1513, YY, 10
1513, ZZ, 10
1351, QQ, -5
1351, AA, 10
1937, XX, 135
1937, YY, 124
1937, PP, 12

I'd like to create a query to group it like this (into 4 different columns):

Number, XX and YY, ZZ and QQ, AA
1513, 25, 10, 0
1351, 0, -5, 10
1937, 259, 0, 0

Note that the Type PP is not added in any of the lists

My database has only this one table and it has a total of 25M records: does
access still handle all that?

Any help would be appreciated!

 

[Guest]

OK I think I've got it now; you want to selectively test for non-zero values
in certain combinations of columns. You have a few options, but the easiest
would probably be to build another query that uses the crosstab query as its
data source, using expressions to test the necessary columns using formulas
such as:

=Iif([XX]<>0 AND [YY]<>0,Sum([Amount],0)
=Iif([ZZ]<>0 AND [QQ]<>0,Sum([Amount],0)
=Iif([AA]<>0, Sum([Amount],0)

I can't test the syntax now but this should be close enough to get you going.

Good luck,

TK

Thanks, would it still be possible to group the different columns so that
they would include the sum of 2 different kinds of "types"?

Like:
[Number], [Sum of XX and YY], [Sum of AA and PP], [ZZ]
1513, 25, 0, 10
1351 ... ...
and so on


thank you very much!

It sounds like you want a cross-tab query. Just change the query type from a
Select query to a Crosstab query, and in the 'Crosstab' row of the query
designer set [Number] as a row heading, set [Type] as the column heading, and
use the function Sum(Amount) as the crosstab Value; (note that this also has
to be set as an expression in the 'Total' row of the query designer)

This will create as many columns are there are unique values in the [Type]
column. If you want to exclude records with a [Type] of "PP" then add a
criteria for the [Type] column that specifies Type<>"PP"

As to the ability to handle, 25M records would be no problem at all

Good luck,

TK

This my data:

Number, Type, Amount
1513, XX, 15
1513, YY, 10
1513, ZZ, 10
1351, QQ, -5
1351, AA, 10
1937, XX, 135
1937, YY, 124
1937, PP, 12

I'd like to create a query to group it like this (into 4 different columns):

Number, XX and YY, ZZ and QQ, AA
1513, 25, 10, 0
1351, 0, -5, 10
1937, 259, 0, 0

Note that the Type PP is not added in any of the lists

My database has only this one table and it has a total of 25M records: does
access still handle all that?

Any help would be appreciated!