Repeating text in field

[Guest]

I have seen many posts with this subject and many different answers. The
answers always respond with Default Value and new records. I inherited a db
that has a main form and subform. The records already exist in this db.
There will not be any new records. Only be using existing records. Only
entries are date and comments. There is a comments text box on the main
form. This same comments box is on the subform. When I enter text on the
main form. The first record gets a copy of the comments. But if there are
more records for this patient with different doctors the comments does not
show. I would like to know if there is some code that I can apply to that if
there are more records to copy the comments text to rest of the records.
Something like how you would "control '" on a spreadsheet. Thank you.

 

[Guest]

Not that it really helps that much but it sounds like a dodgy database to
me. How many records do you have.

 

[Guest]

What you are asking is a serious violation of database normalizatoin rules.
You should not be duplicating data, particularly in a memo field. The
databae design needs serious rework.
If you can describe the database schema and the business workflow, perhaps
we can help.

 

[Guest]

Okay - Thank you. I guess there is no easy way around this. I will attempt
to denormalize this db. Any suggestions?

 

[Guest]

It may be painful now, but you will be happier in the long run.
If you can describe the business work flow and the data you have, then I
will be glad to help out.

 

[Guest]

Thank you very much. Data is a large table of over 4000 records in one
table. This table contains patients names, dob, ss#,ptID,ptcomments, doctor
name, address,phone, doctorID, doctorfx, doctorcity, doctorst,RecordID,
Doctorcomments. This is the actual table imported to Access. In Access
fields were added to this table to track pt's visits Many date fields:
InitialVisit, FollowUp Visit, NoticeSent.NoticeRcvd.

If I am understanding "Normalization" correctly I will have to breakup the
table into at least 3 tables. PT, Doctor, Visits.

tblPT tblDOC tblVisits
ptLNM docLNM Initial(date)
ptFNM docFNM Folloup(date)
ptDOB docID NoticeSent(date)
ptSS# etc. NoticeRcvd(date)
ptID


How do I link these tables? Hmmm RecordID? Does that need a table too?

I appreciate any help in guiding me. Thank you

 

[Guest]

You are pretty close. Those are the tables you need. You also may need one
more. It would be a Doctor/Patient table. That is so you can tell which
patients a doctor normally has and what doctor a patient normally sees. All
it needs is the primarly key of the doctor table and the primary key of the
patient talbe.

It would not be correct to relate the doctor and patient tables directly
because one doctor can see many patients and one patient may see one to many
doctores.

The visit table needs a field to carry the primary key fields from the
doctor table and patient tables as well so you know for this visit which
doctor saw which patient. You probably need a field to describe the specific
issue the visit is for, so that if a patient has to come back for a follow up
, the records for the issue can be grouped easily. The visit record also
needs the memo field for doctor's notes. Now, if you need to see all the
notes for a patient for an issue, you can use a query on the vistit table
filterd on the patient and issue. You would join the doctor and patient
tables to get the names of each.

As to your question regarding "linking" the tables, we have sort of covered
that. (BTW the term is relating. Linking referes to attaching external
tables). Each table should have a unique identifier like an SSN. The unique
identifier has to be something that will not change and will always be
unique. That is why many database designers will use an Autonumber field as
the primary key. It does not change and will be unique. Then to relate the
tables, you carry the value of the primary key of the record in the parent
table in the record of the child table. For example, in this case, the Visit
table is the parent and the Doctor and Patient tables are both child tables.
So that is why you need to carry the primary key values - To point to the
correct Doctor and Patient. The fields in the visit table that point the the
other tables is know as a foreign key.
Hope this helps.

 

[Guest]

Thank you for your response. Okay please let me summarize the steps for this
large table:
1) look at data and breakup accordingly
2) Clean data (This large table had docs listed many times )
3) assign PK and FK and indexes.

Question: Will adding the unique identifier (autonumber) help in the tblDOC
as far as doc having more than one record due to same name, same ID, but
different addresses? Therefore, my Doctor/Patient table would hold
PK(autonumber) and DOCID and PTID only?

What does BTW mean?

Thank you again. I need to get this db normalized and setup in a week. I
am hopeful I can do it with your help.

 

[Guest]

You are certainly on the right track.
Interestingly, you have found the need for another table!
If a doctor can have more than one address, then you need a doctor address
table.
The only other thing I see in the order of events, is you may want to do a
first pass clean before you break it up. Not in detail, but more to look for
other anomolies that may affect the normalization process.

 

[Guest]

I am stuck with the Doctor Table. Docs can be listed as Primary, specialist
or backup etc. Pt may see all of them or just one. Would this mean another
table?

Is there something that would help in determining how to break up the table?
Some kind of formula? What fields to choose to make a table? I know they
have to relate but to what extent? Just when I though I had it I find some
fields questionable

 

[Guest]

Which tables you define and how they are related is more a business process
issue than a pure technical issue. One way to do it is to talk through it.
For example.

A patient may see one or more doctors.
A doctor may tend to one or more patients.
(That makes patient to doctor many to many)
(Relational databases do not support many to many relationships directly)
That requires a junction table to resolve a many to many.
A patient will visit one doctor per visit.
A doctor will tend to one patient per visit.
So, we need a visit table.

A doctor may practice in one or more locations.
A location may have one or more doctors.
Possibly a location table.

A doctor may have one or more phone numbers.
Possibly a phone number table with a type (home, emergency, cell, fax, page,
location 1, etc)

Patients may have one or more phone numbers. (can we use the same phone table)

A doctor may be a primary, a specialist, or a back up. Can he be all 3?
How do we determine which he is.

See what I mean. You have to model the entire business process. For now,
if you have Visio or some other flowcharting software, draw a diagram. If
you don't, turn off the computer, get a pencil and a piece of paper, and
write down the rules and draw a diagram.
Think through each entity. For example, a patient is an entity. What do we
need to know about a patient?
Last Name, First Name, Home address, mailing address, responsible party,
insurance infor (Okay, now do we need a table of insurers?), Phone numbers,
is the patient the insured or a dependant, etc.

This exercise is the most important part of building a database and an
application. I can tell when I see an application where the developer just
sat down and started coding. You can see the patches and work around to
incorporate something they should have thought about before they started
writing code.