Creating Value Tiers

[Dave]

I'm trying to create tiers (deciles) based on total value
(rather than count). Hoping to place top value records in
Tier 1, medium value records in Tier 2 and Low Value
records in Tier 3. For example tier 1 = x% of rows and 33%
of total value, tier 2 = y% of rows and 33% of total value
and Tier 3 = z% of rows and 34% of total value.

Is there anyone out there that can help..??

Cheers
Dave

 

[Duane Hookom]

You can use a subquery in the criteria similar to
SELECT tblSchedule.*
FROM tblSchedule
WHERE schSchID In (SELECT TOP 33 PERCENT schSchID FROM tblSchedule ORDER BY
SchOrDate );

You will need to select different Orders etc.

 

[Dave]

Hi Duane, tried that but it didn't quite give me what i'm
after. I would like something similar to below;

Customer Value Running Total Tier
1 $100 $100 1
2 $100 $200 1
3 $75 $275 2
4 $65 $340 2
5 $60 $400 2
6 $45 $445 3
7 $45 $490 3
8 $40 $530 3
9 $35 $565 3
10 $35 $600 3

As you can see there are a different number of row's in
each tier - but they all Tiers contribute $200 in total
value.

At the moment I am using a DSum fuction, with the value in
decending order, then splitting based on 1/3 of the total
value. But as there are over 3Million records this is
taking about 5 days to run.

Is there anyone out there that know's of a more efficient
way of doing this?

CHeers
Dave

 

[Duane Hookom]

I don't recall anything in your original posting regarding a running sum. 3
million records with a DSum() will never be acceptable.
It might help to see your SQL. However, I would really question the value of
attempting this with Access tables and 3 million records.

 

[Guest]

I know it's not ideal (in fact probably won't be able to
provide anything) but it's all I have to work with. If
you can think of a way to complete without using a DSum()
or running total I'd really appreciate it.

 

[Duane Hookom]

I would think this would take a lot of trial and error since I haven't tried
any of this with such a large table. Please provide your current structure
and sql as well as your requirements. You mentioned first just dividing into
3 groups and later using DSum() for some reason. Can you tell use exactly
what you are hoping to accomplish?

 

[Dave]

Hi Duane,

table structure is as follows;
Row Number (auto count field)
ROW_ID (unique customer identifier)
CCCCV (customer value - currency format)
Tier (currently blank)

What I am hoping to achieve is similar (but on a much
larger scale) to the table below;

Customer Value Row Number Tier
11235 $100 1 1
21534 $100 2 1
15363 $75 3 2
58634 $65 4 2
51654 $60 5 2
12354 $45 6 3
15691 $45 7 3
22236 $40 8 3
89999 $35 9 3
10259 $35 10 3

As you can see in this example Tier 1, Tier 2 and Tier 3
all contain $200 total customer value.

The Row Number in ascending format is the same as
decending order (based on CCCCV).

I agree that DSum() isn't the way to go, and as such don't
really have any SQL that I can send.

Help is really appreciated.

Cheers
Dave

PS - email address is (e-mail address removed)

-----Original Message-----

I would think this would take a lot of trial and error
since I haven't tried any of this with such a large table.
Please provide your current structure and sql as well as
your requirements. You mentioned first just dividing into
3 groups and later using DSum() for some reason. Can you
tell use exactly what you are hoping to accomplish?

 

[Duane Hookom]

I would first create a query that returns 1/3 of the total values.
SELECT Sum([CCCCV])/3 as OneThird
FROM tblA;
It might word best to place this value into a table with a single field and
record tblOneThird.OneThird. This would save calculation resources later.
You then need a query that calculates a running sum:
SELECT tblA.*,
(SELECT Sum(CCCCV)
FROM tblA A
WHERE A.CCCCV >= tblA.CCCCV
AND A.RowNumber <=tblA.RowNumber) as RunValue
FROM tblA;
Then a third query can be made with the above query/tables that does an
integer divide of the RunValue by the OneThird field.

 

[Dave]

thanks for that Duane. Running like a dream, (if only it
wasn't such a long dream ;-)

cheers
dave

-----Original Message-----
I would first create a query that returns 1/3 of the total values.
SELECT Sum([CCCCV])/3 as OneThird
FROM tblA;
It might word best to place this value into a table with a single field and
record tblOneThird.OneThird. This would save calculation resources later.
You then need a query that calculates a running sum:
SELECT tblA.*,
(SELECT Sum(CCCCV)
FROM tblA A
WHERE A.CCCCV >= tblA.CCCCV
AND A.RowNumber <=tblA.RowNumber) as RunValue
FROM tblA;
Then a third query can be made with the above query/tables that does an
integer divide of the RunValue by the OneThird field.

--
Duane Hookom
MS Access MVP

Hi Duane,

table structure is as follows;
Row Number (auto count field)
ROW_ID (unique customer identifier)
CCCCV (customer value - currency format)
Tier (currently blank)

What I am hoping to achieve is similar (but on a much
larger scale) to the table below;

Customer Value Row Number Tier
11235 $100 1 1
21534 $100 2 1
15363 $75 3 2
58634 $65 4 2
51654 $60 5 2
12354 $45 6 3
15691 $45 7 3
22236 $40 8 3
89999 $35 9 3
10259 $35 10 3

As you can see in this example Tier 1, Tier 2 and Tier 3
all contain $200 total customer value.

The Row Number in ascending format is the same as
decending order (based on CCCCV).

I agree that DSum() isn't the way to go, and as such don't
really have any SQL that I can send.

Help is really appreciated.

Cheers
Dave

PS - email address is (e-mail address removed)

I would think this would take a lot of trial and error
since I haven't tried any of this with such a large table.
Please provide your current structure and sql as well as
your requirements. You mentioned first just dividing into
3 groups and later using DSum() for some reason. Can you
tell use exactly what you are hoping to accomplish? DSum
() row's
in this
is wrote
in

.