Hi Everyone,

I have read several tutorials on linking tables to store data, however I cant seem to get this right. I need a little help with my table design and structure. What I am trying to accomplish is a database that captures the following:

-Name
-Employee#
-Group # (1-25 One person can be in more than 1 group)
-Referred By (Only one name for each Group the person is in)
-Priority # (Can be a 1 or a 2 for each group # the person is in)
-Outcome of group (Just a small text explination of the outcome)
-Date Scheduled (Can be mutiple dates scheduled for each group)


So basically what I want to do is be able to retrieve a list of what Groups an emplyee is in, Who reffered them to that group, the Priority # of that person in the group and what dates they are scheduled for each group along with the outcome of each group they have attended. I know I need to join multiple tables but I am a bit stumped on how to go about doing this. Any help with my database and table design would be much appriciated. Thanks.

I think maybe you need to provide a little more clarity on the real world situation that you're trying to model.

I could interpret your post in a number of ways.

1. You have various 'groups' of 'employees' (fixed array of employees), that 'meet' at certain times
OR
2. You have 'groups' that 'meet' at certain times, and some 'employees' attend these groups for one or more meetings (variable array of employees over time).

AND

1. Outcomes are dependent soley on the group and are reached/fixed at the conclusion of the Group's last meeting
or
2. Outcomes are dependent soley on the group and are reached at each meeting of the Group
or
3. Outcomes are a function of both employee and group and are reached/fixed at the conclusion of the group's last meeting
or
4. Outcomes are a function of both employee and group and are reached at the end of the employees last attendance of the group
or
5.Outcomes are a function of both employee and group and are reached at each meeting of the group attended by the employee


The tables you need and the relationships between your tables will depend on which of the above scenarios matched the real world situation.

At a minimum I see a need for a Group table, an Employee table, a GroupEmployee table, and a GroupMeeting table, and then possibly also an EmployeeGroupMeetingAttendance table and/or GroupEmployeeOutcome Table depending on how your real world situation shakes out.

Thanks for the quick reply, Let me elaborate a bit more.
Here is a more defined list. I need to store multiple dates that an emplyee is scheduled for the medical clinic, Each date that the emplyee went to the clinic i need to put down a different outcome. So lets say on 12/7/07 I went to my clinci appointment at clinic #1, what was the outcome?

-Emplyee Name
-Employee#
-Clinic # (1-25 One person can be in more than 1 class)
-Reffering Doctor
-Priority # (Can be a 1 or a 2 for each class # the person is scheduled for)
-Outcome of clinic (Just a small text explination of the outcome of the clinic)
-Date Appintment Scheduled for clinic # (I need to list the dates the emplyee is scheduled for the clinic this can be more than one date)

I have attached a spreadsheet on what information I need in a record.

And now we also need a doctors table :)

So, when you say a clinic 'has a scheduled appointment', this is a fixed weekday(?) and time slot which employees from your company can attend? (and potentially may attend this 'appointment' on more than one occassion)

If true then it seems that for the first of the two scenarios above we're looking at the second option.

Can you elaborate more on 'outcomes', please.

Well, your spreadsheet doesn't really help me much because I don't know anything about the real world constraints (other than what you have told me) behind the listed data which dictate what your data model should look like.

I think I understand outcomes now but you didn't answer my questions about whether the appointments offered by the clinics are for fixed days/times or are variable, and whether they might be attended by more than one employee (these could be group therapy sessions as far as I know), or are they private consultations? Until then, I can't really move forward.

The other issue is this 'priority' field. What is this and how does it relate to employees/clinics/appointments?

Hi Craig,
The appointments can be for any date/time and each person is seperate. One person at a time, private consultations. They can be scheduled at the same time and dates for the same clinic it realy does not make a difference. I was just goint to make a form for the user to enter a data and time for the clinic appointment. The priority # is just for the people looking at the data to use for order of bringing emplyees in. It does not directly effect the dates or records. They will be entering this information into the database and just need the priority # for thier use.

Thanks for clarifying that. I'm not trying to be nosey, just trying to understand your real world data model. Nearly there!

The priority # is just for the people looking at the data to use for order it does nto directly effect the dates or records

Which still leaves the question as to what entity is it that they are wanting to order? If they are wanting to use the field to order records then that's fine, but we still need to know which table to place that field in that will allow them to use it to accomplish what they want. :)

Thanks!
I think the priority # field needs to be in the same table as what clinic # they are going to. Each clinic# apointment needs a priority # as well.

Ok, I think you might need to think hard about how you plan to use and populate this priority field of yours, but see the attached db for a data model you might consider. I've included a query to show you the equivalent of your spreadsheet report, sorted using a priority field in the EmployeeReferalAppointments table.

But think about how you plan to populate the priority field. In your earlier post you seemed to indicate that the values would be either 1 or 2. This system would work because you are either high or low priority.

But in your spreadsheet you have 6 records and priority values between 1 and 6. This means that, somehow, for each appointment, you need to be able to set a priority value according to its desired place in the final report. How will you know what that number should be at the time of entering the appointment data? What if the next appointment that you enter is actually a higher priority record? Now you have to go back and re-set the value of the previous record. Consider how much of a datamanagement nightmare you're facing here.

Are you sure that you can't use some other factor to prioritize your appointment records instead? Like employees could be high or low priority, or clinics could be high or low priority, and then you sort your appointment records according to the priority of the clinic (i.e., the ClinicPriority field) or the Employee (EmployeePriority field). Or maybe some combination of the two fields.

Hope this helps.

Thank you so much for your time and help. This gives me a very nice base to work on and I think Im on the right track and can use this to better understand the relationships between tables. I will definantly take your suggestion on the Priority # and will let you know how it works out. Thanks Again!

Glad to help. Feel free to post back if you have more questions. :)

Hi,
I have one more question. How would I go about making a form that would allow the user to search for a name using a combobox ( I got this setup already) and then enter new appointments and outcomes on perhaps a subform?? Im not sure how to do this properly as each time i do I end up with duplicate name entrys each time I add a new record. I have attached an example of what I am working on with dummy information entered. Thanks.

Hi again. Well, have a look at this version and see if it is similar to what you were needing. The subform shows all previous appointments (most recent first). To add a new one for a patient you can simply add it to the first empty row at the bottom of the list.

Note the parent child relationship of the two forms is dependent on the common field PatientID. If you look at the design view of the subform you will see that the PatientID field is present but hidden. I haven't recreated some of your command buttons for adding a new clinic etc. They weren't functional anyway. I also stripped out a lot of the unused/unnecessary queries to keep the example clean for you to track how things work.

The combobox I used to move from one patient to another was generated using the ComboBox wizard that is built into access. You might want to play around with the wizard to learn the different functionality already present.

I note that you have removed the referrals table from my original example. This now means that you will have to enter the referral information for each and every appointment associated with the one referral. If that floats your boat, then so be it; but it will increase your data entry burden higher than is strictly necessary.

Thanks Again, This has been a wonderful learning experience for me. I was a bit confused with the relationships and further confused myself while trying to make too many querys. I see now that I need the patient ID in the subform and it needs to be hidden. Thanks Alot!

Just for kicks and giggles, here's another version where I've retained my original table structure so you can see what I mean. It does require more 'work up front' but it will hopefully save you time with the data entry down the line especially if multiple appointments share the same referral. :)

Thanks, I will definantly take a look.

I love your form design, mine was a bit tacky.

I basically just re-used your form! (Except removing the graphic background on the main form because otherwise the two images did not line up).

Hi,
I learned how most of your coding and relationships work. I was actualy able to make a new form using the same concepts on my own and it works great, Learned alot from your examples!

However I tryed to incorperate a version of the "Referrals" example you posed last. Rather than just editing your version I wanted to be able to make my own so I can grasp all of the concepts from your example. I think it will work out much better this way. I must be missing some code or not formatting something right. If you have some time could you take a look at my example and see if you can find out where im missing the code or where I have made an error. Everything works except I cant seem to get the referrals to show up per "patient" instead each patient I select shows the entire list of all "referrals" rather than just the referrals for that patient! I have also added in a new table. Thanks Again!

Anyone else is welcomed to give me suggestions/help as well :)

Hi again.

The problem you have is that you have not used a parameter in the sql of the combo box to limit the results to the patient. You need to add the PatientID field into the sql of your combo, and place a criteria in that PatientID field. Your Combo20 control on the mainform contains the current patient ID so your criteria should be

[Forms]![fmPatientSearch]![Combo20]

Hope it helps :) And I'm glad you're doing the rebuild and forcing yourself to learn the whys and wherefores. :)

Great! I didnt know you could edit sql in a combo box like a query. This help alot! Thanks Again.

Glad to help. And don't forget to requery the combo box when you've changed to a new person (or else your combo will still show results for the previous person). I use the on_enter event of the combo box to do this.

Cheers!