Ms Access Continuous form - with a many to many relationship
Hi Everyone,
I have a form based on tblMain, it has a subform on it that is continuous based on tblCondition. Now I need to allow for each record in tblCondition to be able to have 'many' records in tblMeds. So would this be a continuous form on a continuous form? I know I can't put it in the detail section. But I was wondering since I am early in the deisgn phase, if there is another way I can structure this. Basically each person can have many conditions, and each of their conditions can have many medications.
Thanks,
Jetera
I have a form based on tblMain, it has a subform on it that is continuous based on tblCondition. Now I need to allow for each record in tblCondition to be able to have 'many' records in tblMeds. So would this be a continuous form on a continuous form? I know I can't put it in the detail section. But I was wondering since I am early in the deisgn phase, if there is another way I can structure this. Basically each person can have many conditions, and each of their conditions can have many medications.
Thanks,
Jetera
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Etsherman - I have a question - I think I am doing this wrong. How should I set up my tables?
Basically
1 patient can have many conditions, and each condition can have many medications. I think my tables are wrong. Can you explain more about the table design so Ican get theses forms to work, I guess this is my only option.
Thanks!
Jetera
Basically
1 patient can have many conditions, and each condition can have many medications. I think my tables are wrong. Can you explain more about the table design so Ican get theses forms to work, I guess this is my only option.
Thanks!
Jetera
Hello jetera ...
Keep in mind the suggestions that I proposed are just that .... An overall broad approach to accomplish what you are trying to do because I don't have your db application nor do I have the full scope of exactly how you envision this functioning.
Just based on what you've said ....
The overall idea is to have a Patient table with a unique Patient ID. Then you would need a Patient Details table where you would enter a record for each patient, each condition consisting of the (patient id, condition id). Therefore, when you setup your patient form you could have a sub-form that shows all the patient details (conditions) joined by the patient id field.
Next, since each condition can have multiple meds you will need a condition details table where you would enter a record for each condition consisting of the (condition id, med id). Therefore, when you select a condition id on the sub-form on the main form you could re-query another sub-form to display all the meds based on the current condition.
Also, you would need separate tables to define the Conditions (Id, Name, Other Fields, etc.) and Meds (ID, Name, Other Fields, etc.)
Again, this is an overall big picture of on approach. To work out the exact details with table structure and field names, etc. I would need a copy of your application.
Hope this helps and points you in the right direction.
ET
Keep in mind the suggestions that I proposed are just that .... An overall broad approach to accomplish what you are trying to do because I don't have your db application nor do I have the full scope of exactly how you envision this functioning.
Just based on what you've said ....
The overall idea is to have a Patient table with a unique Patient ID. Then you would need a Patient Details table where you would enter a record for each patient, each condition consisting of the (patient id, condition id). Therefore, when you setup your patient form you could have a sub-form that shows all the patient details (conditions) joined by the patient id field.
Next, since each condition can have multiple meds you will need a condition details table where you would enter a record for each condition consisting of the (condition id, med id). Therefore, when you select a condition id on the sub-form on the main form you could re-query another sub-form to display all the meds based on the current condition.
Also, you would need separate tables to define the Conditions (Id, Name, Other Fields, etc.) and Meds (ID, Name, Other Fields, etc.)
Again, this is an overall big picture of on approach. To work out the exact details with table structure and field names, etc. I would need a copy of your application.
Hope this helps and points you in the right direction.
ET
ASKER
I think the problem is the user needs to be able to add new conditions and then associate new meds with each new condition. So in the condition subform - they can just add a new row. But I am having problems with the medicine subform - because they need to see what was already there and be able to add new ones. SO I wanted to put a combo box on that continuous form, or I guess it could be datasheet and then allow the user to select another med if need be.
Either way I can't get it to work. I might have to send my file to you.
Either way I can't get it to work. I might have to send my file to you.
>>>Either way I can't get it to work. I might have to send my file to you.<<<<
Ok, you can upload a Access 2000 version to http://www.ee-stuff.com/ and associate it with this question for all to review.
OR
You can send a zipped Acces 2000 version to my email address located in my profile.
ET
Ok, you can upload a Access 2000 version to http://www.ee-stuff.com/ and associate it with this question for all to review.
OR
You can send a zipped Acces 2000 version to my email address located in my profile.
ET
"You can send a zipped Acces 2000 version to my email address located in my profile."
***** No, you cannot do that ... it violates EE rules. You can *only* upload to EE-Stuff ... so that everyone can access the file.
mx
***** No, you cannot do that ... it violates EE rules. You can *only* upload to EE-Stuff ... so that everyone can access the file.
mx
Thanks MX ... I only suggested option 2 as a last resort in case the asker did not know how to use ee-stuff.com. In that case I generally load it up there anyway.
No Problem though.
jetera just scratch option #2, ok.
ET
No Problem though.
jetera just scratch option #2, ok.
ET
ASKER
Ok I have a meeting until the end of today- I may upload it tomorrow. I am open to any other design of it now - apparently the client has said "Whatever you need to do to make it work - then go ahead".
So if you have any other better ways of representing it all- ie list boxes or whatever- I am open to that too.
I will log back on tomorrow!
So if you have any other better ways of representing it all- ie list boxes or whatever- I am open to that too.
I will log back on tomorrow!
ASKER
How do you make them grow in relation to one another?