Code to calculate quintiles

[Gillian]

Hi

My colleague at work uses an excel sheet to create Recency/Frequency/Value
(RFVs) spreadsheets.

I wanted to know if there was a way i can do this in access.

I have a grouped query and i wanted to know if it is possible to write code
which calculates which records fall in the first 20% of total records and
then append a 5 into the Recency field, then 4 for the next 20%, 3 for the
next 20%, 2 for the next 20% and then 1 for the final 20%.

Off the top of my head i can't think of a simple solution but it is
something my colleague does on a daily basis to being able to automate it
would save us time.

Thanks for any help
Gillian

 

[Lisab]

here is the query to select top percent

SELECT TOP 20 PERCENT * FROM tableName

 

[JimBurke via AccessMonster.com]

First let's see if I understand what you're doing. If, for example,
you had 10 records and they were being categorized by some
value A, then you're loking for something like:

A Recency (?)
row 1 5 1
row 2 7 1
row 3 8 2
row 4 8 2
row 5 11 3
row 6 13 3
row 7 14 4
row 8 15 4
row 9 29 5
row 10 33 5

If this is what you're looking for, it's easy enought to do, but the logic
you'd use depends what
you need to if:

- there are multiple values that are the same and they can't be grouped
together. would you care which ones went inot which group (I don't know if
you have other values that go along with the
main value that things are being grouped by and if it would matter where the
'rows' went in the grouping based on those other values).i.e., in the above
example, if the first four rows all had the same value, and there other
values that go along with the main grouping value that may be different from
each other, would you care which ones went into group 1 and group 2?

- the number of records that go into each group won't always be the same. i.e.
if the above example had 12 rows instead of 10 you woudn't have 5 groups of 2
- there'd be two extra rows. How do want those distributed? In a case like
that would you want 3 groups of 2 and 2 groups of 3? Does it matter which
groups get the extra row?

If I'm on the right track, and if you can answer those questions, I can give
you some simple logic that will do it.

 

[Gillian]

Thanks for the responses my colleague/boss has decided that i've not to
bother doing this in access.

Such is life i suppose

 

[Jay Finch]

Yes, this is exactly what I also need to do. Can you post the logic? Thanks!



JimBurke via AccessMonster.com wrote:

Re: Code to calculate quintiles
22-Mar-09

First let's see if I understand what you're doing. If, for example,
you had 10 records and they were being categorized by som
value A, then you're loking for something like

A Recency (?
row 1 5
row 2 7
row 3 8
row 4 8
row 5 11
row 6 13
row 7 14
row 8 15
row 9 29
row 10 33

If this is what you're looking for, it's easy enought to do, but the logi
you'd use depends wha
you need to if

- there are multiple values that are the same and they can't be groupe
together. would you care which ones went inot which group (I don't know i
you have other values that go along with the
main value that things are being grouped by and if it would matter where th
'rows' went in the grouping based on those other values).i.e., in the abov
example, if the first four rows all had the same value, and there othe
values that go along with the main grouping value that may be different fro
each other, would you care which ones went into group 1 and group 2

- the number of records that go into each group won't always be the same. i.e
if the above example had 12 rows instead of 10 you woudn't have 5 groups of
- there'd be two extra rows. How do want those distributed? In a case lik
that would you want 3 groups of 2 and 2 groups of 3? Does it matter whic
groups get the extra row

If I'm on the right track, and if you can answer those questions, I can giv
you some simple logic that will do it

Gillian wrote

--
Jim Burk

Message posted via AccessMonster.co


EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorial...f-32b2d802ae17/wpf-datagrid-custom-pagin.aspx