Partition Function - not displaying empty "groups" - Please HELP!!!!

  • Thread starter Thread starter cxc
  • Start date Start date
C

cxc

Hi all,

Here is my issue: let's assume that I have a dataset with 2 fields:
"ID" and "score".

ID Score
1 2
2 11
3 33
4 37
5 42

What I am trying to do is to "group" similar scores together and know
how many instances belong to each group.

The output that I want is below:

Score #ID
0-10 1
11-20 1
21-30 0
31-40 2
41-50 1

Now the issue that I have is that when I use the partition function in
access, it basically "discard" the empty groups (in this case the
"21-30" group) and does not display it at all. My output being:

Score #ID
0-10 1
11-20 1
31-40 2
41-50 1

Of course the obvious solution is to then use iif statements to
generate these groups, but if I need to generate let say 1000 groups
(e.g. from 0 to 1 million by groups of 1000) the iif statement would
take forever to write and it would be too complex anyway... So I do
need an automated way to do this.

Also, the dataset is too big (~1 million record) to fit into Excel, so
I can really use Excel.

Anyone has a suggestion on how to work around this?

Thanks in advance!
 
Do this with records and a little SQL. Create a table of score groups:
tblScoreGroups
=============
MinScore MaxScore Title
0 10 0-10
11 20 11-20
21 30 21-30
....

Create a query with you table and tblScoreGroups and set the criteria under
the Score field of:
Between [MinScore] And [MaxScore]

You can group by MinScore and Title while counting ID.
 
Duane, thanks for the answer. Not sure how this can helps with large
number of score groups though: if I have 1000 different score groups (0
to 1million by groups of 1000) , there is no way I can create that
score group table by hand... Any suggestion?

Thank you!



Duane said:
Do this with records and a little SQL. Create a table of score groups:
tblScoreGroups
=============
MinScore MaxScore Title
0 10 0-10
11 20 11-20
21 30 21-30
...

Create a query with you table and tblScoreGroups and set the criteria under
the Score field of:
Between [MinScore] And [MaxScore]

You can group by MinScore and Title while counting ID.
--
Duane Hookom
MS Access MVP

cxc said:
Hi all,

Here is my issue: let's assume that I have a dataset with 2 fields:
"ID" and "score".

ID Score
1 2
2 11
3 33
4 37
5 42

What I am trying to do is to "group" similar scores together and know
how many instances belong to each group.

The output that I want is below:

Score #ID
0-10 1
11-20 1
21-30 0
31-40 2
41-50 1

Now the issue that I have is that when I use the partition function in
access, it basically "discard" the empty groups (in this case the
"21-30" group) and does not display it at all. My output being:

Score #ID
0-10 1
11-20 1
31-40 2
41-50 1

Of course the obvious solution is to then use iif statements to
generate these groups, but if I need to generate let say 1000 groups
(e.g. from 0 to 1 million by groups of 1000) the iif statement would
take forever to write and it would be too complex anyway... So I do
need an automated way to do this.

Also, the dataset is too big (~1 million record) to fit into Excel, so
I can really use Excel.

Anyone has a suggestion on how to work around this?

Thanks in advance!
Click to expand...
Click to expand...
 
You can create tblScoreGroups by hand rather quickly in Excel. All it takes
is four entries and the ability to select and drag down.
Col A ColB
1 10
11 21
Select the four numbers, grab the corner selector and drag down.

Now import that spreadsheet into your database.

If you want you can also create the third column. Although that is not
necessary since you can do that in an SQL statement as a calculated column.

Another possible way to handle this is to have a utility table of integers
(from 1 to 1000 or more) and then construct a query to act as your range
table

SELECT 1000*MyInteger as UpperLimit
, 1000*(MyInteger-1)+1 as LowerLimit
, CStr(1000*(MyInteger-1)+1) & " - " & 1000*MyInteger as SegmentName
FROM tblIntegers

Use that saved query as Douglas Steele mentioned or turn it into a make
table query and build your table.

cxc said:
Duane, thanks for the answer. Not sure how this can helps with large
number of score groups though: if I have 1000 different score groups (0
to 1million by groups of 1000) , there is no way I can create that
score group table by hand... Any suggestion?

Thank you!



Duane said:
Do this with records and a little SQL. Create a table of score groups:
tblScoreGroups
=============
MinScore MaxScore Title
0 10 0-10
11 20 11-20
21 30 21-30
...

Create a query with you table and tblScoreGroups and set the criteria
under
the Score field of:
Between [MinScore] And [MaxScore]

You can group by MinScore and Title while counting ID.
--
Duane Hookom
MS Access MVP

cxc said:
Hi all,

Here is my issue: let's assume that I have a dataset with 2 fields:
"ID" and "score".

ID Score
1 2
2 11
3 33
4 37
5 42

What I am trying to do is to "group" similar scores together and know
how many instances belong to each group.

The output that I want is below:

Score #ID
0-10 1
11-20 1
21-30 0
31-40 2
41-50 1

Now the issue that I have is that when I use the partition function in
access, it basically "discard" the empty groups (in this case the
"21-30" group) and does not display it at all. My output being:

Score #ID
0-10 1
11-20 1
31-40 2
41-50 1

Of course the obvious solution is to then use iif statements to
generate these groups, but if I need to generate let say 1000 groups
(e.g. from 0 to 1 million by groups of 1000) the iif statement would
take forever to write and it would be too complex anyway... So I do
need an automated way to do this.

Also, the dataset is too big (~1 million record) to fit into Excel, so
I can really use Excel.

Anyone has a suggestion on how to work around this?

Thanks in advance!
Click to expand...
Click to expand...
Click to expand...
 
My last line should have read
Use that saved query as ***Duane Hookom*** mentioned or turn it into a make
table query and build your table.

Apologies to whichever of the two might feel insulted by my confusion of the
names.

John Spencer said:
You can create tblScoreGroups by hand rather quickly in Excel. All it
takes is four entries and the ability to select and drag down.
Col A ColB
1 10
11 21
Select the four numbers, grab the corner selector and drag down.

Now import that spreadsheet into your database.

If you want you can also create the third column. Although that is not
necessary since you can do that in an SQL statement as a calculated
column.

Another possible way to handle this is to have a utility table of integers
(from 1 to 1000 or more) and then construct a query to act as your range
table

SELECT 1000*MyInteger as UpperLimit
, 1000*(MyInteger-1)+1 as LowerLimit
, CStr(1000*(MyInteger-1)+1) & " - " & 1000*MyInteger as SegmentName
FROM tblIntegers

Use that saved query as Douglas Steele mentioned or turn it into a make
table query and build your table.

cxc said:
Duane, thanks for the answer. Not sure how this can helps with large
number of score groups though: if I have 1000 different score groups (0
to 1million by groups of 1000) , there is no way I can create that
score group table by hand... Any suggestion?

Thank you!



Duane said:
Do this with records and a little SQL. Create a table of score groups:
tblScoreGroups
=============
MinScore MaxScore Title
0 10 0-10
11 20 11-20
21 30 21-30
...

Create a query with you table and tblScoreGroups and set the criteria
under
the Score field of:
Between [MinScore] And [MaxScore]

You can group by MinScore and Title while counting ID.
--
Duane Hookom
MS Access MVP

Hi all,

Here is my issue: let's assume that I have a dataset with 2 fields:
"ID" and "score".

ID Score
1 2
2 11
3 33
4 37
5 42

What I am trying to do is to "group" similar scores together and know
how many instances belong to each group.

The output that I want is below:

Score #ID
0-10 1
11-20 1
21-30 0
31-40 2
41-50 1

Now the issue that I have is that when I use the partition function in
access, it basically "discard" the empty groups (in this case the
"21-30" group) and does not display it at all. My output being:

Score #ID
0-10 1
11-20 1
31-40 2
41-50 1

Of course the obvious solution is to then use iif statements to
generate these groups, but if I need to generate let say 1000 groups
(e.g. from 0 to 1 million by groups of 1000) the iif statement would
take forever to write and it would be too complex anyway... So I do
need an automated way to do this.

Also, the dataset is too big (~1 million record) to fit into Excel, so
I can really use Excel.

Anyone has a suggestion on how to work around this?

Thanks in advance!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top