Help with Many to Many

[thaines143]

I should start by saying that I get by via the skin of my teeth when working with Access - that being said, here is my problem:

tblIntakeQuestions - table represents questions asked of patients when speaking for the first time
tblIntakeTasks - list of tasks that will result from the completion of the intake questions and the natural workflow of the office
tblIntakeJunction (obvious) - each Intake will result in many tasks, each task can be associated with many intakes


tblTaskStatus - linked to tblIntakeTasks via lookup - each task can only have one status

Goal - upon completion of the initial IntakeQuestions form, auto-populate the tblIntakeJunction with that IntakeID and force the TaskID to 1 ("Review Intake with Doctor")

This will be a continuous process - Once the Review Intake with Doctor status changes, dependant upon the status (i.e. Approved for Appointment, Referred, etc), new tasks now need to be generated using the original IntakeID. For example - if the initial record in the junction table is IntakeID 106 and somehow I have forced that TaskID of 1 (Review Intake with Doctor) and I update the statusid of this on a form to 3 ("Approved for Appointment"), I now need to force a new set of tasks - "Schedule Appointment", "Create Patient Chart", "Send Medical Questionnaire", etc.

To say I am stumped would be an understatement - can anyone help? I am confident that once "shown" I can move forward with all the possible scenarios, but.....any help is greatly appreciated - in the most dumbed down verbiage possible!

[June7]

If you don't want user to select the sequence of tasks, will need code to control. This could get quite complicated. Will each patient always be associated with the same set of tasks?

Want to provide db for analysis? Follow instructions at bottom of my post.

[thaines143]

I will send over June7 - there are approximately 9 tasks right now and 4 status - depending on the status of the task (once that initial task is entered), more tasks are generated. Probably making it sound more complicated than it is -

If you wake up in the morning you may have to
brush your teeth
comb your hair
shower

If you Brush your teeth you have to
Clean the sink

If you Comb your hair you have to
Clean the hair out of your comb
Clean hair gel off the mirror

If you shower you have to
Clean drain
Put wet towel in hamper

The first task is waking up - this would be the task I'm trying to force into the junction table
No matter what you have to do that and let's assume that you also HAVE to do the other three once you have woken up (waking up is now "completed")
You may choose to skip any of the other tasks - leave your towel on the floor, gel on the mirror, etc, but you there is at least an assumed sequence of events and that intial event that sets it all in motion.

Does that make ANY sense?

[thaines143]

Here it is, June - thank you in advance for any help you can give me.

[June7]

I removed your file from post because some of the personal info (address, phone, email) looked real. If they weren't, can reattach file.

I understand sequence of events and decision paths. Starting every intake sequence with the same task is easy enough, after that what do you want? If each intake can follow a different process, how much can be automated? Present user with a series of screens to respond to depending on response in previous screen - like checking in for a flight at the airport or withdrawing cash at an ATM?

Opening AppointmentsVitalsF form triggers "RecordSource does not exist" error.

Opening PatientIntakeF triggers "The form name 'Table1' is misspelled..." error.

Closing IntakeQuestionsF triggers "Unknown function 'Date'" error.

IntakeQuestionQ query is joining IntakeQuestionsT and PatientsT tables on primary keys. This means a one-to-one relationship. Will every patient have a related IntakeQuestionsT record and only one? Then might as well be just one table.

[thaines143]

Hi June - I will start at the bottom

Every Patient will not have an intake, but a patient may only have one intake
I may have removed some tables you need and this is causing the errors(?)

Is there a way to not ask them questions and automate the addition of the next set of tasks?
For instance - The first task will always be "Review Intake with Doctor"

If the status of that is changed to completed, I want the junction table (or perhaps another table I haven't created yet?) to have records added with the TaskID to "Send Medical History Form", "Schedule Appointment", "Create Patient Chart" and if "exposure data" or "medical records" are checked on the Intake Form I also need to add the "Request Medical Records" & "Request Exposure Data". If the status of the original task is changed to "Referred" then I only need to add the task "Followup with Referring Physician" (which may not be in my task list right now). If the status of the original task is changed to "Cancelled" then no additional tasks required.

June, once I figure this out I will also be doing the same thing with scheduling appointments. I think it may be a little simpler because I don't have the many to many relationship to deal with that I do with the tasks and intakes. This one has definately been stumping me, though. Your help on this is greatly appreciated...and thank you for taking the info offline. Most data is real, but not specific to the people its tied to - but you never know!

T

I removed your file from post because some of the personal info (address, phone, email) looked real. If they weren't, can reattach file.

I understand sequence of events and decision paths. Starting every intake sequence with the same task is easy enough, after that what do you want? If each intake can follow a different process, how much can be automated? Present user with a series of screens to respond to depending on response in previous screen - like checking in for a flight at the airport or withdrawing cash at an ATM?

Opening AppointmentsVitalsF form triggers "RecordSource does not exist" error.

Opening PatientIntakeF triggers "The form name 'Table1' is misspelled..." error.

Closing IntakeQuestionsF triggers "Unknown function 'Date'" error.

IntakeQuestionQ query is joining IntakeQuestionsT and PatientsT tables on primary keys. This means a one-to-one relationship. Will every patient have a related IntakeQuestionsT record and only one? Then might as well be just one table.

[June7]

"Is there a way to not ask them questions and automate the addition of the next set of tasks?"
Probably, but sounds complicated. The more 'user-friendly' automation, the more code is required. And the real trick is figuring what event to put code in. Also, having checks in place to prevent duplicate execution. This is something that could take an experienced developer days (couple weeks?) to accomplish, not something I want to tackle.

In whatever event, possibly use INSERT SELECT sql action to create the next task(s) and refresh the form to display the newly created record(s) for user review.