Quintiles

[jlieffort via AccessMonster.com]

I have a query "LastCustomerPurchaseDate" that returns the last Purchase date
of our customers .
Two field Names BILLNAME and MaxOfDocDate.

I want to place each customer "BILLNAME" into a quintile based on their most
recent purchase "MaxOfDocDate".

The most recent quintile would be 5, the last 1.

Thanks,

 

[jlieffort via AccessMonster.com]

Any help out there?

I have a query "LastCustomerPurchaseDate" that returns the last Purchase date
of our customers .
Two field Names BILLNAME and MaxOfDocDate.

I want to place each customer "BILLNAME" into a quintile based on their most
recent purchase "MaxOfDocDate".

The most recent quintile would be 5, the last 1.

Thanks,

 

[John Spencer]

PERHAPS the following will work. IT will be slow

SELECT La.BillName
, La.MaxOfDocDate
, (1 + Count(Lb.MaxOfDocDate))\
((SELECT Count(*) FROM LastCustomerPurchaseDate)\5) as Quintile
FROM LastCustomerPurchaseDate as La LEFT JOIN LastCustomerPurchaseDate as Lb
ON La.MaxOfDocDate > Lb.MaxOfDocDate
GROUP BY La.BillName, La.MaxOfDocDate

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[jlieffort via AccessMonster.com]

Thanks for your thinking. I ran the query and it took so long as not to be
useful.

As a short term measure I am exporting the query results to excel where I use
the PercentRank function to return the rank of the value as a percentage,
then I use a set of nested if functions to sort out into quintile groups.
This works well enough but the data is changing so I would prefer to do it
access. Is there a way to bring the excel command and use it in access? or do
you know how I could write a similar function in vba then call it with the
expression builder?

Thanks in advance

PERHAPS the following will work. IT will be slow

SELECT La.BillName
, La.MaxOfDocDate
, (1 + Count(Lb.MaxOfDocDate))\
((SELECT Count(*) FROM LastCustomerPurchaseDate)\5) as Quintile
FROM LastCustomerPurchaseDate as La LEFT JOIN LastCustomerPurchaseDate as Lb
ON La.MaxOfDocDate > Lb.MaxOfDocDate
GROUP BY La.BillName, La.MaxOfDocDate

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Any help out there?

[quoted text clipped - 8 lines]