Hector said:
Hi, I hope you can help.
Part of my database is made up of two tables, Doctors and Surgeries. Since
any one doctor can work from one or more surgeries and any one surgery can
have one or more doctors I have set up a joining table with the primary
key
of the Doctors and Surgeries tables.
In use, all is well. I have two combo boxes one showing Surgeries and the
other Doctors, based on the selection of the Surgeries combo.
My problem is getting a form or forms to maintain the data. Do I really
need
3, one for each table?
I've managed to get myself more than a bit confused, so any help or
pointers
would be very much appreciated.
First this is a great question, and it's also a classic question in terms of
tables and relationships.
I never did like the terminology joining table or junction table. In all of
these questions and cases my big "secret" I've learned over the years is
always to start at the top most form of your design.
So, we have a doctors table. I would suggest that you create nice form that
allows you to edit and maintain the information about each of the doctors
such as phone number, name etc.
Ok, so we have our 1st table called tblDoctors
The next thing we need to do is we need to keep track of the surgeries that
each doctor does. So that doctor over time will do "many" surgeries and we
want to keep track of that. So we would create a table called
tblListOfDoctorSurgeries
In this table tblListOfDoctorSurgies we will have
id (pk) autonumber ID. This field likely is not needed, but as a rule
ALL OF our tables should have a PK id field
doctor_id This is the field that relates back to tblDoctors
Any time you have one thing (like a doctor), and then that one thing's going
to have many things, that many side will usually be designed and created as
a sub form in access. This is how we model a classic one to many
relationship. In fact as you'll understand as I talk more, there really
isn't any other kind of relationship that you'll ever desing in access. This
is why I'm telling you that I don't like the idea of a junction, or talking
about a join table. We never really have to think of it this problem in
terms of some join table. We simply have a table that is a list of the
doctor's surgeries.
You also mentioned that you need a table for each surgery. So you would
create a new table tblSurgeries that has all kinds of fields like the date,
perhaps the operating room location etc.
You then simply build a nice form that lets you edit this surgery
information.
So we have:
tblSurgery
id (pk) autonumber id
..... and a whole bunch of fields that defines all kinds of things about this
surgery.
Now let's go back to our table called tblListOfDoctorSurgies. we also need a
field in this table to list what the actual surgery was being performed, so,
lets add it
In this table tblListOfDoctorSurgies we will now have
id (pk) autonumber ID. This field likely is not needed, but as a rule
ALL OF our tables should have a PK id field
doctor_id This is the field that relates back to tblDoctors
surgery_id This is the field that is the id from table surgery.
Note that doctor_id, and surgery_id are simply plain Jane standard long
number fields in access, and there's nothing special about these fields.
They are simply regular fields in a table that YOU MUST enter numbers into.
So, ok, at this point in time, the process to enter this information would
be:
First is to open up your surgeries form, and enter all the information of
the particular surgery such as date, time, room# etc. And, you want to with
your pen and paper notepad write down the "id" (this is the autonumber PK id
from this table). You would then close this form.
The Next step would be to open up the doctors form and scroll to or find the
particular doctor that you want to add to the above surgery. Again, you
would write down the "id" of this doctor. (and if you just adding a new
doctor, then again bill simply write down the id of this doctor)
now the last thing we need to do is we simply one to put in our list of
doctors surgeries, we now must simply enter the doctor_id and then enter
what surgery they just worked on.
So, open up the form based on tblListOfDoctorSurgies and enter in the two
values:
doctor_id and the surgery_id
At this point in time you now would have a "list" of what doctors have
performed what surgery!
The above is how relations work in access, and it seems kind lame once this
explained in the above simple way. Again YOU ACTUALLY have to setup these
two fields - again: MS access will not set up these two fields for you!!!
Of course the next thing that comes up as you're likely to ask and say,
golly, it sure would be nice to see a "list" of surgeries that a particular
doctor has worked on. furthermore you're also going to say man who won a
painful process it is above to go through all these forms and grab a little
ID's to have to mark and down on paper and then finally entrant into a list
of doctors surgeries, that's a lot of work and it's difficult and worse it
is error prone because you might enter the wrong idea into one of those two
columns.
So at this point in time let's start building and designing a little bit
more of a user interface to make this process easier.
As I mentioed in just about all cases when you have a "many" of something,
then you simply use a sub form.
So what you're going do in this case is bring up that doctors form in design
mode, and use the the wizards to build you a sub form based on the "list of
doctors surgeries". not only is is easy, but also kinda makes sense when you
think about it also. you want insert a continuous form, because that can
only things like in a spreadsheet, a new build the CNN's listing of all the
surgeries they've worked on. And, if you use the wizard then access will
actually the filter and only display that list that only belongs to the
particular doctor. This is known as the link master and child fields setting
in ms-access. The beauty of placing the sub form inside of this doctors form
is that for each new record you add to the "tbllistOfDoctorsurgeries" table,
the doctor_id field will be set for you. So that sub form you're going
insert as a sub form will look like and be based on the two fields:
doctor_id surrgery_id
You'll notice that in that sub form when you go to add a new record the
doctor ID will be populated correctly for you. At this point you go back
your pen and paper and look at which "id" you marked down from the surgery
table and manually "enter" this surgery "id" value into the surgery_id
control in this sub form.
However since is it a pain to have to go all the way back out and look at
the surgery form to find what that "id" number is and then write it down, it
far more easy to base the surgery_id above on an actual combo box. So,
delete the surgery_id text box, and hten use the wizard in desing mode in
that sub-form. Add a combo box based on tblsurgeries. When using the wizard
the 1st collum MUST BE the autonumber pk "id" from table surgeries, and then
add additional columns in to the combo box such as date, location room# etc
that allows you to identify what surgery you want to select. I cannot stree
MORE that this combo box is ONLY for convenience sake so you don't have to
go all the way out and write down the surgery ID on a piece of paper and
enter this manually. I could go so far as to say that this is not really a
relationship field in as much as it's just simply a field in which we enter
the "id" of the surgery table here. Using a combo simply makes this process
more convenient, but you could continue to enter in that surgery ID by hand
if you did not want to use a combo box.
If you see the reasoning process above, I'm trying to impress the concept
that we simply start at the top most thing we are dealing with.
So, we had doctors. (tblDoctores)
then into those doctors had a list of surgeries so then we had
tblDoctores---->tblListOfDoctorsSurgeries
And then in the list of doctors surgeries we also put in the particular
surgery
tblDoctores---->tblListOfDoctorsSurgeries------->tblsurgeries
See how easy this kind of thinking flows and we don't really have to talk
about some weird junction table.
The next question you might ask as you might say look I would rather bring
up the surgery, and then after the surgery information is completed I then
want to enter a list of doctors that attended this surgery. Again we just
think down from the top most form and always work our way down through the
list.
So we have a form based on tblsurgeries
and we want to know which doctors are attached to that surgery. Well, we
just happen to have a table called:
tblListOfDoctorsSurgeries
And that lists our doctors for what surgery!
So,
tblsurgeries----->tblListOfDoctorsSurgeries
And, our list tells us which doctors so we have
tblsurgeries----->tblListOfDoctorsSurgeries------>tblDoctors
What this means is will simply add a sub form to our form of surgeries, and
that sub-form will be based on
tblListOfDoctorsSurgeries
It will look like:
surgery_id doctor_id
As mentioned when you set up a sub form, access can set the surgery_ID for
you automatically, but you'll have to manually enter the doctor ID. So in
open up the doctor forms scroll to the doctor find the correct one, write
down the "id" and then go back into the sub form and enter in the doctor_id.
However, once again we are lazy, instead of forcing you to type in the
doctor ID, we again delete that text box and use the wizard to place a combo
box into that sub-form. That combo box will be based on tblDoctors. Again
the first collum of the combo must be the ID from tbldoctores, and then
throw in any additional fields you want from table doctors into the combo
box for ease of view and selecting which doctor you want. Again I can't
stress that the only reason why we using this combo box is to save you from
manually typing in that doctor_id number. However, at the end of the day it
is a simple number we are placing into this column that's important here. I
should also of course point out that the sub form will not be the same some
form as we used for table doctors, but is going to be a new sub form
(however both of these sub forms will actually be based on the same table
called tbllistOfDoctoressurgeries).
The end result of the above is that now have a form in which we can find and
search and bring up a doctor, and we'll see in their sub form all of the
surgeries they have performed, and we can furthermore add more surgeries to
that doctors if we so choose to at that point.
And we can also do the reverse, which would likely more often be the case.
Thus we simply enter a new surgery and all its informaton. And, in the sub
form we would then enter all of the doctors that worked on that surgery.
So while you could argue there is a junction table here, I just like to
think that we have a table that lists out a bunch of surgeries that a doctor
worked on, and it becomes a sub form of the doctors form.
It turns out that we can also use that same table for the sub form of the
surgeries form.
I too dislike the terms 'join(ing)' and 'junction.'