How To Assign a Progressive Variable

[Jeff C]

I need to determine the rate of customer returns for each Rep. I thought I
would create a set of queries which, by using "datediff" I can calculate the
number of days between the first "out date" and the second "in date".

How can I assign a variable to differentiate between the different sets of
"in date" and "out date" for each customer? I thought of using "min" and
"max" but if there are more that two sets of "in dates" and "out dates" for a
customer, then that return would be lost.

Data looks something like this:

rep #---------cust #------Date In-------Date Out

can someone offer a solution?

Thanks in advance

 

[Tom van Stiphout]

On Thu, 1 May 2008 01:53:01 -0700, Jeff C

I'm not following your situation. Keep in mind we have no idea what
business you are in, if you are selling cars or cabbages. What do you
mean by a "return"? Is this an unhappy customer that returns the
merchandise previously purchased? Then I would expect a single
DateReturned. Are items returned for repair, and DateOut-DateIn is
the time it took to repair? Or is "return" perhaps for the return on
an investment?
Explain further, and we might be able to help.

-Tom.

 

[Jeff C]

--
Jeff C
Live Well .. Be Happy In All You Do

On Thu, 1 May 2008 01:53:01 -0700, Jeff C

I'm not following your situation. Keep in mind we have no idea what
business you are in, if you are selling cars or cabbages. What do you
mean by a "return"? Is this an unhappy customer that returns the
merchandise previously purchased? Then I would expect a single
DateReturned. Are items returned for repair, and DateOut-DateIn is
the time it took to repair? Or is "return" perhaps for the return on
an investment?
Explain further, and we might be able to help.

Struggling through this morning I can better describe what I am trying to do
as:

Rank by date in a group.

There was a post and solution by Karl Dewey some time back:

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

This seems to be exactly what I need howver I am short on understanding SQL
enough to construct what I think is a sub - query to make this work in my
situation.

I need help getting that query/subquery to work. Thanks for your help

 

[KARL DEWEY]

Can you take the time and answer Tom's questions?
--
KARL DEWEY
Build a little - Test a little

--
Jeff C
Live Well .. Be Happy In All You Do

On Thu, 1 May 2008 01:53:01 -0700, Jeff C

I'm not following your situation. Keep in mind we have no idea what
business you are in, if you are selling cars or cabbages. What do you
mean by a "return"? Is this an unhappy customer that returns the
merchandise previously purchased? Then I would expect a single
DateReturned. Are items returned for repair, and DateOut-DateIn is
the time it took to repair? Or is "return" perhaps for the return on
an investment?
Explain further, and we might be able to help.

Struggling through this morning I can better describe what I am trying to do
as:

Rank by date in a group.

There was a post and solution by Karl Dewey some time back:

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

This seems to be exactly what I need howver I am short on understanding SQL
enough to construct what I think is a sub - query to make this work in my
situation.

I need help getting that query/subquery to work. Thanks for your help

-Tom.

 

[Jeff C]

--
Jeff C
Live Well .. Be Happy In All You Do

Can you take the time and answer Tom's questions?

Apologies - sure

Customers come in on a given date and leave on a given date - The number of
days can vary. Some Customers return again and stay for another period. For
those customers who return, I want to determine the number of days from their
1st "Date Out" to their 2nd "Day In" and then if applicable, their 2nd "Date
Out" to their 3rd "Date In".

I thought that by ranking each visit, The rank number could be used to
identify the visit upon which I woudl calculate the days between visits.

Hope this is a better explanation and again - appreciation

Struggling through this morning I can better describe what I am trying to do
as:

Rank by date in a group.

There was a post and solution by Karl Dewey some time back:

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

This seems to be exactly what I need howver I am short on understanding SQL
enough to construct what I think is a sub - query to make this work in my
situation.

I need help getting that query/subquery to work. Thanks for your help

-Tom.



I need to determine the rate of customer returns for each Rep. I thought I
would create a set of queries which, by using "datediff" I can calculate the
number of days between the first "out date" and the second "in date".

How can I assign a variable to differentiate between the different sets of
"in date" and "out date" for each customer? I thought of using "min" and
"max" but if there are more that two sets of "in dates" and "out dates" for a
customer, then that return would be lost.

Data looks something like this:

rep #---------cust #------Date In-------Date Out

can someone offer a solution?

Thanks in advance

 

[KARL DEWEY]

Try these queries --
JeffC_A_Dates --
SELECT Q.[rep #], Q.[cust #], Q.[Date In], (SELECT COUNT(*) FROM JeffC_A Q1
WHERE Q1.[cust #] = Q.[cust #]
AND Q1.[Date In] < Q.[Date In])+1 AS Rank, Q.[Date Out]
FROM JeffC_A AS Q
ORDER BY Q.[cust #], Q.[Date In];

JeffC_A_DateDiff --
SELECT JeffC_A_Dates.[rep #], JeffC_A_Dates.[cust #], JeffC_A_Dates_1.[Date
Out], JeffC_A_Dates.[Date In], DateDiff("d",[JeffC_A_Dates_1].[Date
Out],[JeffC_A_Dates].[Date In]) AS [Days from out to in]
FROM JeffC_A_Dates INNER JOIN JeffC_A_Dates AS JeffC_A_Dates_1 ON
JeffC_A_Dates.[cust #] = JeffC_A_Dates_1.[cust #]
WHERE (((JeffC_A_Dates_1.Rank)=[JeffC_A_Dates].[Rank]-1))
ORDER BY JeffC_A_Dates.[cust #], JeffC_A_Dates.[Date In];

--
KARL DEWEY
Build a little - Test a little

--
Jeff C
Live Well .. Be Happy In All You Do

Can you take the time and answer Tom's questions?

Apologies - sure

Customers come in on a given date and leave on a given date - The number of
days can vary. Some Customers return again and stay for another period. For
those customers who return, I want to determine the number of days from their
1st "Date Out" to their 2nd "Day In" and then if applicable, their 2nd "Date
Out" to their 3rd "Date In".

I thought that by ranking each visit, The rank number could be used to
identify the visit upon which I woudl calculate the days between visits.

Hope this is a better explanation and again - appreciation

Struggling through this morning I can better describe what I am trying to do
as:

Rank by date in a group.

There was a post and solution by Karl Dewey some time back:

:

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


This seems to be exactly what I need howver I am short on understanding SQL
enough to construct what I think is a sub - query to make this work in my
situation.

I need help getting that query/subquery to work. Thanks for your help




-Tom.



I need to determine the rate of customer returns for each Rep. I thought I
would create a set of queries which, by using "datediff" I can calculate the
number of days between the first "out date" and the second "in date".

How can I assign a variable to differentiate between the different sets of
"in date" and "out date" for each customer? I thought of using "min" and
"max" but if there are more that two sets of "in dates" and "out dates" for a
customer, then that return would be lost.

Data looks something like this:

rep #---------cust #------Date In-------Date Out

can someone offer a solution?

Thanks in advance

 

[Jeff C]

--
Jeff C
Live Well .. Be Happy In All You Do

Try these queries --
JeffC_A_Dates --
SELECT Q.[rep #], Q.[cust #], Q.[Date In], (SELECT COUNT(*) FROM JeffC_A Q1
WHERE Q1.[cust #] = Q.[cust #]
AND Q1.[Date In] < Q.[Date In])+1 AS Rank, Q.[Date Out]
FROM JeffC_A AS Q
ORDER BY Q.[cust #], Q.[Date In];

JeffC_A_DateDiff --
SELECT JeffC_A_Dates.[rep #], JeffC_A_Dates.[cust #], JeffC_A_Dates_1.[Date
Out], JeffC_A_Dates.[Date In], DateDiff("d",[JeffC_A_Dates_1].[Date
Out],[JeffC_A_Dates].[Date In]) AS [Days from out to in]
FROM JeffC_A_Dates INNER JOIN JeffC_A_Dates AS JeffC_A_Dates_1 ON
JeffC_A_Dates.[cust #] = JeffC_A_Dates_1.[cust #]
WHERE (((JeffC_A_Dates_1.Rank)=[JeffC_A_Dates].[Rank]-1))
ORDER BY JeffC_A_Dates.[cust #], JeffC_A_Dates.[Date In];

Thank you very much Karl - My brain is slow here so I want to clarify



JeffCADates is the Data Table - Do I build the Query Q and Q1 and
JeffC_A_Dates_1??

I have main data table "T"

Cust#, Rep#, DateIn, DateOut

I have a Query T1 that defines only the records with customers returning

Select T1.Rep#,T1.Cust#,T1.Date In

Sorry I am confused on this

--
KARL DEWEY
Build a little - Test a little

--
Jeff C
Live Well .. Be Happy In All You Do

Can you take the time and answer Tom's questions?

Apologies - sure

:

On Thu, 1 May 2008 01:53:01 -0700, Jeff C

I'm not following your situation. Keep in mind we have no idea what
business you are in, if you are selling cars or cabbages. What do you
mean by a "return"? Is this an unhappy customer that returns the
merchandise previously purchased? Then I would expect a single
DateReturned. Are items returned for repair, and DateOut-DateIn is
the time it took to repair? Or is "return" perhaps for the return on
an investment?
Explain further, and we might be able to help.

Customers come in on a given date and leave on a given date - The number of
days can vary. Some Customers return again and stay for another period. For
those customers who return, I want to determine the number of days from their
1st "Date Out" to their 2nd "Day In" and then if applicable, their 2nd "Date
Out" to their 3rd "Date In".

I thought that by ranking each visit, The rank number could be used to
identify the visit upon which I woudl calculate the days between visits.

Hope this is a better explanation and again - appreciation

Struggling through this morning I can better describe what I am trying to do
as:

Rank by date in a group.

There was a post and solution by Karl Dewey some time back:

:

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


This seems to be exactly what I need howver I am short on understanding SQL
enough to construct what I think is a sub - query to make this work in my
situation.

I need help getting that query/subquery to work. Thanks for your help




-Tom.



I need to determine the rate of customer returns for each Rep. I thought I
would create a set of queries which, by using "datediff" I can calculate the
number of days between the first "out date" and the second "in date".

How can I assign a variable to differentiate between the different sets of
"in date" and "out date" for each customer? I thought of using "min" and
"max" but if there are more that two sets of "in dates" and "out dates" for a
customer, then that return would be lost.

Data looks something like this:

rep #---------cust #------Date In-------Date Out

can someone offer a solution?

Thanks in advance

 

[KARL DEWEY]

JeffC_A is the Data Table --- I have a table JeffC from previous post
for you.
JeffC_A_Dates is the first query
JeffC_A_Dates_1 is the same first query. When you place a table or query in
a query twice it adds a sufix of '_1' to the second instance (the third would
have '_2').

The second query is using the first query twice so as to look backwards
along with current record. That is why you have WHERE
(((JeffC_A_Dates_1.Rank)=[JeffC_A_Dates].[Rank]-1)) The minus one is
looking back.

--
KARL DEWEY
Build a little - Test a little

--
Jeff C
Live Well .. Be Happy In All You Do

Try these queries --
JeffC_A_Dates --
SELECT Q.[rep #], Q.[cust #], Q.[Date In], (SELECT COUNT(*) FROM JeffC_A Q1
WHERE Q1.[cust #] = Q.[cust #]
AND Q1.[Date In] < Q.[Date In])+1 AS Rank, Q.[Date Out]
FROM JeffC_A AS Q
ORDER BY Q.[cust #], Q.[Date In];

JeffC_A_DateDiff --
SELECT JeffC_A_Dates.[rep #], JeffC_A_Dates.[cust #], JeffC_A_Dates_1.[Date
Out], JeffC_A_Dates.[Date In], DateDiff("d",[JeffC_A_Dates_1].[Date
Out],[JeffC_A_Dates].[Date In]) AS [Days from out to in]
FROM JeffC_A_Dates INNER JOIN JeffC_A_Dates AS JeffC_A_Dates_1 ON
JeffC_A_Dates.[cust #] = JeffC_A_Dates_1.[cust #]
WHERE (((JeffC_A_Dates_1.Rank)=[JeffC_A_Dates].[Rank]-1))
ORDER BY JeffC_A_Dates.[cust #], JeffC_A_Dates.[Date In];

Thank you very much Karl - My brain is slow here so I want to clarify



JeffCADates is the Data Table - Do I build the Query Q and Q1 and
JeffC_A_Dates_1??

I have main data table "T"

Cust#, Rep#, DateIn, DateOut

I have a Query T1 that defines only the records with customers returning

Select T1.Rep#,T1.Cust#,T1.Date In

Sorry I am confused on this

--
KARL DEWEY
Build a little - Test a little

--
Jeff C
Live Well .. Be Happy In All You Do


:

Can you take the time and answer Tom's questions?

Apologies - sure

:

On Thu, 1 May 2008 01:53:01 -0700, Jeff C

I'm not following your situation. Keep in mind we have no idea what
business you are in, if you are selling cars or cabbages. What do you
mean by a "return"? Is this an unhappy customer that returns the
merchandise previously purchased? Then I would expect a single
DateReturned. Are items returned for repair, and DateOut-DateIn is
the time it took to repair? Or is "return" perhaps for the return on
an investment?
Explain further, and we might be able to help.

Customers come in on a given date and leave on a given date - The number of
days can vary. Some Customers return again and stay for another period. For
those customers who return, I want to determine the number of days from their
1st "Date Out" to their 2nd "Day In" and then if applicable, their 2nd "Date
Out" to their 3rd "Date In".

I thought that by ranking each visit, The rank number could be used to
identify the visit upon which I woudl calculate the days between visits.

Hope this is a better explanation and again - appreciation






Struggling through this morning I can better describe what I am trying to do
as:

Rank by date in a group.

There was a post and solution by Karl Dewey some time back:

:

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


This seems to be exactly what I need howver I am short on understanding SQL
enough to construct what I think is a sub - query to make this work in my
situation.

I need help getting that query/subquery to work. Thanks for your help




-Tom.



I need to determine the rate of customer returns for each Rep. I thought I
would create a set of queries which, by using "datediff" I can calculate the
number of days between the first "out date" and the second "in date".

How can I assign a variable to differentiate between the different sets of
"in date" and "out date" for each customer? I thought of using "min" and
"max" but if there are more that two sets of "in dates" and "out dates" for a
customer, then that return would be lost.

Data looks something like this:

rep #---------cust #------Date In-------Date Out

can someone offer a solution?

Thanks in advance

 

[Jeff C]

JeffC_A is the Data Table --- I have a table JeffC from previous post
for you.
JeffC_A_Dates is the first query
JeffC_A_Dates_1 is the same first query. When you place a table or query in
a query twice it adds a sufix of '_1' to the second instance (the third would
have '_2').

The second query is using the first query twice so as to look backwards
along with current record. That is why you have WHERE
(((JeffC_A_Dates_1.Rank)=[JeffC_A_Dates].[Rank]-1)) The minus one is
looking back.

You're a GURU! - Thanks very much for your patience and help.

snip

 

[Tom van Stiphout]

On Thu, 1 May 2008 12:13:08 -0700, KARL DEWEY

Nice job Karl. You must already have has some tables laying around.

Your SIG in TDD (test-driven development): Test a little - Build a
little

-Tom.

 

[meryke3]

ʱÉвÍÒûÒµÊг¡µ÷²éÎʾí

Ç×°®µÄÅóÓÑ£º

ÄúºÃ£¡

Ϊ½÷É÷´´Òµ¼°ÒÔ×îºÃ״̬Ãæ¶ÔÏû·ÑÕߣ¬Ï£ÍûÄÜÒԴ˵÷²éÎʾíÌýÈ¡Äú¶ÔʱÉвÍÒûÒµÏÖ×´µÄ
½¨ÒéºÍÒâ¼û¡£ÇëÄú¸øÓèÅäºÏ²¢ÈÏÕæÌîд£¬ÖÔÐĸÐлÄúµÄÖ§³ÖºÍºÏ×÷!

1. ÇëÎÊÄÄÀàÌØɫС³Ô×îÄÜÎüÒýÄ㣿

¡õ¶«±±²Ë ¡õ»ð¹ø ¡õ´¨²Ë ¡õÏæ²Ë ¡õÔÁ²Ë¡õÆäËü

2. ÇëÎÊÄã×îϲ»¶³ÔÄÄÖÖÀàÐ͵ÄÌØɫС³Ô£¿

¡õÉÕ¿¾Àà ¡õ¼åÕ¨Àà ¡õС³´Àà ¡õÕôÖóÀà ¡õºæ±ºÀà

¡õÖÐʽ ¡õÎ÷ʽ ¡õÆäËü

3¡¢ÇëÎÊÏÂÃæµÄÈâÀàÄãϲ»¶ÄÄÒ»Ñù£¿

¡õÖíÈâ ¡õÅ£Èâ ¡õ¹·Èâ ¡õÑòÈâ ¡õ²»È·¶¨

4. ÇëÎÊÄã¿ÉÒÔ½ÓÊܵÄС³Ô¼ÛλÊǶàÉÙ£¿£¨µ¥¼Û£©

¡õ1-3Ôª ¡õ3-5Ôª ¡õ5-8Ôª ¡õ8-12Ôª ¡õ12Ôª-15Ôª ¡õ15Ôª»òÒÔÉÏ

5. ÇëÎÊÄãÿ´ÎȥʱÉвÍÒûµêµÄÏû·ÑÒ»°ãΪ¶àÉÙÔª£¿

¡õ8ÔªÄÚ ¡õ10ÔªÄÚ ¡õ15ÔªÄÚ ¡õ18ÔªÄÚ ¡õ20Ôª»òÒÔÉÏ

6. ÇëÎÊÄãͨ³£¶à¾ÃÈ¥Ò»´ÎʱÉвÍÒûµê£¿

¡õ3Ìì ¡õÒ»ÖÜ ¡õÁ½ÖÜ ¡õÒ»¸öÔ ¡õÒ»¸öÔ»ò¸ü¾Ã

7¡¢ÇëÎÊÔÚÍâÃæ³Ô·¹Ò»°ã¼¸¸öÈ˺ÍÄãÒ»ÆðÈ¥

¡õ7-8ÈË ¡õ 4-5ÈË ¡õ2-3ÈË ¡õ1ÈË ¡õ²»È·¶¨

ÔٴθÐлÄúÔÚ°Ùæ֮ÖвÎÓëÎÒÃǵÄÎʾíµ÷²é£¬Ð»Ð»£¡





ʱÉвÍÒûÒµÊг¡µ÷²éÎʾí

Ç×°®µÄÅóÓÑ£º

ÄúºÃ£¡

Ϊ½÷É÷´´Òµ¼°ÒÔ×îºÃ״̬Ãæ¶ÔÏû·ÑÕߣ¬Ï£ÍûÄÜÒԴ˵÷²éÎʾíÌýÈ¡Äú¶ÔʱÉвÍÒûÒµÏÖ×´µÄ
½¨ÒéºÍÒâ¼û¡£ÇëÄú¸øÓèÅäºÏ²¢ÈÏÕæÌîд£¬ÖÔÐĸÐлÄúµÄÖ§³ÖºÍºÏ×÷!

1¡¢. ÇëÎÊʲôÇé¿öÏÂÄã»áÑ¡Ôñµ½Ê±ÉвÍÒûµêÏû·Ñ£¿

¡õ½ÚÈÕÇìºØ ¡õÔ¶ÅóÀ´·Ã ¡õÅóÓÑ¾Û²Í ¡õÐÝÏÐÏû·Ñ ¡õÌؼ۴ÙÏú ¡õÅóÓÑÍƽé

¡õÆäËü

2¡¢. ÇëÎÊÄã»á°ÑʱÉвÍÒû×÷ΪÕý²ÍÂð£¿ÎªÊ²Ã´£¿

¡õ»á£¬Ô­Òò-_ ¡õ²»»á£¬Ô­Òò£º

3¡¢ ÇëÎÊÒÔϸ÷ÏîÄãÈÏΪÄÄÏî¸üÖØÒª£¿£¨Çë°´Ö÷´Î˳ÐòÔÚ·½¸ñÉÏдÉÏÐòºÅ£©

¡õµØÀíλÖà ¡õ×°ÐÞ·½Ê½ ¡õ»·¾³·ÕΧ ¡õʳƷζµÀ ¡õ·þÎñÖÊÁ¿ ¡õ²úÆ·¼Û¸ñ

¡õ¾­Óª·½Ê½ ¡õÆ·ÅÆÐÎÏó ¡õµêÆÌ·ç¸ñ

4¡¢ ÇëÁоÙÄã¾õµÃµ¼ÖÂʱÉвÍÒûµêʧ°ÜµÄÒòËØ¡£

¡õµØµãÄÑÕÒ ¡õ½»Í¨²»±ã ¡õ»·¾³·ÕΧ²î ¡õʳƷζµÀ²»¼Ñ ¡õ·þÎñÖÊÁ¿²»¸ß

¡õ²úÆ·¼Û¸ñ¹ó ¡õ²úÆ·ÖÖÀ൥һ ¡õÆ·ÅÆÐÎÏó²î ¡õûÓеêÆÌ·ç¸ñ

5¡¢ ÇëÁоÙÄã×îϲ»¶µÄʱÉвÍÒûµê´ÙÏú·½Ê½¡£

¡õ»áÔ±ÖÆ ¡õ»ý·Ö¿¨ ¡õÕÛ¿Û„» ¡õÿÈÕÌØ¼Û ¡õ½ÚÈÕÓÅ»Ý ¡õ´ÙÏúÀñÆ·

¡õÆäËü

6¡¢¶Ô²ÍÒûµêµÄһЩ·ç¸ñ£¬ÇëÎÊÄã¸üϲ»¶ÄÄÖÖ¡£

¡õ¹ÅµäÆøÏ¢ ¡õ´¿ÆÓ·ç¸ñ ¡õÀËÂþÆøÏ¢ ¡õÒÕÊõ·ç¸ñ

7¡¢ÇëÎÊÄãÄܽÓÊÜ¡°ÍâÂôʽ¡±£¨±ß×߱߳ԣ©µÄÒûʳÏû·Ñ·½Ê½Âð£¿ÎªÊ²Ã´£¿

¡õ¿ÉÒÔ ¡õ²»¿ÉÒÔ

ÔٴθÐлÄúÔÚ°Ùæ֮ÖвÎÓëÎÒÃǵÄÎʾíµ÷²é£¬Ð»Ð»£¡

JeffC_A is the Data Table --- I have a table JeffC from previous post
for you.
JeffC_A_Dates is the first query
JeffC_A_Dates_1 is the same first query. When you place a table or query in
a query twice it adds a sufix of '_1' to the second instance (the third would
have '_2').

The second query is using the first query twice so as to look backwards
along with current record. That is why you have WHERE
(((JeffC_A_Dates_1.Rank)=[JeffC_A_Dates].[Rank]-1)) The minus one
is
looking back.

You're a GURU! - Thanks very much for your patience and help.

snip