Memorize data within an Access Database

I have created a Tracking System DB for WorkmenComp Patients unisg Access 2003.  I have several different headings like Physician, Employer & Adjuster.  Within these headings I have fields like: Name, Address, Phone, etc.  
Questions
1 - If i have a Patient that uses the same Adjuster as another Patient.  How can I pull up the Adjuster information automatically.  In other words, I don't want to fill in the Adjuster information if he already exist in the DB.  Please Advise.  Some instructions will be helpful.
Access-db.doc
Wizkid003Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

therealmongooseCommented:
you can create a combo box based on your adjuster table, using the adjuster primary key as the reference - that way you only need to use a drop down to associate the patient with the adjuster and therefore be able to show all of the adjuster details...

You need to create separte tables for Adjuster and patient, with a link field in the patient table for adjuster.

Hope this makes sense.
0
Wizkid003Author Commented:
What should I enter in the link field for the patient table?
0
Wizkid003Author Commented:
Do you have a procedure to create a combo box based on my adjuster table. I don't know how to do this.  Please advise.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jim P.Commented:
>> You need to create separate tables for Adjuster and patient, with a
>> link field in the patient table for adjuster.

This is dependent on a few things if you need a separate table.

If you have one Adjuster per patient and just care who the current one is then you would just add a field to your patient table for Adjuster. If you have multiple adjusters per patient or want to keep track of when a new one was selected then you would need a separate table.

The same thing applies for the doctor patient relationship. If it is an office and you only have 5 doctors to 500 patients then you don't really need a separate table. But if you are trying to track beyond primary care physician (PCP), or have a large amount of doctors, or want to track historical then you would want a separate table.

Another "what if" on the doctors is to have the PCP as a 1 to 1 relationship against the Patient table and then on the billing/visits table you would have an Attending Physician. Again this is if you don't want to track historically who is the PCP.

I'm throwing this out to consider how you want to do this. We can come up with a solution you can use, but it is dependent on multiple factors.
0
Wizkid003Author Commented:
I have uploaded the Adjuster information as an example, but i also have information which I am uploading to you now.  I say information because, I am only using one table Called WorkmenComp.  I do not have multiple tables, but if that it what I need to do to make this work then I have no problem with that.
My goal is to be able to retrieve previous entered information for future patients.  
In other words, if I am entering a new patient with an adjuster, physician, employer, case manager who is already in the system.  I want to be able to automatically pull up their information as oppose to typing it all over again.
Please let me know of the best way I can do this?
Thank you.
Doc1.doc
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
That's the nature of a relational database - the avoidance of re-entering data.

In your case, I'd presume you'd have a couple of tables:

tAdjuster
tPatient
tDoctor
tEmployeer

Each of those would hold the basic information about a person - name, address, phone, etc. You'd also need to relate them together as needed - for example, I'd assume you need to show what Employeer a Patient is related to, so you add a field to Patient to store the ID field from tEmployeer.

You'd then need to build a "Visits" table (or whatever you want to call it) to show when a Doctor saw a Patient. To do that, you'd probably need a table like this:

tVisits
----------------
lPatientID (foreign key field, stores ID value from tPatients)
lDoctorID (foreign key field, stores ID value from tDoctors)
dVisitDate (date of visit)
etc etc

Not sure how the Adjuster fits in, but hopefully this will give some insight into how to build it.
0
Jim P.Commented:
On the basic level then:

You need to create tables -- Physician, Adjuster, etc, that has all the columns like name, address, city ... billing number, etc. The big one for each table is to have an Index_Num autonumber column. (There is a better way, but for your purposes start small.)

Then in your patients table You will have columns like Physician_Num, Adjuster_Num etc.

When you create the Physician_Num column go down to the bottom of the table and change it to look something like what is in the attached image.

Repeat for other tables.

Then when you put it on a form it be a combo box and you can look up from the other table.

This is called normalization.
Lookup.jpg
0
Wizkid003Author Commented:
I follow your instructions and when I create a new form I get the following on the Patient Form attachment.  I also upload the table for you review.  In the Form when I click the down arrow on the combo box for the adjuster information for example I get all of the adjuster information diagonally in one combo box.  I would like to be able to enter an Adjuster name for example and it will pull up all his information in different fields, like, name, address, city etc. vertically down the page.  How can I accomplish this?
Patient-Form.doc
Patient-Table.doc
0
Jim P.Commented:
Your employer table should look something like below.

The Column Count and has to match the numbers of columns in the table. Or you have to build a query to select just what you need to see.

You can build a stand alone query that you use. Or on the Row Source box, off to the right when your cursor is in the field you will have three little dots. Click on that and it will take you to a query building window.
Emp_Num    Emp_Name              Emp_Street    Emp_City   .....
123        ABC Corp              123 Main      Springfield
125        Springfield Nuke Pwr  456 Hill St   Springfield
127        Ned's Auto Body       311 Apu St    Springfield

Open in new window

0
Wizkid003Author Commented:
Lets start from the beginning.  I am uploading a sample Table and a sample Form for your review.  My goal is to be able to for example: type in the Adjuster First name into the Form Field and it will give me all the information like address, city, state, zip, email, etc. about that Adjuster, keep in mind that the data for the Adjuster has already been enter once.  The same goes for employer, patient, etc.  What do I need to do to accomplish this.  Please advise.  
Sample-Form.doc
Sample-Table.doc
0
Jim P.Commented:
I built a basic Three table of the sort of thing you are trying.

If you save it and change the extension back to mdb you can see what I am trying to get at.
Info-Lookup.txt
0
Wizkid003Author Commented:
How would you add a new Physician or Adjuster in the Form?
0
Jim P.Commented:
>> How would you add a new Physician or Adjuster in the Form?

You would have to have other form(s) to add a new adjuster/ physician/ employer....

You can put a button on the original form to open a different form to add that <whatever> on the fly.
0
Wizkid003Author Commented:
I like the idea on putting a button on the original form.  Can you attach a form with an example?
For example suppose I have to enter a new adjuster with all his information like name, address, phone, etc. how would I do this on my existing form?
0
Jim P.Commented:
The "Command Button" on the toolbox toolbar is how you add it with the wizard.




Info-Lookup-1.TXT
0
Wizkid003Author Commented:
I am uploading the actual database I am working on.  This db is being created to keep track of WorkmensComp Patient. Please let me know which is the best way to configure this db for user friendly access.  It will be use to enter new data and retrieve existing data, print reports & print labels.
Some of the things I would like to accomplish are:
1 - Add recods on a form on the fly
2 - Retrieve existing data - ex: new patient with an existing Adjuster in db - Be able to pull-up existing Adjuster with just first and last name
3 - Be able to click in a filed and it pops up with all the information for that field

Thanks for all your help

WorkmenComp.txt
0
Wizkid003Author Commented:
New Scenerio - If you can assist with this.  I will be all set to go.
1 - Attach is a snapshot of my Form - This is the Form that I will be use in my office
2 - I have created only 1 table with all the information you see on the snapshot
3 - I customize the Form with combo box, check boxes, etc.
4 - I have entered some sample data into the Form as you can see
5 - Test my combo box and check box and they work just fine
To complete my project, I am left with one problem and that is:
6 - When I enter a new patient that uses an existing (Employer, Adjuster, Primary Physician, etc.), I would like to be able to just type in the (Employer, Adjuster, Primary Physician, etc.) name and pull up all their information rather than typing all their information again.  What is the easiest way to do this with my current design which is 1 Table and 1 Form.  If this is not possible.  Please provide a solution for me to follow.
Thank you.
WorkmensComp.doc
0
Jim P.Commented:
What we are discussing is normalization. A good description is here: http://en.wikipedia.org/wiki/Database_normalization

You can't do this with only one table.

The second database has the a simple example of what you need to do.
0
Wizkid003Author Commented:
I don't understand where is the second database located.
0
Jim P.Commented:
http://www.experts-exchange.com/Q_23126995.html#20824835

The database is this one with "Info-Lookup-1.TXT". Change the extension back to MDB.
0
Wizkid003Author Commented:
Ok, I want to do this the right way which is normalization.  Take a look at the attached tables and forms.
1 - So far I have created 7 Tables & 7 Form with 1 record in each Form
2 - What is the next step in normalization
3 - How do I manipulate date within the Form?
Forms.doc
Tables.doc
Forms-format.doc
0
Wizkid003Author Commented:
Not sure what happen with his question, but from what I understand I need to do something called normalization.  Attach is a word doc of what my form looks like.  I only have one tabe that contains all the information you see on the attach Form.  
Question:
1 - I would like my form to look exactly like the attachment I sent to you
2 - Be able to type in the name of the Adjuster for example and pull up all the Adjuster information, type in the name of the Employer and pull up all the Employer information, etc.
3 - I would like to accomplish this task for data integrity
4 - How can I make this happen

Please Advise
Thank you.
WorkmensCompScreenShot.doc
0
Jim P.Commented:
You need multiple tables.

You can do sub forms that key off the main form and table.
0
Wizkid003Author Commented:
If I stick with just 1 Table and 1 Form, is it possible to do combo boxes like the attach example.  In this way the user can just use the drop down arrow to select an existing data if needed.
 
I tried the como boxes and it works ok.  The only problem is when I enter a new record and choose existing data (ex: Adjuster_F_Name) from the drop down arrow and save it, the name shows up twice on the drop down menu.  I want to be able to access existing record without it being saved twice so that when I try to print a report or label and select the Adjuster information for example, I must only be able to see it just once and not as a duplicate record.
Combo-Box.doc
0
Jim P.Commented:
There is no good way to do this with one table.
0
Wizkid003Author Commented:
I previously uploaded 3 files to you called forms.doc, tables.doc, and forms-format.doc.  Could you please look the files over and advice if that is the way to go?
0
RyanProject Engineer, ElectricalCommented:
The form that you keep doing a screen shot of like in post 20863288 is going to be very hard for a beginner to understand.

What you had in post 20819436 is very much what you'll need to try to do first.

The way you post implies you're still not sure about how normalized tables work. I think this explanation would be very good in most database textbooks, but theres whole college classes dedicated to this topic, it can't really be expected to be fully taught here.

What you are trying to do in that huge form you have can be done, but what I'd recommend you do is make a seperate form for each of those 6 sections you have. This way, each form is based on 1 table (it'll be really easy to do with form wizard). Later, once you better understand Access and normalization, you can make a 7th form, and put each of those 6 forms into one, and use filters to display the relevent data based on the patient form's data. If its necessary for you to get this done, I'd seriously recommend getting personally help from someone knowledgeable in databases and/or Access. Normalized tables are abstract and hard to understand at first, but I would think someone in person can explain it all in 30min and you'll be able to do this.
0
Wizkid003Author Commented:
Thank you.
0
GRayLCommented:
Wizkid003:  I just read over the thread and and jimpen's suggestion at http:#a20834074 seems very appropriate.  Why not Google - database normal form - and have a good read of the first four hits.  
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Could you post your screenshots as Image files (jpg, png etc) instead of .doc? I don't open .doc files from websites, and lots of Experts won't either.
0
RyanProject Engineer, ElectricalCommented:
All that he posted were clean when scanned with AVG.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Right ... but the only way to know that is to download and attempt to open them. If there's a need for .doc files, then I say use them, but in most cases I find that the .doc files are nothing more than screenshots, and in order to view the .doc files I have to open Word, etc etc ... I look at and answer a LOT of questions on this forum daily, and it's much easier to just view an image file, pdf etc.
0
GRayLCommented:
Why not use http://www.ee-stuff.com/login.php  instead of trying to trick*(&^ the sytem?

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Exactly ...
0
GRayLCommented:
Glad someone knows what trick*(&^ is all about;-)
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm multilingual <g>
0
RyanProject Engineer, ElectricalCommented:
Is that a regular expression or C? Trick as many times as you want..Then something before getting the address of the pointer ^?
I feel dumb. This thread has been hijacked. hijacked looks like an arabic word, maybe I shouldn't use it in times of war.

I'm multilingual too, but my compiler doesn't speak it.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'd tell you, but then I'd have to kill you (and I don't even know where you live, although with a name like BullWinkle I'd assume you live in Frostbite Falls, Minnesota)
0
RyanProject Engineer, ElectricalCommented:
Haha, no. But I do like an hour from Frostburg College.

Bullwinkle is Mr Know-It-All, if you ever watched the show. So that makes me Mr Mr Know-it-all.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Yes, but he's a bit on the, uh, ... moronic side <g> as well ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.