Access Query Referencing Value from Previous Row

Question

I’ve been dealing with this specific issue for years, but still haven’t been able to figure out a solution. I have found dozens and dozens of similar questions and answers on the net, but nothing that seems to solve my exact problem.

I’ve got a table (actually a query that draws from a previous query) with names and addresses of package recipients. This final query selects certain records and sorts them by address within route. The query guarantees that all deliveries to a particular addresses will be next to each other in the table.

Route	Name	Address
1	Family 1	100 Alpha St
1	Family 2	110 Alpha St
1	Family 3	200 Alpha St
1	Kid 3	200 Alpha St
1	Family 4	51 Antique Rd
2	Family 5	20 Bixby St
2	Kid 5a	20 Bixby St
2	Kid 5b	20 Bixby St
2	Family 6	52 Bojangles Ln
2	Family 7	60 Bojangles Ln

I have been manually assigning stop numbers for each unique address in each route; I’d prefer to have my Access query perform that task. Family 1 and Family 2 are the first two stops on route 1, and would be stops #1 and #2, respectively. Family 3 and Kid 3 are at the same address, so they’re both at stop #3. Family 4 would be stop #4 on route 1. On route 2, Family 5, Kid 5a, and Kid 5b are all at the same address, so they’re all stop #1 on route 2. And so on.

Route	Stop	Name	Address
1	1	Family 1	100 Alpha St
1	2	Family 2	110 Alpha St
1	3	Family 3	200 Alpha St
1	3	Kid 3	200 Alpha St
1	4	Family 4	51 Antique Rd
2	1	Family 5	20 Bixby St
2	1	Kid 5a	20 Bixby St
2	1	Kid 5b	20 Bixby St
2	2	Family 6	52 Bojangles Ln
2	3	Family 7	60 Bojangles Ln

Currently I run my query without producing stop numbers, then export it to Excel. I then insert a column B as "Stop", put the formula

=IF(A2<>A1,1,IF(D2=D1,B1,B1+1))

into cell B2 and drag it down the column, producing the correct values in column B.

Can anyone come up with an Access query formula that would detect a change in route number to restart the stop number at 1, and simply increment the stop number when on the same route?

Answer 1

Glad to hear it.

Answer 2

All: Be aware that for my original post I tried to simplify things for clarity. I also made up the data so no actual client information was included. That led to Scottgem correctly pointing out issues with my example that don’t exist in my real environment. My apologies for any confusion. In this post I will use actual data, but change some of the address information for security.

Ken,

Thanks so much for your suggestions and your examples. I have played with the simplified query that you proposed, and I think I’m close to making it work. If my understanding is correct, the proposed query produces a table of stop numbers, and  my existing query would fetch the value from there. (That’s as opposed to a formula that I simply add to my original query.) Here is my current “Compute the stop number” query:

  SELECT COUNT(*) AS StopNumber, T1.Sort_Route, T1.Sort_Key FROM RQ_RoutingSheets_Step2 AS T1

  INNER JOIN RQ_RoutingSheets_Step2 AS T2

  ON T2.Sort_Key<=T1.Sort_Key AND T2.Sort_Route=T1.Sort_Route

  GROUP BY T1.Sort_Route, T1.Sort_Key ;

RQ_RoutingSheets_Step3
StopNumber	Sort_Route	Sort_Key
1	01	017900g7Co Ln13304
2	01	017900g7Hu Way4028
3	01	017900g7Re Ln13508
4	01	017900h5Ha Ln3305
5	01	017900h7Pa Cir3800
6	01	017900h7Wh Ter12605
1	02	027900f8Mi Pl3700
2	02	027900f8Mi Pl3724
3	02	027900g8Ch Rd12817
…
1	05	058008g2Br Ct2300
2	05	058008g2Le Ct2302
3	05	058008g2Le Ct2308
4	05	058008h1Ga Rd124Apt 2618
5	05	058008h1Ga Rd124Apt 4533
6	05	058008h1Wa Ct2539
16	05	058008h1Wa Ct2540
9	05	058008h3We Ln1411
10	05	058008j1Gr Cir2322Apt W
11	05	058008j1In Rd1111

The only issue is illustrated in route 05. There are two packages at the common address at entries 7 and 8 on the route. After stop #6, Instead of having one entry numbered 7 to include the two deliveries at Wa Ct 2540, it produces one entry number 16 (=8+8), then numbers the next stop 9 instead of 8. This pattern occurs on several routes.

If you’re willing to give me some advice, I’d greatly appreciate it. If you want to point me at a specific example of yours, that would be fine too. I'm grateful for any assistance.

Answer 3

As I said in my previous reply the simplified query's methodology assumes distinct values of sort_key per sort_code.  As you have duplicated sort_key values per sort_code, however, the values are returned incorrectly.  I was a little surprised at first that the value for route 5 should be 16.  I would normally have expected, with two identical sort_code values, two rows to be returned with a value of 8.  Upon a closer look at the SQL statement, however, I see that the reason for this is that the columns on which the query is grouped do not include a candidate key.  Usually this will be an autonumber column, TransactionID in my case.

So, you need to expand the query's JOIN criteria so that they call candidate key into play as the tie-breaker, and include the candidate key in the GROUP BY clause.  The original query in my demo does this:

SELECT COUNT(*) AS RowNumber, T1.CustomerID, T1.TransactionDate, T1.TransactionAmount

FROM Transactions AS T1 INNER JOIN Transactions AS T2

ON (T2.TransactionID<=T1.TransactionID OR T2.TransactionDate<>T1.TransactionDate)

AND (T2.TransactionDate<=T1.TransactionDate) AND (T2.CustomerID=T1.CustomerID)

GROUP BY T1.CustomerID, T1.TransactionDate, T1.TransactionAmount, T1.TransactionID;

Answer 4

Better, but still not correct. I’ve made my changes, trying very hard to match my key fields to those in your example, but now it doesn’t recognize any duplicate addresses. I’m hoping you’ll be able to spot my error.

Below is the significant route 05 data from the query RQ_RoutingSheets_Step2 that feeds RQ_RoutingSheets_Step3:

Sort_Route	LastName	Map_Address_S	Apt	Sort_Key
05	Person01	2300 Br Ct		058008g2Br Ct2300
05	Person02	2302 Le Ct		058008g2Le Ct2302
05	Person03	2308 Le Ct		058008g2Le Ct2308
05	Person04	124 Ga Rd	Apt 2618	058008h1Ga Rd124Apt 2618
05	Person05	124 Ga Rd	Apt 4533	058008h1Ga Rd124Apt 4533
05	Person06	2539 Wa Ct		058008h1Wa Ct2539
05	Person07	2540 Wa Ct		058008h1Wa Ct2540
05	Person08	2540 Wa Ct		058008h1Wa Ct2540
05	Person09	1411 We Ln		058008h3We Ln1411
05	Person10	2322 Gr Cir	Apt W	058008j1Gr Cir2322Apt W
05	Person11	1111 In Rd		058008j1In Rd1111

Sort_Key is used to sort the list. It is composed of route #, route sub-info, street, house, apartment.

LastName is actually surname & firstname and will be unique for any address on a route. I chose to use LastName as my candidate key because (a) I can’t autonumber in a query [and this query has a string of at least 3 queries feeding it], and (b) the input has been sorted by route, etc., with LastName as its final key. For the purposes of generating stop numbers, it doesn’t matter to me which name is used if more than one person lives there.

Here is my latest version of query RQ_RoutingSheets_Step3, which produces the stop numbers:

SELECT COUNT(*) AS StopNumber, T1.Sort_Route, T1.Map_Address_S, T1.LastName

FROM RQ_RoutingSheets_Step2 AS T1

INNER JOIN RQ_RoutingSheets_Step2 AS T2

ON

(T2.LastName<=T1.LastName OR T2.Map_Address_S<>T1.Map_Address_S)

AND

(T2.Sort_Key<=T1.Sort_Key) AND (T2.Sort_Route=T1.Sort_Route)

GROUP BY T1.Sort_Route, T1.Sort_Key, T1.Map_Address_S, T1.LastName;

The output of RQ_RoutingSheets_Step3:

StopNumber	Sort_Route	Map_Address_S	LastName
1	05	2300 Br Ct	Person01
2	05	2302 Le Ct	Person02
3	05	2308 Le Ct	Person03
4	05	124 Ga Rd	Person04
5	05	124 Ga Rd	Person05
6	05	2539 Wa Ct	Person06
7	05	2540 Wa Ct	Person07
8	05	2540 Wa Ct	Person08
9	05	1411 We Ln	Person09
10	05	2322 Gr Cir	Person10
11	05	1111 In Rd	Person11

Persons 4 & 5 should have shared a stop number, as should persons 7 &8, so there should be only 9 stops.

Once again I thank you for your patience, your time, and your expertise.