Creating Relationships - Required?

[RainLover]

Some of this is minor but still worth pointing out.

You have used a Value List. If this changes then YOU have to go in and fix it. No one else can. This is known as Hard Coding. You are better off with a separate table to store the values which can have greater access by other people.

Use a Combo Box the same as you have previously done.Give the Combo a proper name. Combo 23 has no meaning to it. Try CboMeal or CnboParient and so on. Do it now and as you build. Don't say I was going to come back to it.

If you are going to show your work to someone else make sure it is as tidy as possible.I usually put the Foreign key immediately under the primary. This is not a Rule just makes it easier to read at times.

TblPatients has some of those typical mistakes that some people do. No Spaces in Names. No # Do you really need to use the Data type Memo. You cannot search sort etc. on a MEMO field. 255 for a Text box is usually enough.You use note in the description of each field in a Table. I have never found a benefit in that but if you wish continue using it until you are more comfortable designing.

Now for the Business stuff and what is what.

The Patient is given food from the table tbljnFoodPapients. Appears to be ok. But is it????

There really can't be much wrong with tblPatients.In FoodMeals there is One record made up of a selection from Meals and another from Foods.

This is where your problem lies.

Can you explain the use of tbljnFoodMeals. I cannot come up with a single positive use for this table. Same will apply to the other join table although I have not looked at it yet. So put your SIMPLE ENGLISH hat on and enlighten me.

Please post the latest when you are ready. Can you change things to make opening it a little easier. I did have a few problems as I could only get to a Maximised Table.

 

[RainLover]

Leo

What is happening. Have you given up or did I upset you.

 

[Leo]

Leo

What is happening. Have you given up or did I upset you.

Never…

If anything, I am thankful for your help. I am not mad but I am frustrated!

I have been consuming too much time attempting to understand - which I believe I do - the use of junction tables. However, I cannot apply them into practice. I have been disassembling my database - so currently I have nothing useful to show. At this point, my conclusion is that I don't need two junction tables.

First, I am having a one-to-many relationship between patients and meals. This eliminates the need for the junction table between patients and meals.

Secondly, I am attempting to have a many-to-many relationship with meals and foods. Which makes sense - to me. However, each suggestion I have received mentions using a combo box for selecting data from the junction table. However, that means I have to populate the junction table manually in advance of its use and the user is to know how the numbers are associated? Not very intuitive…

Anyway, I have been using the following guidelines to help me understand - also been watching youtube videos:

Data Entry

When deciding how to set up your data entry form, you've got three basic options:

1. Create a form based on the tblClasses table, and a continuous subform based on the tblClassMembers table. In the main form, browse to the class (or create a new record for a new class). In the subform, use a combobox to select each student that is attending the class.

2. Create a form based on the tblStudents table, and a continuous subform based on the tblClassMembers table. In the main form, browse to the student (or create a new record for a new student). In the subform, use a combobox to select each class that this student is attending.

3. Create a form based on the tblClassMembers table. Use comboboxes to select the student and the class.

Source Reference:
http://www.utteraccess.com/wiki/index.php/Junction_Tables_(Many-to-Many_Relationships)

 

[RainLover]

Leo

I am flat out like a Lizard drinking at the moment. I havn't read your reply properly as yet. I will do that later.

Have a look at the attached. If it still works. I wrote it ages ago. It does contain most of the answers you seek.

 

[RainLover]

The process is a patient can have multiple meal plans (breakfast, lunch, dinner). Each meal is going to be made from many food items.

Leo

Would I be correct in saying that Patients have Meals.

Meals are made up of One or More Food items.

After these meals have been created will they be used many times. Also from time to time Meals are removed from the list of choices and new ones are created. You would never alter an existing Meal. If you do you change history.

If this is true then I believe I understand what you are doing. It is only the Meals/Foods table we are having problems with. I am sure we can solve that one. It won't be too hard.

 

[Leo]

I got it!!!

I created a table(s) relationship that works! Thanks for your help…

However, now I am beginning to have more questions as I attempt to make use of this option (junction tables). How would:

I need to categorize the food selections by meals; Breakfast, Lunch, Dinner & Snacks - yes and be able to remove items (if needed).

I have the "FoodItems" made available via a combo that uses the data found in the FoodItems field in tblFoodsTest. Can I display the details associated with the FoodItems field (example: nutirent, calories, servings, etc. (I have not created these fields in my example - just thinking ahead).

I have uploaded what I currently have…note only reference the tables marked "test" with the associated junction.

 

[RainLover]

Leo

I will get to it a little later as I have a couple of things to do just now.

 

[RainLover]

Leo try this. Your Junction table and your Foods table just to name two at this stage need a name that people understand. The Autonumber is still your identifier so jst add a name like Breakfast or Lunch. Then for food there is Eggs, Fish Cow etc.

Your Table tblPatients needs to be able to select many Meals. So squeeze a new table in there.

Primary Key
Foreign Key to tblPatients's Primary.
Foreign Key to tblMeals's Primary

So now the Parient can have many meals. You might also add a date and some other data.

I need to categorize the food selections by meals; Breakfast, Lunch, Dinner & Snacks - yes and be able to remove items (if needed).

Add another Field and give it a Category. Therefor you need a table tblCategories to store the available choices.

We should not delete the Meal in the Lookup Table as it will delete everywhere. Just add a Field for Active or Inactive. You may wish to add a date also.

I see you have worked out how to design a Combo. Shame about the name. This is a must fix. If you come back in 6 months will you know what the number means.

BTW when did you last back up. When I am in design mode I would do anything from 3 or 4 to a dozen or more every half hour. Do not delete older backups as you don't know how far you may need to go back.

Hope that is enough for you to make you totally confused..