Full Version: Subform Total
UtterAccess Forums > Microsoft® Access > Access Forms
warrenjburns
How do I get a subform total already brought to the master form to update a field in the forms original table?
able is called recipes, the subform is the ingredients and costings, the total of the subform is displayed on the form but would like it to update the cost price field in the recipe table.
Please help.
Warren.
cpetermann
Warren,
Welcome to UA! -o!
Would you copy your db, compact & repair, zip & attach it?
It's easier to know what suggestions to make if we can see the db.
From your description, it sounds like you may have a normalization issue, as ingredients and price
should be stored in a separate lookup table.
Also, any values that can be calculated are rarely stored in tables.
Looking forward to seeing your db.
Cynthia
dashiellx2000
This would be storing a calculated value and would violate the rules of normalization. Rather then store the number, you want to calculated it at run-time as needed.
TH.
warrenjburns
Hi,
Thanks for the reply.
Oknow what your gonna say when you see my database and im going to tell you that im not a programmer or it specialist, but have needed to to do this as a life time project. Being a chef i have collected many recipes and understand what is required to cost, organise and compile information used within my work environment so im trying to put this in a database that could possibly help others also and especially small resorts and food and beverage operations.
I have kept my database user friendly but not programmer friendly and you will find a lot of names that dont work well with sql... im hoping over time when the foundation of the database has been formed that i can go throug and fix my sql formats... but the database works well so far and is easy to understand from a chefs point of view.
Please take a look mostly at the recipe calculation i have done.
My problem is that i need costs of stock items, basic cuts and preparations available to create more basic cuts, preparations and also beverages and menu items.
Have thought about having one table that compiles menu items and beverages and another that compiles stock items, basic cuts and preparations this may fix some combo box issues but have opted to create a union query that compiles these tables for combo box in the respective subform in the verious tyles of recipes i have created.
I have used a basic requistion, invoice form and subform format to create the recipe form, i need this to update automatically a collection of these costs to be used to continue developing new recipes.
Please take a look maybe you will find it interesting, possibly amusing and let me know your comments on how i can improve my humble database.
Thanks for your time and effort to help me with my concerns.
warren.
cpetermann
warren,
Please try to attach your db again-- it didn't seem to work.
How to attach file
cynthia
warrenjburns
Attached again.
Thanks
cpetermann
Warren,
For not being a programmer you have made a good start!
However, there are some normalization issues that I would suggest you try to take care of now
rather than later.
I will study your db tonight (it is 10:45 pm my time) and get back to you tomorrow with some suggestions,
unless someone else is faster than I am:)
Cynthia
warrenjburns
Thanks Cynthia,
ook forward to your suggestions.
Take care.
cpetermann
Warren,
You have taken on a huge project!
On answer to your original question regarding storing a total--DON'T!
Any total that can be calculated using exisitng data should not be stored.
That would be asking for trouble and you would risk data corruption.
If you were going to use this db just for yourself we would probably stop there.
However, and please don't be discouraged, since you would like to offer this db to others,
I would suggest that you read the following articles, and then take a fresh look at your db.
For example:
Units of Meaure table does not have a PK assigned
lookup fields and subdatasheets--remove these
each of your tables that have a detail table--these detail tables are all exactly the same
which tells me that what you intend to store in these tables may be redundant.
These Detail Tables should be used to store the PKs of related tables

For example:
tblBasicCutsDetail
BasCutDetID Autonumber PK
BasicCutID LongInteger FK to BasicCuts Table
MethodID LongInteger FK to Method Table
EquipReqID LongInteger FK to EquipmentTable
you wouldn't store a staffID and DeptID in this table--
It would be more logical to store that information in the RecipePrep Table or RecipePrepDetails Table.
Please read these

From Alan G.
1 Use an Autonumber as the primary key in all of your tables
2 Use a Number (Long Integr) foreign key in your many side tables to link to primary keys
3 Don't use spaces in the naming of table fields or objects
4 Don't use reserved words (eg Date or Time) as the names of fields
5 Don't use lookups at table level --ever SEarch UA for Evils of Lookup Fields. Always use lookup tables
6 Store dates and times in a single field, not two fields
7 Use lookup tables for combos/lists instead of value lists
8 Set the Master/Child links for forms/subforms setups
No one was born understanding the Rules of Normalization;) After reading these articles & links,
on a sheet of paper try sketching out 1 recipe from Supplier to Guest
What Main steps would that recipe follow and then what Substeps and how would Step1 relate to Step2, etc?
How could you simplify and streamline your tables?
What data could be stored in a lookup table?
HTH,
Cynthia
warrenjburns
Thanks Cynthia and Alan G.
This is just what i needed.
I have a lot to carry on with thankyou very much for your help guys.
Ill get back to you once ive processed all this new information.
Thanks again
warren.
warrenjburns
Hi Cynthia,
dd re-thought the recipe section of the database.
Have created autonumbers as primary keys.
Have removed all lookups in master tables and created lookup tables, i hope this is what was ment as there is not much information on lookup tables. I assume that it is basically the primary key form two related master tables storing information on the relation to be put together in a query at some later stage as i have done with the recipe query to create the recipe form. Seems like a lot of detail lookup forms so dont know if ive really understood this well and the query has had some relationship lookup issues.
Have removed spacing and revised table names and field names.
Read through a lot of documents on normalization.
Have created a simple recipe database and attached for you to take a look at to understand a little about the relationships of the tables. [INGREDIENTS] is the main table with all stock items and recipes. Forms were created to add either [STOCK_ITEMS] or [RECIPES] to this table. A new recipe can then either choose stock items or other recipes to create a new recipe.
Had problems assigning [UNIT] and [STOCK_ITEM_CATEGORY] to the 2 forms ive created.
Would like to know if this is going in the right direction and if i should carry on with this style.
look forward to your help.
warren.
cpetermann
Warren,
everal concerns,
1. Table names and field names are all capital letters.
Access uses all caps for reserved words. Using all capital letters in your db could make it
difficult to read and especially difficult to read queries.
2. Lookup fields at table level are not a good idea--it is better to use combo boxes in your forms to
lookup data--you still have somelook up fields.
3. I don't think that you need two primary keys in a table for this particular db
4. It is best to have the PK of a table reflect the name of the table
5. Your main table should probably be recipes so for example:
tblRecipes
RecipeID autonumber PK
Recipe txt The name of your Recipe
Then a table to collect the details of your recipes
tblRecipeIngredientDetails
RecIngDetID autonumber PK
RecipeID FK to the Recipe Table
IngredientID FK to IngredientTable
UnitOfMearueID FK to UnitOfMeasure Table
Qty number of the units of measure of the ingredient to include in this recipe
A recipe may have many RecipeIngredientDetails
You may need a tblRecipeMethodDetails
RecMethDetID
RecipeDetailID
MethodID
this would be a junction table that joins the two tables together.
Please go back and read the articles again, then come back if you have more questions.
HTH,
cynthia
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.