Querying for postcodes within an address against another masters t

[Guest]

THE PROBLEM

I have a master ‘Runs’ Table listing regular vehicle runs on a ‘from’ – ‘to’
basis.

For example:

Runs_Table

Run_No (Autonumber Index)
Run_From (Address)
[From_PCode] (Postcode)
Run_To (Address)
[To_PCode] (Postcode)


[Run_No] [Run_From] [From_Pcode] [Run_To] [To_PCode]

6 Sub Depot W2 Main Depot W2
8 Big Farm SW9 Little Farm SW9

Along the vehicle’s run are 8 (regular) delivery points stored in a table
called Run_Points (linked by the ‘Run_No’ field)

The format is

Run_No
point_Venue_A
point_Address_A

point_Venue_B
point_Address_B

And so on… (upto 8 pairs)


The Field containing the Address also contains the postcode which is
separated by a comma.

For example:

[point_Venue_A] [point_Address_A] [point_Venue_B] [point_Address_B]

Post Office High St, W2 Civic Centre South
St, W2
Hospital East St, SW9 School Main
St, SW9




What I want to do

I want to be able to run a query/process that takes the 2 main postcodes
from the Run tables and looks up the various corresponding Postcodes in other
points runs to let the user know that while on that run, there are other
locations close by with the same postcode, albeit on a different run, that he
may want to deliver to.

The problem is that the postcode is within the same field as the address,
but separated by a (hopefully) useful comma. Does anyone know how I would go
about parsing this data, or building a suitable query to do the job?. I
realise the simplest way would be to separate the Run_Points postcodes, but
if I were to do that, the whole database would need a re-write (a task that
fills me with dread).

 

[John Spencer]

If and only if there is always a comma before the post code and there is
never any other comma in the address field and there is nothing after
the postcode in the address, you could use a calculated field to derive
the postcode. That formula would look like

Field: PostCodeFrom: Trim(Mid(Run_From,Instr(1,Run_From,",")+1))

Warning this could be slow if a lot of records are involved.

If you have something different than the above for the address format
then you really should consider a redesign where the postcode is in a
separate field. It is not impossible to use a VBA function to extract
the postcode if the address is differently formatted, but it will become
more and more complex to do well.

 

[Guest]

John,

Yes, The comma only ever appears after the postcode. (if any errors throw,
I'll manually reconfigure them)

Can you give me some pointers on how/where to use your formula, Query/VBA?.
Also, Is it possible to be able to compare both 'From_Pcode' and 'To_Pcode'
in the master table with the 'comma' postcodes in the sub table. or will it
require two seperate processes?.

Am i making sense? (already my head is spinning...)

If and only if there is always a comma before the post code and there is
never any other comma in the address field and there is nothing after
the postcode in the address, you could use a calculated field to derive
the postcode. That formula would look like

Field: PostCodeFrom: Trim(Mid(Run_From,Instr(1,Run_From,",")+1))

Warning this could be slow if a lot of records are involved.

If you have something different than the above for the address format
then you really should consider a redesign where the postcode is in a
separate field. It is not impossible to use a VBA function to extract
the postcode if the address is differently formatted, but it will become
more and more complex to do well.

THE PROBLEM

I have a master ‘Runs’ Table listing regular vehicle runs on a ‘from’ – ‘to’
basis.

For example:

Runs_Table

Run_No (Autonumber Index)
Run_From (Address)
[From_PCode] (Postcode)
Run_To (Address)
[To_PCode] (Postcode)


[Run_No] [Run_From] [From_Pcode] [Run_To] [To_PCode]

6 Sub Depot W2 Main Depot W2
8 Big Farm SW9 Little Farm SW9

Along the vehicle’s run are 8 (regular) delivery points stored in a table
called Run_Points (linked by the ‘Run_No’ field)

The format is

Run_No
point_Venue_A
point_Address_A

point_Venue_B
point_Address_B

And so on… (upto 8 pairs)


The Field containing the Address also contains the postcode which is
separated by a comma.

For example:

[point_Venue_A] [point_Address_A] [point_Venue_B] [point_Address_B]

Post Office High St, W2 Civic Centre South
St, W2
Hospital East St, SW9 School Main
St, SW9




What I want to do

I want to be able to run a query/process that takes the 2 main postcodes
from the Run tables and looks up the various corresponding Postcodes in other
points runs to let the user know that while on that run, there are other
locations close by with the same postcode, albeit on a different run, that he
may want to deliver to.

The problem is that the postcode is within the same field as the address,
but separated by a (hopefully) useful comma. Does anyone know how I would go
about parsing this data, or building a suitable query to do the job?. I
realise the simplest way would be to separate the Run_Points postcodes, but
if I were to do that, the whole database would need a re-write (a task that
fills me with dread).

 

[Guest]

John,

Scratch my last reply...


Duh!, now i see. this formula goes in the query grid as a seperate
field/formula. I think it is working for me, the codes are certainly
matching, but i need to digest how to ge tit to work further for me.

So far so thanks. Appreciated.

John,

Yes, The comma only ever appears after the postcode. (if any errors throw,
I'll manually reconfigure them)

Can you give me some pointers on how/where to use your formula, Query/VBA?.
Also, Is it possible to be able to compare both 'From_Pcode' and 'To_Pcode'
in the master table with the 'comma' postcodes in the sub table. or will it
require two seperate processes?.

Am i making sense? (already my head is spinning...)

If and only if there is always a comma before the post code and there is
never any other comma in the address field and there is nothing after
the postcode in the address, you could use a calculated field to derive
the postcode. That formula would look like

Field: PostCodeFrom: Trim(Mid(Run_From,Instr(1,Run_From,",")+1))

Warning this could be slow if a lot of records are involved.

If you have something different than the above for the address format
then you really should consider a redesign where the postcode is in a
separate field. It is not impossible to use a VBA function to extract
the postcode if the address is differently formatted, but it will become
more and more complex to do well.

THE PROBLEM

I have a master ‘Runs’ Table listing regular vehicle runs on a ‘from’ – ‘to’
basis.

For example:

Runs_Table

Run_No (Autonumber Index)
Run_From (Address)
[From_PCode] (Postcode)
Run_To (Address)
[To_PCode] (Postcode)


[Run_No] [Run_From] [From_Pcode] [Run_To] [To_PCode]

6 Sub Depot W2 Main Depot W2
8 Big Farm SW9 Little Farm SW9

Along the vehicle’s run are 8 (regular) delivery points stored in a table
called Run_Points (linked by the ‘Run_No’ field)

The format is

Run_No
point_Venue_A
point_Address_A

point_Venue_B
point_Address_B

And so on… (upto 8 pairs)


The Field containing the Address also contains the postcode which is
separated by a comma.

For example:

[point_Venue_A] [point_Address_A] [point_Venue_B] [point_Address_B]

Post Office High St, W2 Civic Centre South
St, W2
Hospital East St, SW9 School Main
St, SW9




What I want to do

I want to be able to run a query/process that takes the 2 main postcodes
from the Run tables and looks up the various corresponding Postcodes in other
points runs to let the user know that while on that run, there are other
locations close by with the same postcode, albeit on a different run, that he
may want to deliver to.

The problem is that the postcode is within the same field as the address,
but separated by a (hopefully) useful comma. Does anyone know how I would go
about parsing this data, or building a suitable query to do the job?. I
realise the simplest way would be to separate the Run_Points postcodes, but
if I were to do that, the whole database would need a re-write (a task that
fills me with dread).

 

[John Spencer]

What I gave you can be used in a query as a calculated column.


Field: PostCodeFrom: Trim(Mid(Run_From,Instr(1,Run_From,",")+1))

As for the rest of what you are trying to do, I thought you already had
a solution, but just needed to solve this problem.

Since I don't know what is in your "sub table" in terms of fields, I am
unsure of how to advise you.

The speed of anything you do would significantly improve if you had the
Postcodes in separate fields. I suspect that you could use an non-equi
join in a query using the calculated value as part of the join and
joining to two instances of your "sub table" from each of the From and
To postcode calculations.

 

[Guest]

John,

I hope this makes things clearer for you.

This is my Master table: (Containg just two addresses. From & To)

[Run_No] [Run_From] [From_Pcode] [Run_To] [To_PCode]

6 Sub Depot W2 Main Depot W2
8 Big Farm SW9 Little Farm SE17


This is my Sub Table

[Run_No] [point_Venue_A] [point_Address_A] [point_Venue_B] [point_Address_B]

45 Post Office High St, W2 Civic Centre
South St, W2
23 Hospital East St, SW9 School
Main St, SE19


The master table contains the ‘standard runs’

The sub table contains the ‘addresses’ (containing 8 pairs of addresses.
Venue and Address relating to a given Run No. in the master table)

The significant thing about the master table is that it has two main
postcodes. From & To. (as shown in Run No.8)

I want to be able to query the one table against the other so that the user
can see what other points relate to a given run that he may be doing that
day. (bearing in mind the postcode is part of the address) by comparing the
two master postcodes against any other runs that may have the same postcodes.

for example, he may be doing Run No.8, but with the query he can also see
that he can do a supplementary delivery to the Hospital (SW9) and the School
(SE17) on the same day.

Regards

Eric

 

[John Spencer]

Trim(Mid([Run_Point_Address_A],InStr(1,[Run_Point_Address_A],",")+1))

This works from the inside to the outside so the first thing that happens is

InStr(1,[Run_Point_Address_A],",") finds the location of the comma and then
we add 1 to we have the position of the first character after the comma.

Mid([Run_Point_Address_A],InStr(1,[Run_Point_Address_A],",")+1) returns the
string from the position to the end of the string. The number of characters
to return is not specified, so mid defaults to all the characters from the
start position to the end position.

Trim simply chops off any leading or trailing spaces. This is handy since
users are prone to entering no spaces to multiple spaces after a comma.
Normally one space, but sometimes none and sometimes 2 or 3 (or more).

xxx, AL 21
xxx, AL 21
xxx,AL 21

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John,

Thanks for helping with my problem, After a fair bit of trial and error
I'm
happy to say that your Formula helped immensly. What I had to do in order
for
it to work for me was to create a Union Query, taking the 8 Venue and
address
per run and get it down to a 2 field array. Once i did that, i was able to
use your Formula against this 2 field array. I also had to change the
field
definitions from:

Field: PostCodeFrom: Trim(Mid(Run_From,Instr(1,Run_From,",")+1))


TO

PostCodeFrom:
Trim(Mid([Run_Point_Address_A],InStr(1,[Run_Point_Address_A],",")+1))

to reflect the only relevant fields in the new unionised and slimmed down
table .

The only thing is i'm not sure what the various syntax means in your
formula, i tried to look them up referencing things like 'Trim and Instr,
but
couldn't figure out how the whole formuala actually works. Can you
explain?

in the meantime,

THANKS

regards

Eric

What I gave you can be used in a query as a calculated column.


Field: PostCodeFrom: Trim(Mid(Run_From,Instr(1,Run_From,",")+1))

As for the rest of what you are trying to do, I thought you already had
a solution, but just needed to solve this problem.

Since I don't know what is in your "sub table" in terms of fields, I am
unsure of how to advise you.

The speed of anything you do would significantly improve if you had the
Postcodes in separate fields. I suspect that you could use an non-equi
join in a query using the calculated value as part of the join and
joining to two instances of your "sub table" from each of the From and
To postcode calculations.