How sort address for walk list

[David40]

Not sure this is the right place to post this because I'm not sure what method to use to do what I want to do.

I have a database of street addresses and I want to create a walk list. This would mean sorting the addresses ascending/descending based upon even and odd house numbers, so that the walkers can have a sheet with the addresses they need to visit. The addresses need to be in the proper order to walk up one side of the street and then back down the other side. I've gotten as far as sorting the addresses by street and house number but I don't have a clue about how to isolate/sort even and odd house numbers. I would really appreciate any advice.

Thanks,

David40

[comment]

Try a calculation field (result is Number) =

Mod ( HouseNumber ; 2 )

Include this field in the sort order, between street and house number.

[David40]

Looks like you've done that before. I've never had to set up a calculation field before so what you're saying is Greek to me, but hopefully it will make sense when I get in there and actually see the options.

Thank You.

[Lee Smith]

Is this for delivery of something?

Are the addresses in one field like, Address: 111B W South Street Apt5, Your City, Your State

Or are they broken into parts such as:

Street Number

Supplimental Number

Street Name

Apt/Suite

City

State

Lee

[David40]

Totally broken up by street name/number, street name suffix (Ave, St, Blvd), house number, etc. It was easy to get them sorted by the street name and house number but sorting it further by even and odd house numbers is beyond my expertise.

It's the database of all the voters in the county I live in. Just under 700,000 records. I am doing walk lists for political canvassers. I sort by precinct, party affiliation, then street name, and house number. Adding a sort on even and odd house numbers on top of that would be sweet.

Edited April 4, 2009 by Guest

[Lee Smith]

Did you see comment's post?

His [color:blue]Mod ( HouseNumber ; 2 ) will provide you with either a 1 or 0 depending on whether the house number is Odd or Even.

You will need to create a New field, call it something like c_Odd|Even and use his calculation.

Then include it as part of your sort.

HTH

Lee

[David40]

Yes I did, I'm just not sure how it works. I don't know what "Mod" means or the significants of the punctuation. I'll play with it and the formatting, and see what happens. I'm considered an expert at several things but math is definitely NOT one of them.

Thanks much.

[comment]

Try reading the help on the Mod() function.

[David40]

Got it, Thanks so much. Now as an added touch I'm going to try figure out how to sort it further by sorting the odd numbers ascending and the even numbers descending (or vise versa). That would make it darn near perfect.

[Lee Smith]

Think in terms of two reports, one for the Odd, and one for Even.

Sort one Ascending, and the other Descending.

HTH

Lee

[Lee Smith]

BTW, look into finding Ranges of Information.

Link

You are going to want to break these list down so that they are manageable.

[comment]

That's not "an added touch", but something else. You need a calculation along the lines of =

Case ( Mod ( HouseNumber ; 2 ) ; -HouseNumber ; HouseNumber )

and use ONLY this field for sorting, instead of the previous calculation and the "real" house number.

Moving the minus sign to the default result and/or toggling between ascending/descending will provide more variations.

---

To reverse the direction of walking, use:

1 / Case ( Mod ( HouseNumber ; 2 ) ; -HouseNumber ; HouseNumber )

Edited April 4, 2009 by Guest

[David40]

I am working with street addresses to create efficient walk sheets for door to door canvassers. A problem I am having is to sort the street names in order because we have numbered streets with named streets in between. For example Emerson Ave is between 5th and 6th Ave. So when I sort the street names all the numbered streets are first and then all the named streets. (The named streets are Alphabetic.) What makes it more difficult is that there are only named streets between some of the numbered streets, not all. I would be grateful for any advice on how to get these to sort in the proper order.

Thanks,

David40

[David40]

Yes, I generally break them down by precinct. Then whomever is doing the canvassing can work alone or divide the sheets among helpers.

[comment]

What *is* the proper order?

[Lee Smith]

I merged your two topics. Please don't start new thread when asking followup questions on the same need.

Lee

[David40]

OK. I didn't think it was related close enough to be considered the same topic.

Anyway, in reply:

Alphanumeric ascending or descending. For example the physical streets are laid out Central, 1st, 2nd, Dartmouth, 3rd, 4th, 5th, Emerson, 6th, etc. That way walkers could go street to street up and back down each one. They could just flip through the sheets to find the street they are on but I wanted to make it as easy and foolproof as possible. Be nice if all they had to do was follow the list and concentrate on their work instead of figuring out where they are going.

[comment]

I must be missing something, because that order doesn't seem alphanumeric to me. How is one supposed to know that your city decided to put Emerson between 5th and 6th, or that Central comes before 1st?

It seems to me you need a table of Streets, where each street has a Order field. Link your addresses to the Streets table, and use the Streets::Order field for sorting, instead of the street's name.

[RalphL]

Make a custom value list of the street names. Sort based on the value list.

[David40]

Yes, alphanumeric, the numbered streets 1, 2, 3, 4... for the maned streets the first letter of each name C, D, E....

I think I understand. I can assign a numeric value to each street regardless of it's actual name in a table. "0" could represent Central. Then 1st and 2nd, then Dartmouth could be "2.5, then 3rd and 4th etc.

Thanks to all of you. Great bunch of users here. :(

[David40]

Comment,

I tried that little formula:

Case ( Mod ( HouseNumber ; 2 ) ; -HouseNumber ; HouseNumber )

and it would not work. I get an error message that there are too many "(" or not enough ")"....

Looks like it won't accept a nested formula. Is there any other way to do that?

[comment]

I see exactly two of each, so I believe it should work (it does for me).

[David40]

I know, I see two of each as well, that's why I was surprised to get the error message. Maybe I have an extra space in there or something. Must be I have the syntax wrong somewhere. I cut it from your post and pasted it in the formula box then changed the field name to what I am using.

[David40]

The first formula got me almost there and I got the second formula to function but it does not return the results I need. It gives me a list that is sorted with all the even addresses and then all the odd addresses. Not what I had in mind for a walk list.

I think I figured out a way to do what I need but I don't know how to write the formula for it. I hope I can explain this correctly.

I figure that if I create 2 calculated fields (c_even and c_odd) instead of one I can have one field set a 0 for every even house number and in the other field a 1 for every odd house number. That way when I do my multi field sort I can do the even addresses ascending and the odd addresses descending(or vise versa)

The sort would look like this:

Sort Street_Name (Acsending)

Sort c_even (Ascending)

Sort c_odd (Descending)

Sort House_Number (Ascending)

Is there a way to use the MOD function set a result of 0 for an even address (or a 1 for the c_odd field) and then leave the field blank if the number is odd, or would that be a completely different function?

Any comments welcome.

Thanks,

David40

[comment]

If I understand correctly, there are 4 possible walk lists. If we take a street with 25 houses, the 4 options are:

Start at #1;

Start at #2;

Start at #25;

Start at #24.

The two options I gave you earlier (make either odd or even negative, and invert the result or not) provide all the possible variations (two Boolean choices, 4 possible results).

[David40]

That's probably true but I have not been able to figure out how to apply it in a way that gives me a list I can use. Using your example I should end up with a list that starts with Street #1 ascending starting with house #1 ascending, then #24 descending, then repeat the same thing for street #2 House #1 ascending, then #24 descending, and so on. It needs to represent walking up one side of the street then walking back down the other side, moving over to the next street and going up and back again, and so on. I did a bit of research on this and the USPS and UPS among others call it a sequential deliver list or something to that effect. Of course I'm sure they use a formula that's much more complex as it takes the cross street into account as well. That I would not even attempt. If I could get my calculated field results into two separate fields I think I can get it to do what I need.

[comment]

Do you want the same order for all streets? If yes, which one of the 4 mentioned should it be? If not, how do you propose to determine the correct order for each street?

[David40]

Yes, same order for all streets. It would need to be two methods:

Start at #1 (odd house numbers) Ascending and Start at #24(even house numbers)Descending

These would be placed after first Sorting the Street Name Ascending, and lastly sorting by House Number

For now the street order will be alphanumeric in an ascending order until I can create a value table for the named streets.

[comment]

I am afraid you are losing me rapidly: to me, same order for all streets means same order for all streets - not two methods. If you start at #1, then you do NOT start at #24. Please clarify.

[David40]

Sure, be glad to. One side of the street has all even numbered houses and the other side has all odd numbers. So in order to print a walk list to go up and down the street in order house by house, with out having to cross the street and zig zag you need a list of all the houses on that street; even numbers ascending and odd descending or vise versa. It's like a postal carrier route. Your mail man doesn't zig zag up the street delivering mail, he would walk up one side and then back down the other. Less walking that way.

[comment]

Yes, that much is obvious. What is not obvious to me is what do you actually want. You say "even numbers ascending and odd descending or vise versa". I don't know what to make out of the "or vise versa" part.

[David40]

Vise versa just means if I wanted to switch it around to say start at the other end of the street. To further clarify. Using the example of 25 houses numbered 1 to 25 and for this example Street #1. My printed list should look like this:

1 - 1st Street

3 - 1st Street

5 - 1st street

(and so on up to)

25 - 1st Street

then down

24 - 1st Street

22 - 1st Street

20 - 1st Street

( and so forth down to)

4 - 1st Street

2 - 1st Street

Then repeat the same thing for 2nd Street and 3rd Street etc.

The list I created with the first formula you gave me groups the houses by even and odd but they are both ascending (or descending if I reverse it)and looks like this:

1 - 1st Street

3 - 1st Street

5 - 1st Street

(and so on to the end)

25 - 1st Street

( then it does the even houses)

2 - 1st Street

4 - 1st Street

( and so on up to the end)

24 - 1st street

I suppose the walker could just skip to the last house on the sheet and work backwards but I'd prefer to have it in the correct order to prevent confusion.

[comment]

To sort in the order specified in your example, define the calculation field as =

1 / Case ( Mod ( HouseNumber ; 2 ) ; -HouseNumber ; HouseNumber )

and sort by it in ascending order (after sorting by street, of course).

To switch to the other (i.e. even) SIDE of the street (but still starting at the same end), sort in descending order.

To switch to the other END of the street, use:

Case ( Mod ( HouseNumber ; 2 ) ; -HouseNumber ; HouseNumber )

Here too, ascending order will put odd numbers first, while descending order will start with even numbers.

[Vaughan]

Might as well start on a "travelling salesman" optimisation Comment, it'll be the next question. :(

[comment]

Yeah, right - could I borrow your Q-36 for a moment?