By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,225 Members | 1,434 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,225 IT Pros & Developers. It's quick & easy.

Relationship problem when inserting records

P: 7
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:

In the query I set the Right joins so I could be able to enter data in the fields.
Expand|Select|Wrap|Line Numbers
  1. SELECT MenuByCycle.CycleID, MenuByCycle.Year, MenuByDay.Day, 
  2. MenuByDiet.TipoOfDietID, FoodPerMenu.FoodID,   
  3. FROM ((MenuByCycle RIGHT JOIN MenuByDay ON MenuByCycle.MenuByCycleID = MenuByDay.MenuByCycleID) RIGHT JOIN 
  4. MenuByDiet ON MenuByDay.MenuByDayID = MenuByDiet.MenuByDayID) 
  5. RIGHT JOIN FoodPerMenu ON MenuByDiet.MenuByDietID = 
  6. FoodPerMenu.MenuByDietID;
Sep 29 '08 #1
Share this Question
Share on Google+
5 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello, Papote.

Pay attention to the fields retrieved by the query you've posted. Those participating in relationships are all PKs of the correspondent tables. No wonder, form control bound to such a field will add new record to PK-side table rather than modify FK field value in FK-side table.

Regards,
Fish
Oct 1 '08 #2

P: 7
The retreived fields aren't PKs. Only in the Order by clause.
I have retrieve the fields CycleID and Year from MenuByCycle table and Day from MenuByDay table to make a where statement it shouldn't appear in the end query. I am missing the MenuByDietID field from MenuByDiet table.


Hello, Papote.

Pay attention to the fields retrieved by the query you've posted. Those participating in relationships are all PKs of the correspondent tables. No wonder, form control bound to such a field will add new record to PK-side table rather than modify FK field value in FK-side table.

Regards,
Fish
Oct 1 '08 #3

FishVal
Expert 2.5K+
P: 2,653
Ok, I've experimented with that a while.
Apparantly, Access join automation creates records properly only if correspondent FK is retrieved within query. Check whether is it so in your query.
Also, relationship between [MenuByDiet] and [FoodPerMenu] tables is not clear.

Regards,
Fish.
Oct 1 '08 #4

P: 7
This is what I originally had.
zshare.net/download/19770145d4f6257a/
Pass:tester0001.?
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.

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

P: 7
I managed to to do this via code. A couple of Ifs and dlookups. Works pretty good controlling the the records that need be created.

I am worried that when I upsize to MS SQL Server 2005, that the Dlookups won't work.
Oct 16 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.