Memorize data within an Access Database

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.

What should I enter in the link field for the patient table?

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.

>> 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.

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

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.

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

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

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

Select allOpen in new window

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

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

How would you add a new Physician or Adjuster in the Form?

>> 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.

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?

The "Command Button" on the toolbox toolbar is how you add it with the wizard.




Info-Lookup-1.TXT

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

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

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.

I don't understand where is the second database located.

https://www.experts-exchange.com/questions/23126995/Memorize-data-within-an-Access-Database.html#20824835

The database is this one with "Info-Lookup-1.TXT". Change the extension back to MDB.

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

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

You need multiple tables.

You can do sub forms that key off the main form and table.

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

There is no good way to do this with one table.

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?

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.

Thank you.

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.  

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.

All that he posted were clean when scanned with AVG.

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.

Why not use http://www.ee-stuff.com/login.php  instead of trying to trick*(&^ the sytem?

Exactly ...

Glad someone knows what trick*(&^ is all about;-)

I'm multilingual <g>

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.

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)

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.