[mso] Re: Access 2002 normalizing a database

  • From: "DiMarco, Suzanne " <SDiMarco@xxxxxxxxxxxxxxxxxx>
  • To: "'mso@xxxxxxxxxxxxx'" <mso@xxxxxxxxxxxxx>
  • Date: Thu, 4 Nov 2004 10:01:03 -0500

Sounds good.  Do you know where I find the wizard?  Also, I'm trying to
delete the primary key field (currently an autonumber field) in the table
but when I do I get a message that says it can't delete the primary key
because it's part of a relationship. I've gone into the Relationship Window
and I removed any relationship this table has with other tables but it still
won't let me delete the primary key.  Any thoughts??

-----Original Message-----
From: Glenda Wells [mailto:gwells@xxxxxxxxxxx] 
Sent: Thursday, November 04, 2004 9:55 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Access 2002 normalizing a database


I would definitely use the date field if it's unlikely the same patient
started using that equip twice on the same day or that two patients w/same
last/first started using that equip on the same day.

There is a wizard in access that helps w/using part of a field:  the
functions are Left, Right and MID.  The wizard steps you through it. The end
result using 3 of last and 3 of first and the date might look something like
this:

UID: Left([LastName],3) & Left([FirstName],3) & [mmddyyyy]



-----Original Message-----
From: DiMarco, Suzanne [mailto:SDiMarco@xxxxxxxxxxxxxxxxxx]
Sent: Thursday, November 04, 2004 09:28 AM
To: 'mso@xxxxxxxxxxxxx'
Subject: [mso] Re: Access 2002 normalizing a database

Glenda, I understand your logic here...just a few questions. I went back
into the transaction table to see if I can use an additional field as part
of the UID.  There's not many fields that are consistently filled in.
However, there's a date field that specifies when a patient starting using a
piece of equipment.  Can I use a date field as part of the primary key?

Also, how do I specify that I only want to use a portion of a field as part
of they UID, for example, only the first 3 letters?

-----Original Message-----
From: Glenda Wells [mailto:gwells@xxxxxxxxxxx]
Sent: Thursday, November 04, 2004 9:13 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Access 2002 normalizing a database


you have NO other info on the patient than last and first?

I'm not sure the autonumber is the way to go. if there was a mistake and the
same patient last/first was entered twice, they'd have two autonumber ID but
it could be the same patient. there should be something that truly uniquely
identifies the patient. in the past, I've used various combinations of
information, such as

first 3 of last, first 3 of first, date patient seen...if the chances of the
same patient coming in twice in the same day or two people with identical
first/last on the same day are fairly slim ex:glewel11042004

or

first 3 of last, first 3 of first, last 4 of ssn ex:glewel1234

or

first 3 of last, first 3 of first, last 4 of phone ex:glwel7036

deciding on a UID is the first important step, because this UID must be part
of both tables in order to create a relationship between patient and
transaction.

Next would be to create two queries based on the existing table. One query
called mkePatient, the other mkeTransaction  (mke in my world stands for
Make Table)  In query design, choose make table as the type of query from
the query menu.  The resulting table names might be (again, the naming
convention is what I use) mstPatient and mstTransaction (mst for Master) Add
the fields appropriate to each query , i.e. UID, Last and First for patient
to the grid.  Run the queries. The result will be two tables ready to work
with.

This has been a simplistic explanation and I've probably left out too much
detail but others will jump in and correct me and help you and we'll all get
a chance to learn something.

hope this helps. /glenda



-----Original Message-----
From: DiMarco, Suzanne [mailto:SDiMarco@xxxxxxxxxxxxxxxxxx]
Sent: Thursday, November 04, 2004 08:56 AM
To: 'mso@xxxxxxxxxxxxx'
Subject: [mso] Re: Access 2002 normalizing a database

Split the transaction table into two - one containing just patient info and
one containing the transaction info creating a one to many relationship. The
patient info only consists of first name, and last name.  Currently there's
no good primary key field. They have not been using social security number
or any field like that to identify a patient. I'm guessing I'll have to use
an autonumber field as the way to relate the two tables?  Thanks for any
help.

-----Original Message-----
From: Glenda Wells [mailto:gwells@xxxxxxxxxxx]
Sent: Thursday, November 04, 2004 8:49 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Access 2002 normalizing a database


this list can help suzanne. what exactly are you wanting to do?  /g

-----Original Message-----
From: DiMarco, Suzanne [mailto:SDiMarco@xxxxxxxxxxxxxxxxxx]
Sent: Thursday, November 04, 2004 08:47 AM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Access 2002 normalizing a database

I have a database with a table that consists of patient information and
rental transaction information. There's a lot of patient duplication since 1
patient can have many transactions.  I want to split the table into two.
From articles I've read so far I know I have to run a few queries to
accomplish this (I don't know code).  I need help with creating the queries.
Does anyone know of any articles or web sites that could help me with this.
Thank you very much. Suzanne DiMarco




*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to mso-request@xxxxxxxxxxxxx
with the word "unsubscribe" (without the
quotes) in the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also
allow you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation
with instructions.  Once you are a member of the files group, you can go
here to upload/download files: http://www.smartgroups.com/vault/msofiles
*************************************************************



  _____ =3D3D20

<< ella for Spam Control >> has removed 13216 Spam messages and set aside 0
Newsletters for me You can use it too - and it's FREE!
www.ellaforspam.com=3D3D09
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to mso-request@xxxxxxxxxxxxx
with the word "unsubscribe" (without the
quotes)
in the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also
allow you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation
with instructions.  Once you are a member of the files group, you can go
here to upload/download files: http://www.smartgroups.com/vault/msofiles
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to mso-request@xxxxxxxxxxxxx
with the word "unsubscribe" (without the
quotes) in the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also
allow you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation
with instructions.  Once you are a member of the files group, you can go
here to upload/download files: http://www.smartgroups.com/vault/msofiles
*************************************************************



  _____ =3D20

<< ella for Spam Control >> has removed 13216 Spam messages and set aside 0
Newsletters for me You can use it too - and it's FREE!
www.ellaforspam.com=3D09
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to mso-request@xxxxxxxxxxxxx
with the word "unsubscribe" (without the
quotes)
in the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also
allow you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation
with instructions.  Once you are a member of the files group, you can go
here to upload/download files: http://www.smartgroups.com/vault/msofiles
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to mso-request@xxxxxxxxxxxxx
with the word "unsubscribe" (without the
quotes) in the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also
allow you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation
with instructions.  Once you are a member of the files group, you can go
here to upload/download files: http://www.smartgroups.com/vault/msofiles
*************************************************************



  _____ =20

<< ella for Spam Control >> has removed 13216 Spam messages and set aside 0
Newsletters for me You can use it too - and it's FREE!
www.ellaforspam.com=09
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes)
in the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also
allow you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation
with instructions.  Once you are a member of the files group, you can go
here to upload/download files: http://www.smartgroups.com/vault/msofiles
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in 
the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also allow 
you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a 
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with 
instructions.  Once you are a member of the files group, you can go here to 
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

Other related posts: