Show data from a form/subform in a query

[MicheleHughes]

Hello everyone,

I am very new to Access and I have bought three textbooks on Access 2007. I have been learning as I go, but now I am stuck and don't know how to get Access to do what I need. Any help would be greatly appreciated!!

I wish to have a query that will show data that is calculated in form/sub-form and also return that data into the table.

For example, form is recipe, sub-form is ingredients of that recipe...I have the calculated cost of each ingredient based on prep factor entered into sub-form. This data only displays in the form. I want to have it also return that result (recipe unit cost)to the table tblrecipe as well as a query that will list all this and the total cost per lb of inventory on hand and a grand total of all recipes and ingredients alike.

I might being trying to do this the hard way, but when I do inventory on the prepared recipes by weight, I have to look it up one-at-a-time and figure the calculations. I want to be able to enter the inventory on hand in the table and have Access calculate the recipe unit cost per lb times the inventory on hand and I can query the data which is >0, show it only shows the items we have on hand, the name, cost per unit, inventory on hand, and make the necessary calculations.

Is this possible? I have attached a copy. (Many of the fields are hidden in the tables.)

 

[Galaxiom]

To answer the questionin the title
http://access.mvps.org/access/forms/frm0031.htm

Queries need the ones that are addressed via the forms collection.
(They start with "Forms!")

However your question is different. The calculated values should not be stored in the table.
Calculate them in a query every time you need them.

Read about "normalization" in your textbooks.

 

[MicheleHughes]

Thanks Galaxiom,

There is alot of new terminology with Acess, "normalization" is the word I was looking for, kinda hard to search for something when you don't know what it is called. I did figure out the calculations thing today, and was able to generate the queries and reports that I needed for inventory, but I still have an issue with pulling data from a form/subform. "RecipeUnitCost" is calculated in the subform, so how do I get that to display in a query and be able to calculate cost per Recipe Unit times quantity on hand?


Thanks in advance!
Michele

 

[jdraw]

Here are a couple of great resources

http://forums.aspfree.com/attachment.php?attachmentid=4712 RDBMS overview/intro

http://www.rogersaccesslibrary.com/forum/topic238.html the TOPICs section has very good info on database design, normalization, entity relationship diagramming and the Hernandez Process

Info on referencing form and subform

http://access.mvps.org/access/forms/frm0031.htm
http://support.microsoft.com/kb/113352

 

[MicheleHughes]

Thanx jdraw,

I will check those out!