By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,231 Members | 1,707 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,231 IT Pros & Developers. It's quick & easy.

consistency in abbreviations

P: n/a
If one user enters "Laurel Drive" and another enters "Laurel Dr." and still
another forgets the period and enters "Laurel Dr" - is there a way to
enforce consistency so that "103 Laurel Drive" doesn't appear as three
different addresses when entered by the different users?

TIA
Nov 12 '05 #1
Share this Question
Share on Google+
19 Replies


P: n/a
"Catherine Jo Morgan" <cj******@hemc.net> wrote in
news:R8********************@hemc.net:
If one user enters "Laurel Drive" and another enters "Laurel
Dr." and still another forgets the period and enters "Laurel
Dr" - is there a way to enforce consistency so that "103
Laurel Drive" doesn't appear as three different addresses when
entered by the different users?

TIA
Proper database design! Users should not need to be re-entering
repeating data. If you are entering the address for each order from
a customer, you need to redesign such that customers have a table,
which contains the customer's ID, name, address, phone, etc.

When a new order comes in, you simply link to the customer's ID
number, and don't have to retype everything.

Bob Q.




Nov 12 '05 #2

P: n/a
"Catherine Jo Morgan" <cj******@hemc.net> wrote in message
news:R8********************@hemc.net...
If one user enters "Laurel Drive" and another enters "Laurel Dr." and still
another forgets the period and enters "Laurel Dr" - is there a way to
enforce consistency so that "103 Laurel Drive" doesn't appear as three
different addresses when entered by the different users?


No.

In such situations the best you can do is create simple, but flexible
filtering/search functionality and allow the user to determine with each new
record insertion whether that entity is already in the database prior to
entering it. This problem is as old as data processing.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 12 '05 #3

P: n/a
"Bob Quintal" <bq******@generation.net> wrote in message
news:13******************************@news.teranew s.com...
"Catherine Jo Morgan" <cj******@hemc.net> wrote in
news:R8********************@hemc.net:
If one user enters "Laurel Drive" and another enters "Laurel
Dr." and still another forgets the period and enters "Laurel
Dr" - is there a way to enforce consistency so that "103
Laurel Drive" doesn't appear as three different addresses when
entered by the different users?

TIA


Proper database design! Users should not need to be re-entering
repeating data. If you are entering the address for each order from
a customer, you need to redesign such that customers have a table,
which contains the customer's ID, name, address, phone, etc.

When a new order comes in, you simply link to the customer's ID
number, and don't have to retype everything.


But the OP didn't indicate that this is an Orders database. Perhaps they are
entering data into the Customers table itself, or a Prospects table, or a
Survey, etc..
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 12 '05 #4

P: n/a
Catherine Jo Morgan wrote:
If one user enters "Laurel Drive" and another enters "Laurel Dr." and still
another forgets the period and enters "Laurel Dr" - is there a way to
enforce consistency so that "103 Laurel Drive" doesn't appear as three
different addresses when entered by the different users?

TIA


If this is a recurring problem for new entries, set up a Streets table.
Base a combo box on the table and LimitToList the street names. If a new
street needs to be added, use the NotInList event to do so.

If this is an existing problem and you wish to have consistency on
already entered records, I think you can run an Update query to search
for Laurel D and update the field to whichever you wanted, Laurel Dr,
Laurel Dr., or Laurel Drive.

Let's pick 'Laurel Drive'.
Warning: Air Code ..
Update YourTable Set YourTable.Address =
Left([Address],InStr([Address],"Laurel D")-1) & " Laurel Drive")
Where InStr([Address],"Laurel D")>0;

This will lose every thing after the street name, so if there is an
address
103 Laurel Dr, Apt. 202.
you'll need to adapt the Update query for this.

--
Fred
Please reply only to this newsgroup.
I do not reply to personal email.
Nov 12 '05 #5

P: n/a
In article <4W*********************@bgtnsc05-news.ops.worldnet.att.net>,
fg******@example.invalid says...
Catherine Jo Morgan wrote:
If one user enters "Laurel Drive" and another enters "Laurel Dr." and still
another forgets the period and enters "Laurel Dr" - is there a way to
enforce consistency so that "103 Laurel Drive" doesn't appear as three
different addresses when entered by the different users?

TIA


If this is a recurring problem for new entries, set up a Streets table.
Base a combo box on the table and LimitToList the street names. If a new
street needs to be added, use the NotInList event to do so.

If this is an existing problem and you wish to have consistency on
already entered records, I think you can run an Update query to search
for Laurel D and update the field to whichever you wanted, Laurel Dr,
Laurel Dr., or Laurel Drive.

Let's pick 'Laurel Drive'.
Warning: Air Code ..
Update YourTable Set YourTable.Address =
Left([Address],InStr([Address],"Laurel D")-1) & " Laurel Drive")
Where InStr([Address],"Laurel D")>0;

This will lose every thing after the street name, so if there is an
address
103 Laurel Dr, Apt. 202.
you'll need to adapt the Update query for this.

You need to learn about normalization!
Nov 12 '05 #6

P: n/a
What if there's more than one customer in a street?

--
Michael Hopwood (Phobos)
"Bob Quintal" <bq******@generation.net> wrote in message
news:13******************************@news.teranew s.com...
"Catherine Jo Morgan" <cj******@hemc.net> wrote in
news:R8********************@hemc.net:
If one user enters "Laurel Drive" and another enters "Laurel
Dr." and still another forgets the period and enters "Laurel
Dr" - is there a way to enforce consistency so that "103
Laurel Drive" doesn't appear as three different addresses when
entered by the different users?

TIA


Proper database design! Users should not need to be re-entering
repeating data. If you are entering the address for each order from
a customer, you need to redesign such that customers have a table,
which contains the customer's ID, name, address, phone, etc.

When a new order comes in, you simply link to the customer's ID
number, and don't have to retype everything.

Bob Q.



Nov 12 '05 #7

P: n/a
"Catherine Jo Morgan" <cj******@hemc.net> wrote in message news:<R8********************@hemc.net>...
If one user enters "Laurel Drive" and another enters "Laurel Dr." and still
another forgets the period and enters "Laurel Dr" - is there a way to
enforce consistency so that "103 Laurel Drive" doesn't appear as three
different addresses when entered by the different users?

TIA


Here's a practical suggestion that might *reduce* the confusion.
Separate the entry of the street name into two bits. One is the street
"name" and the other is the street "type" which you can represent as a
drop box that only allows certain values like Ln, Rd, St, Ave, Ct,
Cir, Way and Tr. You might also have to allow additions to this list,
as well as allow blank entries (e.g. for Broadway) You might also want
to disallow the addition of any of the specified names spelled out, so
the user wouldn't be allowed to add "Lane" or "Road" to the list.
Nov 12 '05 #8

P: n/a
ri*********@hotmail.com (Rick Brandt) wrote in
<bp************@ID-98015.news.uni-berlin.de>:
"Catherine Jo Morgan" <cj******@hemc.net> wrote in message
news:R8********************@hemc.net...
If one user enters "Laurel Drive" and another enters "Laurel
Dr." and still another forgets the period and enters "Laurel Dr"
- is there a way to enforce consistency so that "103 Laurel
Drive" doesn't appear as three different addresses when entered
by the different users?


No.

In such situations the best you can do is create simple, but
flexible filtering/search functionality and allow the user to
determine with each new record insertion whether that entity is
already in the database prior to entering it. This problem is as
old as data processing.


Well, you could have the AfterUpdate of the address field process
and regularize the data based on a table of abbreviations. I have
just such an implementation pending for one of my clients.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #9

P: n/a
"Rick Brandt" <ri*********@hotmail.com> wrote in
news:bp*************@ID-98015.news.uni-berlin.de:
"Bob Quintal" <bq******@generation.net> wrote in message
news:13******************************@news.teranew s.com...
"Catherine Jo Morgan" <cj******@hemc.net> wrote in
news:R8********************@hemc.net:
> If one user enters "Laurel Drive" and another enters
> "Laurel Dr." and still another forgets the period and
> enters "Laurel Dr" - is there a way to enforce consistency
> so that "103 Laurel Drive" doesn't appear as three
> different addresses when entered by the different users?
>
> TIA
Proper database design! Users should not need to be
re-entering repeating data. If you are entering the address
for each order from a customer, you need to redesign such
that customers have a table, which contains the customer's
ID, name, address, phone, etc.

When a new order comes in, you simply link to the customer's
ID number, and don't have to retype everything.


But the OP didn't indicate that this is an Orders database.
Perhaps they are entering data into the Customers table
itself, or a Prospects table, or a Survey, etc..

If it's the customers table itself, then a table of city streets is
a possible solution. The "idea" is to avoid repetition by proper
database design.

If it's a survey, the same concept applies.

Bob Q



Nov 12 '05 #10

P: n/a
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.86.. .
ri*********@hotmail.com (Rick Brandt) wrote in
<bp************@ID-98015.news.uni-berlin.de>:
"Catherine Jo Morgan" <cj******@hemc.net> wrote in message
news:R8********************@hemc.net...
If one user enters "Laurel Drive" and another enters "Laurel
Dr." and still another forgets the period and enters "Laurel Dr"
- is there a way to enforce consistency so that "103 Laurel
Drive" doesn't appear as three different addresses when entered
by the different users?


No.

In such situations the best you can do is create simple, but
flexible filtering/search functionality and allow the user to
determine with each new record insertion whether that entity is
already in the database prior to entering it. This problem is as
old as data processing.


Well, you could have the AfterUpdate of the address field process
and regularize the data based on a table of abbreviations. I have
just such an implementation pending for one of my clients.


That would certainly help if the way the street is entered is your only concern.
I assumed that the OP was using street name as an example of the variable way
humans will enter data. You still have to deal with "John" vs. "Jonathon",
"Saint Louis" vs. "St. Louis", etc.. We even found a customer entered into our
users database once as...

555 Hiway 5 North

....and again as...

Suite 34 Such n' Such Mall.

(both correct BTW).
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 12 '05 #11

P: n/a
Not sure to whom you're referring, or why?
You need to learn about normalization!

Nov 12 '05 #12

P: n/a
This looks like a practical solution, pretty foolproof.

"Brian" <lo************@hotmail.com> wrote in message
news:f3**************************@posting.google.c om...
Here's a practical suggestion that might *reduce* the confusion.
Separate the entry of the street name into two bits. One is the street
"name" and the other is the street "type" which you can represent as a
drop box that only allows certain values like Ln, Rd, St, Ave, Ct,
Cir, Way and Tr. You might also have to allow additions to this list,
as well as allow blank entries (e.g. for Broadway) You might also want
to disallow the addition of any of the specified names spelled out, so
the user wouldn't be allowed to add "Lane" or "Road" to the list.

Nov 12 '05 #13

P: n/a
On Wed, 19 Nov 2003 16:37:18 -0500 in comp.databases.ms-access,
"Catherine Jo Morgan" <cj******@hemc.net> wrote:
Not sure to whom you're referring, or why?
You need to learn about normalization!

Perhaps he was steering toward the fact that you shouldn't be storing
any address more than once, it should be in an address table and have
people that live there pointing to it.

This in itself presents a problem, if Bert and Ernie live at the same
address and Bert moves out, someone updates Bert's address and without
thinking they've moved Ernie as well.

--
A)bort, R)etry, I)nfluence with large hammer.
Nov 12 '05 #14

P: n/a
rkc

"Trevor Best" <bouncer@localhost> wrote in message
news:st********************************@4ax.com...
On Wed, 19 Nov 2003 16:37:18 -0500 in comp.databases.ms-access,
"Catherine Jo Morgan" <cj******@hemc.net> wrote:
Not sure to whom you're referring, or why?
You need to learn about normalization!

Perhaps he was steering toward the fact that you shouldn't be storing
any address more than once, it should be in an address table and have
people that live there pointing to it.

This in itself presents a problem, if Bert and Ernie live at the same
address and Bert moves out, someone updates Bert's address and without
thinking they've moved Ernie as well.


How would you go about preventing that from happening, using that type
of structure and just the Jet database engine?


Nov 12 '05 #15

P: n/a
Well, that's why I'm making a relational database in access, to replace the
flat excel spreadsheet records.

"Trevor Best" <bouncer@localhost> wrote in message
news:st********************************@4ax.com...
On Wed, 19 Nov 2003 16:37:18 -0500 in comp.databases.ms-access,
"Catherine Jo Morgan" <cj******@hemc.net> wrote:
Not sure to whom you're referring, or why?
You need to learn about normalization!

Perhaps he was steering toward the fact that you shouldn't be storing
any address more than once, it should be in an address table and have
people that live there pointing to it.

This in itself presents a problem, if Bert and Ernie live at the same
address and Bert moves out, someone updates Bert's address and without
thinking they've moved Ernie as well.

--
A)bort, R)etry, I)nfluence with large hammer.

Nov 12 '05 #16

P: n/a
In the database I'm designing, Persons, Households, and Addresses are
separate tables. So if Bert moves out, the household relationship changes.
He gets a new household and new address, while Ernie stays with old
household and address. The old household mailing address changes from Bert
and Ernie to whatever it is now.

"rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:0c********************@twister.nyroc.rr.com.. .

"Trevor Best" <bouncer@localhost> wrote in message
news:st********************************@4ax.com...
On Wed, 19 Nov 2003 16:37:18 -0500 in comp.databases.ms-access,
"Catherine Jo Morgan" <cj******@hemc.net> wrote:
Not sure to whom you're referring, or why?

> You need to learn about normalization!

Perhaps he was steering toward the fact that you shouldn't be storing
any address more than once, it should be in an address table and have
people that live there pointing to it.

This in itself presents a problem, if Bert and Ernie live at the same
address and Bert moves out, someone updates Bert's address and without
thinking they've moved Ernie as well.


How would you go about preventing that from happening, using that type
of structure and just the Jet database engine?


Nov 12 '05 #17

P: n/a
On Wed, 19 Nov 2003 20:40:25 -0500 in comp.databases.ms-access,
"Catherine Jo Morgan" <cj******@hemc.net> wrote:
Well, that's why I'm making a relational database in access, to replace the
flat excel spreadsheet records.


Actually Excel has a feature that helps in that respect, i.e. when you
start typing something it drops down a list of similar things you've
typed before. You could replicate this functionality in Access
although for addresses you'd need to strip off the number to search
for street names already in there. This could be slow in a large
database although if you store number and street in separate columns
then it's feasable.

I've not tried it myself but something along the lines of an event
procedure on Keypress to change the rowsource property of a combo box,
e.g.

(air code)
Sub cboStreet_KeyPress(KeyASCII As Inteher)
cboStreet.Rowsource = "select Street from tblAddress Where Street
Like cboStreet.Text & "*"
End Sub
--
A)bort, R)etry, I)nfluence with large hammer.
Nov 12 '05 #18

P: n/a
On Thu, 20 Nov 2003 00:49:00 GMT in comp.databases.ms-access, "rkc"
<rk*@yabba.dabba.do.rochester.rr.bomb> wrote:

"Trevor Best" <bouncer@localhost> wrote in message
news:st********************************@4ax.com.. .

This in itself presents a problem, if Bert and Ernie live at the same
address and Bert moves out, someone updates Bert's address and without
thinking they've moved Ernie as well.


How would you go about preventing that from happening, using that type
of structure and just the Jet database engine?


If it were me I'd use a BeforeUpdate event on the address to check if
any other individuals lived there and prompt the user if they're sure
they want to move those people too, else have a button to change
address.

--
A)bort, R)etry, I)nfluence with large hammer.
Nov 12 '05 #19

P: n/a
rkc

"Trevor Best" <bouncer@localhost> wrote in message
news:lj********************************@4ax.com...
On Thu, 20 Nov 2003 00:49:00 GMT in comp.databases.ms-access, "rkc"
<rk*@yabba.dabba.do.rochester.rr.bomb> wrote:

"Trevor Best" <bouncer@localhost> wrote in message
news:st********************************@4ax.com.. .

This in itself presents a problem, if Bert and Ernie live at the same
address and Bert moves out, someone updates Bert's address and without
thinking they've moved Ernie as well.


How would you go about preventing that from happening, using that type
of structure and just the Jet database engine?


If it were me I'd use a BeforeUpdate event on the address to check if
any other individuals lived there and prompt the user if they're sure
they want to move those people too, else have a button to change
address.


Seems to me you would first have to search for an address. Maybe Bert
is moving in with Big Bird who is already in the Sesame System. If Bert
is moving away from where the air is clean, say to Los Angeles, maybe
then you'd have to add an address. Unless Bert was moving in with
Snuffleupagus who had already moved to LA.

I'm not sure the benefits outweigh the complications. Certainly not
enough to say storing Address as a seperate entity is the Right way.


Nov 12 '05 #20

This discussion thread is closed

Replies have been disabled for this discussion.