seperating door numbers from addresses in an update query

[efandango]

I have a table/Query:

SELECT tbl_Points.Point_ID, tbl_Points.Run_point_Venue,
tbl_Points.Run_point_Address, tbl_Points.Run_Point_Postcode,
tbl_Points.Run_point_Door_No, tbl_Points.Run_point_Address_New
FROM tbl_Points;


Where I want to cut and copy(update) the door numbers from [Run_point_Address]
into [Run_point_Door_No] and [Run_point_Address_New], respectively.

The door numbers are always at the start of the address and can be up to 3
digits long. Sometimes they can have a single letter suffix like this: ###a,
and sometime they can be in this format 12-13 Acacia Avenue. But if these
last two formats are too complex to do, I will settle for just doing those
types manually.

 

[Steve Schapel]

Efandango,

Do *all records* have a door number at the start of the address?

Can we say that for all door numbers, it will always be "the data that is to
the left of the first 'space' in the address"?

 

[John W. Vinson]

I have a table/Query:

SELECT tbl_Points.Point_ID, tbl_Points.Run_point_Venue,
tbl_Points.Run_point_Address, tbl_Points.Run_Point_Postcode,
tbl_Points.Run_point_Door_No, tbl_Points.Run_point_Address_New
FROM tbl_Points;


Where I want to cut and copy(update) the door numbers from [Run_point_Address]
into [Run_point_Door_No] and [Run_point_Address_New], respectively.

The door numbers are always at the start of the address and can be up to 3
digits long. Sometimes they can have a single letter suffix like this: ###a,
and sometime they can be in this format 12-13 Acacia Avenue. But if these
last two formats are too complex to do, I will settle for just doing those
types manually.

IF - and it's a pretty big if - the door number is always first and always
followed by a blank, you can use the first blank to split: the door number
would be

Left([RunPointAddress], InStr([RunPointAddress], " ") - 1)

and the rest would be

Mid([RunPointAddress], InStr([RunPointAddress], " ") + 1)

This will fail for addresses (real ones, people I know) such as
"1812 1/2 Columbia Pl." or "Ballacraine".

 

[david]

No system works always for any real address - you will always
have to verify by checking. You normally can't generate this
information dynamically when it is needed: you have to store the
new Door_No and Address_New in a table, so that you can
build up your table of hand-corrected exceptions, and so that
you can run a series of translation queries across it -- your translation
query quickly gets too complex to have it all in one query.

This is one way to start finding all the exceptions:

SELECT val(tbl_Points.Run_point_Address) as n,

Trim(Left([RunPointAddress], InStr([RunPointAddress], " ") - 1))
as [Run_point_Door_No] ,

Trim(Mid([RunPointAddress], InStr([RunPointAddress], " ") + 1))
as [Run_point_Address_New],

IIF(trim(str(n)) = [Run_point_Door_No], null,"fail") as check,

tbl_Points.Point_ID, tbl_Points.Run_point_Venue,

tbl_Points.Run_point_Address, tbl_Points.Run_Point_Postcode,
tbl_Points.Run_point_Door_No, tbl_Points.Run_point_Address_New

I have a table/Query:

SELECT tbl_Points.Point_ID, tbl_Points.Run_point_Venue,
tbl_Points.Run_point_Address, tbl_Points.Run_Point_Postcode,
tbl_Points.Run_point_Door_No, tbl_Points.Run_point_Address_New
FROM tbl_Points;


Where I want to cut and copy(update) the door numbers from
[Run_point_Address]
into [Run_point_Door_No] and [Run_point_Address_New], respectively.

The door numbers are always at the start of the address and can be up to 3
digits long. Sometimes they can have a single letter suffix like this:
###a,
and sometime they can be in this format 12-13 Acacia Avenue. But if these
last two formats are too complex to do, I will settle for just doing those
types manually.

 

[efandango]

Steve,

Unfortunately some addresses do not have a door number, and therefore means
that the some data that is to left of the first space will be part of the
address name and not the door number, as in John Vinson's example of
'Ballacraine'
where he say's;

"This will fail for addresses (real ones, people I know) such as
"1812 1/2 Columbia Pl." or "Ballacraine".


David's solution is pretty neat, in that it tells me what lines have errors,
where the 'address' part has been mistakenly taken for a door number.

Efandango,

Do *all records* have a door number at the start of the address?

Can we say that for all door numbers, it will always be "the data that is to
the left of the first 'space' in the address"?

--
Steve Schapel, Microsoft Access MVP

I have a table/Query:

SELECT tbl_Points.Point_ID, tbl_Points.Run_point_Venue,
tbl_Points.Run_point_Address, tbl_Points.Run_Point_Postcode,
tbl_Points.Run_point_Door_No, tbl_Points.Run_point_Address_New
FROM tbl_Points;


Where I want to cut and copy(update) the door numbers from
[Run_point_Address]
into [Run_point_Door_No] and [Run_point_Address_New], respectively.

The door numbers are always at the start of the address and can be up to 3
digits long. Sometimes they can have a single letter suffix like this:
###a,
and sometime they can be in this format 12-13 Acacia Avenue. But if these
last two formats are too complex to do, I will settle for just doing those
types manually.

 

[efandango]

John,

Yes, the door number is always first and is always followed by a blank, but
as you say; "Ballacraine" and the like are fails. Is there any way of simply
saying anything that ends with a number, followed by a blank gets copied?

I have a table/Query:

SELECT tbl_Points.Point_ID, tbl_Points.Run_point_Venue,
tbl_Points.Run_point_Address, tbl_Points.Run_Point_Postcode,
tbl_Points.Run_point_Door_No, tbl_Points.Run_point_Address_New
FROM tbl_Points;


Where I want to cut and copy(update) the door numbers from [Run_point_Address]
into [Run_point_Door_No] and [Run_point_Address_New], respectively.

The door numbers are always at the start of the address and can be up to 3
digits long. Sometimes they can have a single letter suffix like this: ###a,
and sometime they can be in this format 12-13 Acacia Avenue. But if these
last two formats are too complex to do, I will settle for just doing those
types manually.

IF - and it's a pretty big if - the door number is always first and always
followed by a blank, you can use the first blank to split: the door number
would be

Left([RunPointAddress], InStr([RunPointAddress], " ") - 1)

and the rest would be

Mid([RunPointAddress], InStr([RunPointAddress], " ") + 1)

This will fail for addresses (real ones, people I know) such as
"1812 1/2 Columbia Pl." or "Ballacraine".

 

[John W. Vinson]

John,

Yes, the door number is always first and is always followed by a blank, but
as you say; "Ballacraine" and the like are fails. Is there any way of simply
saying anything that ends with a number, followed by a blank gets copied?

You could use a criterion on the query

LIKE "*# *"

 

[efandango]

John,

With your query, I have managed to get to two different columns as you
intended. NOw what I need to do is play around with various permutations
based on columns having and not having addresses with digits in them. Can you
tell me how I can express the following options?

1. give me this column with any address that do not have numbers in the
address line

2. give me this column with any address that only has numbers and letters in
the address line


with the syntax for these two, I should be able to concatenate the
non-number addresses, and also delete the 'with numbers' addresses, leaving
me to columns that I can then update into one column.

I have a table/Query:

SELECT tbl_Points.Point_ID, tbl_Points.Run_point_Venue,
tbl_Points.Run_point_Address, tbl_Points.Run_Point_Postcode,
tbl_Points.Run_point_Door_No, tbl_Points.Run_point_Address_New
FROM tbl_Points;


Where I want to cut and copy(update) the door numbers from [Run_point_Address]
into [Run_point_Door_No] and [Run_point_Address_New], respectively.

The door numbers are always at the start of the address and can be up to 3
digits long. Sometimes they can have a single letter suffix like this: ###a,
and sometime they can be in this format 12-13 Acacia Avenue. But if these
last two formats are too complex to do, I will settle for just doing those
types manually.

IF - and it's a pretty big if - the door number is always first and always
followed by a blank, you can use the first blank to split: the door number
would be

Left([RunPointAddress], InStr([RunPointAddress], " ") - 1)

and the rest would be

Mid([RunPointAddress], InStr([RunPointAddress], " ") + 1)

This will fail for addresses (real ones, people I know) such as
"1812 1/2 Columbia Pl." or "Ballacraine".

 

[efandango]

John,

when I run two queries from a total number of 7259 records, containing the
following two criteria.

Like "*# *"
Not Like "*# *"

I Get an overall shortfall of 267 records, which appear to be the records
that return as #Error. This occurs when the address has only one word, such
as, 'parkside'.

Is there a way I can deal with the single word 'error' records. When I run
another query on the 1st query and ask for records containing #error, I get
nothing returned. If I can somehow get 'ownership' of these records, I should
be able to crack the whole problem.

 

[John W. Vinson]

John,

when I run two queries from a total number of 7259 records, containing the
following two criteria.

Like "*# *"
Not Like "*# *"

I Get an overall shortfall of 267 records, which appear to be the records
that return as #Error. This occurs when the address has only one word, such
as, 'parkside'.

Is there a way I can deal with the single word 'error' records. When I run
another query on the 1st query and ask for records containing #error, I get
nothing returned. If I can somehow get 'ownership' of these records, I should
be able to crack the whole problem.

A criterion of Not Like "*# *" should return "Parkside" as one of the hits,
since that field doesn't contain a digit followed by a blank. Are you using
these two criteria on separate queries (good) or on two OR lines of the same
query? If the latter, then you are returning all records ("show me all the
records that contain a number along with all the records that don't contain a
number").

Try these as two separate queries. The first one can be the update query
fixing the door numbers; the second would be just a select query allowing
manual correction.

 

[John W. Vinson]

John,

With your query, I have managed to get to two different columns as you
intended. NOw what I need to do is play around with various permutations
based on columns having and not having addresses with digits in them. Can you
tell me how I can express the following options?

1. give me this column with any address that do not have numbers in the
address line

2. give me this column with any address that only has numbers and letters in
the address line


with the syntax for these two, I should be able to concatenate the
non-number addresses, and also delete the 'with numbers' addresses, leaving
me to columns that I can then update into one column.

I can't think of any good way to get two calculated fields for these
subcategories and still have them editable.

 

[efandango]

John,

between the two seperate queries, as you recommended, plus an additional
query for the '#Errors', which I managed to get control of by specifying a
'null' criteria in a column that the '#Errors' gave a blank to; I was able to
completely resolve the issue.

Thanks for your help; it's much appreciated.

Eric