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

A2K3 table design issue? - Populating table fields with summary data from same table

P: n/a
I have a relatively simple (I assume) issue which I am at a complete
loss to address. My issues is: I want to populate fields in my tables
with summary data from the same table. Let me explain:

tblItemDetails (contains data on food products purchased)
Item_Description_ID (key, source link to tblMenuItemRecipe)
Item_Unit_of_Measure
Item_Location
Item_Type
Item_Category
Menu_Unit_Of_Measure
Menu_UOM_Conversion

tblMenuMaster (contains information about restaurants Menu)
Menu_Description_ID (key, source link to tblMenuItemRecipe)
Menu_Category
Menu_Type
Menu_Memo

tblMenuItemRecipe (builds the recipe for each Menu item and ultimately
prices out each recipe)
Recipe_ID (key)
Menu_Description_ID (many link to tblMenuMaster)
Item_Description_ID (many link to tblItemDetails)
Item_Quantity_Used
Item_Yield
Item_Memo

Also, I have several other tables which contain invoice / costing data
that price out and inventory each "Item" (i.e., Item_Description_ID).
Finally, I have created (successfully) a form that allows users to
enter the recipe data which populates the fields in tblMenuMaster and
tblMenuItemRecipe.

The problem: A recipe in the tblMenuItemRecipe (e.g., mashed potatoes)
is also used as an "Item" (e.g., Item_Description_ID) in other recipes
(e.g., shepherd's pie, where mashed potatoes are used). Something else
to keep in mind - ultimately, each recipe will be priced based on
current invoice data; I plan to have the sub-assembly recipes (e.g.,
mashed potatoes) carry through with all the pricing information.

I believe that this is a table design issue that I don't have the
experience to address (note: I'm a self taught Access user without the
$$$ to buy a commercial restaurant program). I'm open to modifying the
table design, or if more appropriate form / query design.

Thanks in advance to anyone who can provide some insight :D, even if it
is an article or book that I can purchase to solve this problem.

Kelii

Mar 28 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
As a first thought, you can include multiple version of the same table in
an Access query (they will be called Table,
Table-1, Table-2, etc). If you only want totals, then you have to create a
(sub)query (let's call it qrySumofTable), but
you can still include that in your top-level query.

In your case, what I would do is add a field into your Items table, which
identifies something as being
1) An indredient (1 egg), or
2) An assembly (mashed potatoes = certain quantities of potato, egg, milk,
water)

You need a routine which will construct assemblies (what indredient, what
quantity)

You will need to change your recipe-build procedure so that it can include
both Indredients and Assemblies.

For example, Fried Egg with Mashed Potatoes = an egg, but also egg in the
mashed potatoes.

You can re-price ingredients - but not assemblies (that should be
calculated on-the-fly)

HTH
On Tue, 28 Mar 2006 17:23:03 +0200, <ke****@yahoo.com> wrote:
I have a relatively simple (I assume) issue which I am at a complete
loss to address. My issues is: I want to populate fields in my tables
with summary data from the same table. Let me explain:

tblItemDetails (contains data on food products purchased)
Item_Description_ID (key, source link to tblMenuItemRecipe)
Item_Unit_of_Measure
Item_Location
Item_Type
Item_Category
Menu_Unit_Of_Measure
Menu_UOM_Conversion

tblMenuMaster (contains information about restaurants Menu)
Menu_Description_ID (key, source link to tblMenuItemRecipe)
Menu_Category
Menu_Type
Menu_Memo

tblMenuItemRecipe (builds the recipe for each Menu item and ultimately
prices out each recipe)
Recipe_ID (key)
Menu_Description_ID (many link to tblMenuMaster)
Item_Description_ID (many link to tblItemDetails)
Item_Quantity_Used
Item_Yield
Item_Memo

Also, I have several other tables which contain invoice / costing data
that price out and inventory each "Item" (i.e., Item_Description_ID).
Finally, I have created (successfully) a form that allows users to
enter the recipe data which populates the fields in tblMenuMaster and
tblMenuItemRecipe.

The problem: A recipe in the tblMenuItemRecipe (e.g., mashed potatoes)
is also used as an "Item" (e.g., Item_Description_ID) in other recipes
(e.g., shepherd's pie, where mashed potatoes are used). Something else
to keep in mind - ultimately, each recipe will be priced based on
current invoice data; I plan to have the sub-assembly recipes (e.g.,
mashed potatoes) carry through with all the pricing information.

I believe that this is a table design issue that I don't have the
experience to address (note: I'm a self taught Access user without the
$$$ to buy a commercial restaurant program). I'm open to modifying the
table design, or if more appropriate form / query design.

Thanks in advance to anyone who can provide some insight :D, even if it
is an article or book that I can purchase to solve this problem.

Kelii


--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
Mar 30 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.