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
19  2174 
"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.
"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
"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
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.
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!
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.
"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. 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
"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
"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
Not sure to whom you're referring, or why? You need to learn about normalization!
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.
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.
"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?
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.
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?
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.
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.
"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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Markus Breuer |
last post by:
I have a question about oracle commit and transactions. Following scenario:
Process A performs a single sql-INSERT into a table and commits the
transaction. Then he informs process B (ipc) to...
|
by: Sergio |
last post by:
Hi everybody,
I have the following scenario. Several computers with shared disk in a
LAN. Each of these computer has a MySQL server that serves several
databases. I have several clients that...
|
by: bruce strom |
last post by:
I have a sql database
I run dbcc checkdb and get no errors
I go to backup the database in enterprise manager, and it won't
restore because it has internal consistency errors
I can go back to an...
|
by: George Hester |
last post by:
In JavaScript we have the if then else which, if there is not more than one statement following we do not need to use {}. For example:
if ()
;
works fine and so does
if ()
if()
;
|
by: lechequier |
last post by:
Let's say I define a list of pairs as follows:
>>l =
Can anyone explain why this does not work?
>>h = {}.update(l)
and instead I have to go:
>>h = {}
>>h.update(l)
to initialize a...
|
by: burn |
last post by:
Hello,
i am writing a program under linux in c and compile my code with make
and gcc.
Now i have 4 files: init.c/h and packets.c/h. Each header-file contains
some:
init.h:
struct xyz
{
|
by: VB Coder |
last post by:
I have tried the sample code associated with the article titled "Maintain Consistency With ASP.NET Templates" by Dan Wahlin http://www.fawcette.com/vsm/2003_12/online/dwahlin/default_pf.aspx
I...
|
by: sialater |
last post by:
Hello,
I've been trudging through the net trying to find possible abbreviations to find a specific date using strtotime()
What I'm after is a way to find the first specific day of a month in...
|
by: Venkat G |
last post by:
$ db2 describe table venkat
Data type Column
Column name schema Data type name Length Scale Nulls...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |
