Adding result box next to drop down menu

[jzwp11]

You show one-to-one relationships between your 3 tables. I'm not sure that is what you want. You might have to explain what type of information you are dealing with and how you are trying to use it.

From what I understand from your posts so far, you have doctors that do many surgeries. That in itself describes a one-to-many relationship (one doctor to many surgeries) not a one-to-one relationship. A surgery typically involves 1 patient. A patient, however, can have many surgeries (one-to-many relationship). Also, several patients might undergo the same surgery. (one-to-many relationship).

Can many doctors work on the same surgery? I assume that is a possibility

I would probably start out with a table to hold the basic information about people (doctors and patients). You can choose to have separate tables for doctors and patients, but the rule is that like data should be in the same table.

tblPeople
-pkPeopleID primary key, autonumber
-PersonIDNumber
-txtFName
-txtLName
-Role (doctor or patient)

A table to hold all possible surgeries

tblSurgery
-pkSurgeryID primary key, autonumber
-txtSurgeryName

Now since at least 2 people are involved in a surgery (i.e. the patient and the doctor) that is a one-to-many relationship. Further, the same surgery might be performed on more than one person, so that describes another one-to-many relationship. When you have 2 one-to-many relationships between the same two entities (people and surgeries) you have a many-to-many relationship which is handled with a junction table:

tblPeopleSurgery
-pkPeopleSurgeryID primary key, autonumber
-fkSurgeryID foreign key to tblSurgery
-fkPeopleID foreign key to tblPeople
-dteSurgery (date of the surgery)


Now if you want to capture notes about a surgery that a doctor makes, then that is another one-to-many relationship

tblPeopleSurgeryNotes
-pkPeopleSurgNoteID primary key, autonumber


-fkPeopleSurgeryID foreign key to tblPeopleSurgery
-dteNote (date of the note)
-txtNote (the note itself)

Just some general recommendations:
1. Do not include spaces or special characters in your table or field names
2. Do not use reserved words or symbols in your table or field names (see list here)
3. Do not use lookup fields in your tables; they are best left for forms. Here is a site that explains the problems table level lookups can cause.

To properly structure databases, it is best to know the rules of normalization. This site describes normalization from a technical perspective.

This site provides some tutorials for someone just starting out with Access and provides examples of how to apply the rules of normalization.

[ashu.doc]

You show one-to-one relationships between your 3 tables. I'm not sure that is what you want. You might have to explain what type of information you are dealing with and how you are trying to use it.

From what I understand from your posts so far, you have doctors that do many surgeries. That in itself describes a one-to-many relationship (one doctor to many surgeries) not a one-to-one relationship. A surgery typically involves 1 patient. A patient, however, can have many surgeries (one-to-many relationship). Also, several patients might undergo the same surgery. (one-to-many relationship).

Can many doctors work on the same surgery? I assume that is a possibility

I would probably start out with a table to hold the basic information about people (doctors and patients). You can choose to have separate tables for doctors and patients, but the rule is that like data should be in the same table.

tblPeople
-pkPeopleID primary key, autonumber
-PersonIDNumber
-txtFName
-txtLName
-Role (doctor or patient)

A table to hold all possible surgeries

tblSurgery
-pkSurgeryID primary key, autonumber
-txtSurgeryName

Now since at least 2 people are involved in a surgery (i.e. the patient and the doctor) that is a one-to-many relationship. Further, the same surgery might be performed on more than one person, so that describes another one-to-many relationship. When you have 2 one-to-many relationships between the same two entities (people and surgeries) you have a many-to-many relationship which is handled with a junction table:

tblPeopleSurgery
-pkPeopleSurgeryID primary key, autonumber
-fkSurgeryID foreign key to tblSurgery
-fkPeopleID foreign key to tblPeople
-dteSurgery (date of the surgery)


Now if you want to capture notes about a surgery that a doctor makes, then that is another one-to-many relationship

tblPeopleSurgeryNotes
-pkPeopleSurgNoteID primary key, autonumber
-fkPeopleSurgeryID foreign key to tblPeopleSurgery
-dteNote (date of the note)
-txtNote (the note itself)

Just some general recommendations:
1. Do not include spaces or special characters in your table or field names
2. Do not use reserved words or symbols in your table or field names (see list here)
3. Do not use lookup fields in your tables; they are best left for forms. Here is a site that explains the problems table level lookups can cause.

To properly structure databases, it is best to know the rules of normalization. This site describes normalization from a technical perspective.

This site provides some tutorials for someone just starting out with Access and provides examples of how to apply the rules of normalization.

You are the coolest teacher, Great stuff, I am not sure how I am going to do all this but I will start changing the tables now and I will give you a shout if I have a doubt
.
many thanks

[ashu.doc]

Hi
I dont know whether I have done right or not but it seems that it is working for me now, I have taken your advise and added PatientID in patient details table as primary key, then I have added PatientID as FK in two different tables viz. operation notes and surgery name tables, also given one primary key to each of these table. Then I have related PatientID of patient details table with primary keys of the other two tables as one to one relation. Finally I used lookup wizard for PatientID in these two tables to lookup PatientID from Patient details table.

So now when I am entering the data in the forms corresponding to these tables the PatientID is populating itself as same number. I know its different from what you said, its because I have kept doctor details separately, because my intention is that one user who will be a doctor to use this logbook for himself. So ultimately the user will get access by a username and password to start entering the data for himself.

Then issue was that one patient can have many surgeries, true, but I have allowed in the table that hospital number (hospital ID of patient ) can be duplicated, so if patient A has one surgery on 21 aug, and other surgery on 29 sep, it can be re added with adding same hospital ID. Although you can say patientID will be different this time, but I don't know whether it will have any major effect on my database?

The other issue was many patients can have same surgeries which I think is similiarly solved with allowing duplication of name of surgery for different patient.

I am sending you the relationship --



Tell me what you think.
Thanks
Ash

[jzwp11]

Then I have related PatientID of patient details table with primary keys of the other two tables

You have to relate the patientID of the patient table to the foreign key for patientID in the related tables NOT the primary keys of the related tables.

[ashu.doc]

You have to relate the patientID of the patient table to the foreign key for patientID in the related tables NOT the primary keys of the related tables.

I have tried what you said, but whether I relate pk with pk or pk with fk or do not relate them at all, the same problem is happening that after entering one set of data for a patient ID, the patientID is populating in patient detail tbl but in other two tbl where patientID is a fk (and I have used lookup option for it to populate from patient detail), its not populating itself, instead it is showing a combo drop down box to select the patient ID, which is not right!!!

[jzwp11]

Did you set up the fkPatientID field in the related tables as a combo/list box?

Can you zip and post a copy of your database with some sample data (please remove or alter any sensitive information)?

[ashu.doc]

Did you set up the fkPatientID field in the related tables as a combo/list box?

Can you zip and post a copy of your database with some sample data (please remove or alter any sensitive information)?

there is nothing to hide my friend, you have been very kind already, im sending you a copy
many thanks

[ashu.doc]

obviously the login window is just a show off, you can close it start seeing the tables etc. The password for changing macros and codes is 1234

[ashu.doc]

tell me when u have downloaded the file, i will take it off the forum.

thanks

[jzwp11]

I do not see a file attached to any of your posts.

[ashu.doc]

I do not see a file attached to any of your posts.

Ok, can you see it now?

[ashu.doc]

Does this relationship looks any better?



but still the same problem is there, when I am entering the data in different forms, the patient ID in all the forms is not auto populating

[jzwp11]

I was able to download your database. Which form is giving you problems?

The relationship you posted with the surgeon tied to the patient, does not sound correct since a surgeon should be tied to the combination of patient and surgery. I assume that a patient can have many surgeries each with a different surgeon. You cannot do that with the structure you proposed

[ashu.doc]

I was able to download your database. Which form is giving you problems?

The relationship you posted with the surgeon tied to the patient, does not sound correct since a surgeon should be tied to the combination of patient and surgery. I assume that a patient can have many surgeries each with a different surgeon. You cannot do that with the structure you proposed

All I want is that when I enter a set of data in the F_patient detail, F_opnotes and complications, and F_general surgery, the patient ID in all the tables related to the forms get auto populated with same number, so that, when a surgeon bring out a report of operations he has done which will include fields - patientID, Hospital number, DOB of patient, operation done, etc from different tables, give correct set for each patient in report.

I know it sounds very simple, but dont know why it is getting so complicated to get it.

If you see the relationship, ignore the surgeon part, the patientID is related one to many with opnotes and complication, and one to many surgeryID, which means one patient can have many surgery and obviously which will require many opnotes. Then why patientID in all the related tables is not auto populating with same number.

thanks
Ash

[ashu.doc]

Also you recommended not to use lookup options in table fields, so I have taken that off, it was not helping me anyways as it was shwoing as combobox and everytime I have to go in to table to select a value for patient ID after I enter the data which was useless.