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

Hi RizzKid1, hope you're doing well. I’m Ian, and I’m happy to help you today.

I'll give you an idea that will help you to resolve your issue.

In this one you will be needing an intermediary table to have the result you wanted.

Right now you have your 1st table (from a query), the for intermediary table you can use the SQL below (change this based on you table and column naming).

SELECT YourTable.Route, YourTable.Address , 
DCount("*","YourTable","Route=" & [Route] & " AND Address =" & [Address]) AS Stop
FROM YourTable; 

In here you have a sequence number (Stop) without the family.

Going back yo your 1st table, you need to search the "Stop" from the intermediary table data based on your Route and Address. You can do nested select query in this case.

This is a user-to-user support forum and I am a fellow user.

I hope this helps, but please let me know if you need anything else.

Answer 2

rianvillareal is on the right track with the DCount. In fact you have to find the "number of rows with the same route and a 'smaller' address and name" for each row (and add 1 to it).

in a query it would look like:

SELECT Route, Recipient, Address, DCount("*","Stops","Route=" & [Route] & " AND Address & Recipient<'" & [Address] & [Recipient] & "'")+1 AS Stop

FROM Stops;

Note I changed "Name" to "Recipient". Name is a reserved word in Access. Better not use it as fieldname.

You can find an example database here.

Answer 3

The most efficient way to do this would be by means of a JOIN of two instances of the table.  You'll find examples in RowNumbering.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

In this little demo file the following query numbers rows per group, customer in my case, (route in yours) ordered by transaction date (address in your case).

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;

The above query allows for duplicate transaction dates, i.e. allowing more than one transaction per customer to be on the same day.  As I assume that addresses would be distinct per route in your case the query can be simplified as follows:

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

FROM Transactions AS T1 INNER JOIN Transactions AS T2

ON T2.TransactionDate<=T1.TransactionDate AND T2.CustomerID=T1.CustomerID

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

Answer 4

One problem that I see in your issue is that you appear to be using ONE address field. If you are trying to create a route for addresses, then one address field won't do it. For example, how does 51 Antique St come before 20 Bixby Road. I built an app, a while back) for a client that involved such routing. In that app, there was a separate field for street number and street name. You need 2 field if you want to sort by address for routing purposes. Even then, you might have a situation where Apple Lane is close to Pear Lane, but Banana St is on the other side of the route!

I would suggest you research GIS systems to place addresses near each other.

Answer 5

Scottgem: Thanks for the response. The fact is that house number, street name, and apartment number -are- in different fields in the database. Several initial queries sort those fields in the correct order, but concatenate them in the current query after the sort, as my volunteer drivers don't need to see those fields split up.

I'll also add that the program I wrote -does- fetch and keep longitude & latitude for each location. As the non-profit I'm helping can't afford the cost for a GIS system that would be used once a year (I checked), my program produces a giant map with the location of every stop superimposed on commercial map pages. I manually scan those maps to ensure that locations on opposite sides of a highway, even though in the same long/lat block, end up in different routes.