Relationship problem when inserting records

[Luzbel]

I am making a menu ordering system.

These are the main tables:
MenuByCycle
MenuByCycleID (PK)
Year
CycleID

MenuByDay
MenuByDayID
Day
MenuByCycleID (FK to MenuByCycle)

MenuByDiet
MenuByDietID
TypeOfDietID (PK)
MenuByDayID (PK, also a FK to MenuByDay Table)

FoodPerMenu
FoodByMenuID (PK)
MenuByDietID (FK to MenuByDiet Table)
FoodID

In a year there are 3 cycles.
The Menu changes daily depending on which cycle it is on.
There are different types of diets, and each day there is a food menu for each type of diet.
The MenuByDiet is a junction table between MenuByDay and FoodPerMenu.

Via data sub-sheets it works great but once I make a query to add a record in FoodPerMenu table it creates a duplicate MenuByCycle, and MenuByDiet records.


SQL Code:

SELECT MenuByCycle.CycleID, MenuByCycle.Year, MenuByDay.Day,
MenuByDiet.TipoOfDietID, FoodPerMenu.FoodID,
FROM ((MenuByCycle RIGHT JOIN MenuByDay ON MenuByCycle.MenuByCycleID = MenuByDay.MenuByCycleID) RIGHT JOIN
MenuByDiet ON MenuByDay.MenuByDayID = MenuByDiet.MenuByDayID)
RIGHT JOIN FoodPerMenu ON MenuByDiet.MenuByDietID =
FoodPerMenu.MenuByDietID;

In the query I set the Right joins so I could be able to enter data in the fields.

 

[fneily]

The MenuByDiet is a junction table between MenuByDay and FoodPerMenu." Nope. A junction table, at minimum, contains the primary key of the other two tables. So you probably mean:
MenuByDay
MenuByDayID
Day
MenuByCycleID (FK to MenuByCycle)

FoodPerMenu
FoodByMenuID (PK)
FoodID

MenuByDiet
MenuByDietID PK
TypeOfDietID FK
MenuByDayID FK
FoodByMenuID FK

 

[Luzbel]

I had originally TypeOfDiet in the MenuPerDiet Table as a Primary key since there are many Types of diets for each day (1 day - many diets). Should I put all 3 as Primary Keys then?

In this normalized form I should have about 3 records in the MenuByCycle table per year, since there are 3 cycles per year. 21 records per year in the MenuByDay table, since there are 7 days in a cycle and there are 3 cycles in a year, and so forth.

With the query I made if I add a new record it creates a new MenuByCycle record (even if it is the same cycle and year fields), and the same for the other tables.

 

[fneily]

Ok, let's see.
Three cycles per year. So:
tblCycles
CycleID Primary Key
StartDate
EndDate
Description

Now, since it wasn't explained,
I presume a diet has a menu. That diet's menu can change.
I presume a menu has food. The food for that menu can change.

So alot of many-to-many relationships. So junction tables are needed.

tblFood
FoodID Primary Key
Description

tblDiets
DietID Primary Key
Description

tblMenu
MenuID Primary Key
Description
MenuDay

Now you must connect the food to the menus.
tblMenuFood
MFID Primary Key
MenuID
FoodID

You must connect the diet to the menu.
tblDietMenu
DMID Primary Key
MenuID
DietID

You must connect the cycle to the menus.
tblMenuCycle
MCID Primary Key
CycleID
MenuID

Given it a try - For Cycle1, Cycle1 uses those menus. Those menus consist of those foods. And those menus belong to those diets.
Diets can be changes to different menus and menus can have their food components changed.

 

[Luzbel]

This is what I originally had.
Some items are in spanish...

I include a working version of the form used to enter / view the menu MenuByCycleEntry.

I wanted the subforms to link like the MenuByCycle table nested sub-datasheets upto ordering by institution per food menu item.

Pass:tester0001.?

 

[fneily]

You wouldn't have a standard .zip file instead of Mr. Roshal's ARchive (RAR)?

 

[Luzbel]

Sure no problem.

BTW, you are right to assume that each diet has a menu and each menu has food.

There are 13 different types of diets for each day.
Each type of diet has it's own menu which contains different food.

 

[fneily]

right to assume that each diet has a menu and each menu has food." I know that. What I was assuming was
that a diet A can have menu A today. But next month diet A can have menu B.
And that menu A can foods A,B,C today but next month menu A can have foods X,Y,Z.

Also, your zip file has a password on it. I can't open it.

 

[Luzbel]

Password: tester0001.?
Going back again.
There are 3 cycles in a year. Each cycle lasts a week. After that it starts all over again within the year.
Each cycle has 7 Days.
Each Day in a given cycle has a different menu.
The Food in the menu depends on the type of diet
Each type of diet has a different menu.

 

[fneily]

I'm guessing institutions order by menu.
You don't need your Days table. Overnormalized.
I have the main tables:
tblInstitution
InstitutionID
Name
Address
etc.

tblFood
FoodID
Description
TypeOfFoodID
Rate
Weight

tblTypeOfFood
TypeOfFoodID
Description

tblDiet
DietID
Description

tblCycles
CycleID
CycleNum (I,II,III)
StartDate


tblMenu
MenuID
MenuDay (monday, tuesday, etc.)
Description

Now you need to connect them.
tblMenuDiet
MDID
MenuID
DietID

tblMenuFood
MFID
MenuID
FoodID

tblMenuCycle
MCID
MenuID
CycleNum

tblOrders
OrderID
InstitutionID
OrderDate
MenuDateDelivery

tblOrderDetails
OrderDetID
OrderID
MenuID
Quantity
comment

A one-to-many relationship between tblOrders and tblOrderDetails.

The tblCycles table takes care of date changes from year to year for each cycle and let's you run a history.

When an institution orders, depending on the day, that dictates which cycle the menu is coming from. So you have a query that takes the OrderDate and uses it as criteria. The query would be on the tblCycles table. In your query grid, you'd have CycleNum and Startdate. You'd click the Totals button to show the Totals row. Under CycleNum you'd have Group By. Under Startdate you'd have MAX. On the criteria line under Startdate, you'd have < Forms![Formname]![OrderDate]. This would give the Cyclenum and thus you can use this to show the appropriate menus in a dropdown box.