text then date ascending count

[Cameron]

I need to group an alphanumeric customerID and Order by PurchaseDate, then
assign each ascending purchase its PurchaseRank. Please Help.

customerID PurchaseDate PurchaseRank (new field needed)
EA454 1/1/08 1
EA454 3/1/08 2
GV152 9/1/07 1

 

[KARL DEWEY]

Try this --
SELECT customerID, PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate <=
T.PurchaseDate) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

 

[Cameron]

This worked. Thank you very much.

Try this --
SELECT customerID, PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate <=
T.PurchaseDate) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

--
KARL DEWEY
Build a little - Test a little

I need to group an alphanumeric customerID and Order by PurchaseDate, then
assign each ascending purchase its PurchaseRank. Please Help.

customerID PurchaseDate PurchaseRank (new field needed)
EA454 1/1/08 1
EA454 3/1/08 2
GV152 9/1/07 1

 

[Cameron]

The problem I am having with this query is if the customer purchases more
than once on the same day, which means it doesn't count up for the second
purchase. Any idea's how to correct that? I don't even care if they are in
any type of order for the same date purchases, just that they are counted up.

Try this --
SELECT customerID, PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate <=
T.PurchaseDate) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

--
KARL DEWEY
Build a little - Test a little

I need to group an alphanumeric customerID and Order by PurchaseDate, then
assign each ascending purchase its PurchaseRank. Please Help.

customerID PurchaseDate PurchaseRank (new field needed)
EA454 1/1/08 1
EA454 3/1/08 2
GV152 9/1/07 1

 

[KARL DEWEY]

Use a totals query ahead of this.
--
KARL DEWEY
Build a little - Test a little

The problem I am having with this query is if the customer purchases more
than once on the same day, which means it doesn't count up for the second
purchase. Any idea's how to correct that? I don't even care if they are in
any type of order for the same date purchases, just that they are counted up.

Try this --
SELECT customerID, PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate <=
T.PurchaseDate) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

--
KARL DEWEY
Build a little - Test a little

I need to group an alphanumeric customerID and Order by PurchaseDate, then
assign each ascending purchase its PurchaseRank. Please Help.

customerID PurchaseDate PurchaseRank (new field needed)
EA454 1/1/08 1
EA454 3/1/08 2
GV152 9/1/07 1

 

[KARL DEWEY]

If you have a primary key field like an autonumber that try this --
SELECT customerID, PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate & [PrimaryKey]
<= T.PurchaseDate & [PrimaryKey]) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

--
KARL DEWEY
Build a little - Test a little

Use a totals query ahead of this.
--
KARL DEWEY
Build a little - Test a little

The problem I am having with this query is if the customer purchases more
than once on the same day, which means it doesn't count up for the second
purchase. Any idea's how to correct that? I don't even care if they are in
any type of order for the same date purchases, just that they are counted up.

Try this --
SELECT customerID, PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate <=
T.PurchaseDate) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

--
KARL DEWEY
Build a little - Test a little


:

I need to group an alphanumeric customerID and Order by PurchaseDate, then
assign each ascending purchase its PurchaseRank. Please Help.

customerID PurchaseDate PurchaseRank (new field needed)
EA454 1/1/08 1
EA454 3/1/08 2
GV152 9/1/07 1

 

[Cameron]

I tried this and I got a case where it doesn't work correctly and I listed it
below:

SELECT T.customerID, T.PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate & T1.PrimaryKey
<=
T.PurchaseDate & T.PrimaryKey) AS Rank
FROM Cameron AS T
ORDER BY T.customerID, T.PurchaseDate;


customerID PurchaseDate PrimaryKey Rank
1200 12/2/2003 15448 2
1200 8/13/2007 41311 3
1200 11/10/2007 44031 1

I am temporarily doing this in excel and importing the correct rank. In
excel it is so simple, sort and use a countif formula.

Cameron

If you have a primary key field like an autonumber that try this --
SELECT customerID, PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate & [PrimaryKey]
<= T.PurchaseDate & [PrimaryKey]) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

--
KARL DEWEY
Build a little - Test a little

Use a totals query ahead of this.
--
KARL DEWEY
Build a little - Test a little

The problem I am having with this query is if the customer purchases more
than once on the same day, which means it doesn't count up for the second
purchase. Any idea's how to correct that? I don't even care if they are in
any type of order for the same date purchases, just that they are counted up.

:

Try this --
SELECT customerID, PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate <=
T.PurchaseDate) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

--
KARL DEWEY
Build a little - Test a little


:

I need to group an alphanumeric customerID and Order by PurchaseDate, then
assign each ascending purchase its PurchaseRank. Please Help.

customerID PurchaseDate PurchaseRank (new field needed)
EA454 1/1/08 1
EA454 3/1/08 2
GV152 9/1/07 1

 

[KARL DEWEY]

Maybe I am just slow this morning but I do not see what is not working
correctly.

Explain it to me.
--
KARL DEWEY
Build a little - Test a little

I tried this and I got a case where it doesn't work correctly and I listed it
below:

SELECT T.customerID, T.PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate & T1.PrimaryKey
<=
T.PurchaseDate & T.PrimaryKey) AS Rank
FROM Cameron AS T
ORDER BY T.customerID, T.PurchaseDate;


customerID PurchaseDate PrimaryKey Rank
1200 12/2/2003 15448 2
1200 8/13/2007 41311 3
1200 11/10/2007 44031 1

I am temporarily doing this in excel and importing the correct rank. In
excel it is so simple, sort and use a countif formula.

Cameron

If you have a primary key field like an autonumber that try this --
SELECT customerID, PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate & [PrimaryKey]
<= T.PurchaseDate & [PrimaryKey]) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

--
KARL DEWEY
Build a little - Test a little

Use a totals query ahead of this.
--
KARL DEWEY
Build a little - Test a little


:

The problem I am having with this query is if the customer purchases more
than once on the same day, which means it doesn't count up for the second
purchase. Any idea's how to correct that? I don't even care if they are in
any type of order for the same date purchases, just that they are counted up.

:

Try this --
SELECT customerID, PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate <=
T.PurchaseDate) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

--
KARL DEWEY
Build a little - Test a little


:

I need to group an alphanumeric customerID and Order by PurchaseDate, then
assign each ascending purchase its PurchaseRank. Please Help.

customerID PurchaseDate PurchaseRank (new field needed)
EA454 1/1/08 1
EA454 3/1/08 2
GV152 9/1/07 1

 

[John Spencer]

Try this modification. I think it will give you the desired result, but I
have no way to test it.

SELECT customerID, PurchaseDate
, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID
AND T1.PurchaseDate <= T.PurchaseDate
AND T1.PrimaryKey <= T.PrimaryKey) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

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

The problem I am having with this query is if the customer purchases more
than once on the same day, which means it doesn't count up for the second
purchase. Any idea's how to correct that? I don't even care if they are in
any type of order for the same date purchases, just that they are counted up.

Try this --
SELECT customerID, PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate <=
T.PurchaseDate) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

--
KARL DEWEY
Build a little - Test a little

I need to group an alphanumeric customerID and Order by PurchaseDate, then
assign each ascending purchase its PurchaseRank. Please Help.

customerID PurchaseDate PurchaseRank (new field needed)
EA454 1/1/08 1
EA454 3/1/08 2
GV152 9/1/07 1

 

[Cameron]

The example I gave that isn't working shows that it is ranked out of order.
I am still trying to figure out why for this example it is ranking out of
order but it is. For the most part the purchases are ranked properly, rank
of 1 being the first sale (oldest) and the rank of 3 being the newest
purchase (newest).

Maybe I am just slow this morning but I do not see what is not working
correctly.

Explain it to me.
--
KARL DEWEY
Build a little - Test a little

I tried this and I got a case where it doesn't work correctly and I listed it
below:

SELECT T.customerID, T.PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate & T1.PrimaryKey
<=
T.PurchaseDate & T.PrimaryKey) AS Rank
FROM Cameron AS T
ORDER BY T.customerID, T.PurchaseDate;


customerID PurchaseDate PrimaryKey Rank
1200 12/2/2003 15448 2
1200 8/13/2007 41311 3
1200 11/10/2007 44031 1

I am temporarily doing this in excel and importing the correct rank. In
excel it is so simple, sort and use a countif formula.

Cameron

If you have a primary key field like an autonumber that try this --
SELECT customerID, PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate & [PrimaryKey]
<= T.PurchaseDate & [PrimaryKey]) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

--
KARL DEWEY
Build a little - Test a little


:

Use a totals query ahead of this.
--
KARL DEWEY
Build a little - Test a little


:

The problem I am having with this query is if the customer purchases more
than once on the same day, which means it doesn't count up for the second
purchase. Any idea's how to correct that? I don't even care if they are in
any type of order for the same date purchases, just that they are counted up.

:

Try this --
SELECT customerID, PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate <=
T.PurchaseDate) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

--
KARL DEWEY
Build a little - Test a little


:

I need to group an alphanumeric customerID and Order by PurchaseDate, then
assign each ascending purchase its PurchaseRank. Please Help.

customerID PurchaseDate PurchaseRank (new field needed)
EA454 1/1/08 1
EA454 3/1/08 2
GV152 9/1/07 1

 

[KARL DEWEY]

Try this --
SELECT T.customerID, T.PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.[Primarykey] <=
T.[Primarykey]) AS Rank
FROM Cameron AS T
ORDER BY T.customerID, T.PurchaseDate;
--
KARL DEWEY
Build a little - Test a little

The example I gave that isn't working shows that it is ranked out of order.
I am still trying to figure out why for this example it is ranking out of
order but it is. For the most part the purchases are ranked properly, rank
of 1 being the first sale (oldest) and the rank of 3 being the newest
purchase (newest).

Maybe I am just slow this morning but I do not see what is not working
correctly.

Explain it to me.
--
KARL DEWEY
Build a little - Test a little

I tried this and I got a case where it doesn't work correctly and I listed it
below:

SELECT T.customerID, T.PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate & T1.PrimaryKey
<=
T.PurchaseDate & T.PrimaryKey) AS Rank
FROM Cameron AS T
ORDER BY T.customerID, T.PurchaseDate;


customerID PurchaseDate PrimaryKey Rank
1200 12/2/2003 15448 2
1200 8/13/2007 41311 3
1200 11/10/2007 44031 1

I am temporarily doing this in excel and importing the correct rank. In
excel it is so simple, sort and use a countif formula.

Cameron

:

If you have a primary key field like an autonumber that try this --
SELECT customerID, PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate & [PrimaryKey]
<= T.PurchaseDate & [PrimaryKey]) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

--
KARL DEWEY
Build a little - Test a little


:

Use a totals query ahead of this.
--
KARL DEWEY
Build a little - Test a little


:

The problem I am having with this query is if the customer purchases more
than once on the same day, which means it doesn't count up for the second
purchase. Any idea's how to correct that? I don't even care if they are in
any type of order for the same date purchases, just that they are counted up.

:

Try this --
SELECT customerID, PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate <=
T.PurchaseDate) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

--
KARL DEWEY
Build a little - Test a little


:

I need to group an alphanumeric customerID and Order by PurchaseDate, then
assign each ascending purchase its PurchaseRank. Please Help.

customerID PurchaseDate PurchaseRank (new field needed)
EA454 1/1/08 1
EA454 3/1/08 2
GV152 9/1/07 1

 

[Cameron]

For some reason it doesn't work in some cases, logically I don't understand
why it doesn't work for some, and does for others. Below is what I tried and
then a few examples, one that didn't work, and one just like it that did work:


SELECT customerID, PurchaseDate, PrimaryKey
, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID
AND T1.PurchaseDate <= T.PurchaseDate
AND T1.PrimaryKey <= T.PrimaryKey) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;


customerID PurchaseDate PrimaryKey RANK
312 11/11/2002 9424 1
312 5/22/2006 33714 1


customerID PurchaseDate PrimaryKey RANK
402 2/1/2008 45726 1
402 2/25/2008 46101 2

Try this modification. I think it will give you the desired result, but I
have no way to test it.

SELECT customerID, PurchaseDate
, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID
AND T1.PurchaseDate <= T.PurchaseDate
AND T1.PrimaryKey <= T.PrimaryKey) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

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

The problem I am having with this query is if the customer purchases more
than once on the same day, which means it doesn't count up for the second
purchase. Any idea's how to correct that? I don't even care if they are in
any type of order for the same date purchases, just that they are counted up.

Try this --
SELECT customerID, PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate <=
T.PurchaseDate) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

--
KARL DEWEY
Build a little - Test a little


:

I need to group an alphanumeric customerID and Order by PurchaseDate, then
assign each ascending purchase its PurchaseRank. Please Help.

customerID PurchaseDate PurchaseRank (new field needed)
EA454 1/1/08 1
EA454 3/1/08 2
GV152 9/1/07 1