How Do I Set Up a Cell Calculation with a Number from a Related Table?

[Industriallust]

I'm relatively new to Access 2000 Professional and I'm having some trouble understanding how to set up a calculation for a field when the 1 number I need to perform that calculation is coming from a particular cell of a related table. Basically, I need to know how to write a mathematical expression (add, subtract, divide, or multiply) for an entire field of cells when the 1 number I need to perform this calculation is coming from 1 cell of a related table. Can someone help me with some examples? Also, where would I place this expression? In the many individual field cells themselves or somewhere within the Field Properties General tab for the field I need these calculations in? Thanks!

 

[georgedwilkinson]

Yoips, we're gonna have problems answering this cause you're talking and thinking in sreadsheet think (there's no such thing as a cell in Access). Access isn't anything like Excel so it's gonna be hard to answer you apples to apples. I remember when I had to make the change and it was hard to modify my thinking. But we can work through this.

When you create your form, there are a variety of "formulas" or functions that you could potentially put into an unbound text box on the form to show data related to a row of data in your table (DSum(), DCount(), etc.) and you can also do math (just like you do in an Excel cell). The syntax is similar to that of Excel but the function names are all different and behave just a bit differently because of the nature of relational databases. In order to give you more information, we'll need to know the structure of your tables, etc.

 

[Industriallust]

George,

Thanks for responding to my thread, but I'm not exactly sure what you mean when you say "we'll need to know the structure of your tables, etc." I'm currently trying to teach myself Access via a book I picked up, so you should know I'm only as far as knowing how to edit a form. With this said, let me start with a simple problem of trying to create a mathematical expression within a single table, but ultimately have the answer populate the field of subsequent related tables. I have a table called "Nutrition Facts" that has 2 fields: one is called "Serving Size" and the other is called "Calories." I have a form built for this table. What I want to do is divide "Calories" by "Serving Size" and have the answer (quotient) populate the fields of related tables ("1 gram," "2 gram," 3 gram" tables, etc.) I can see where you say I'm thinking and talking in spreadsheet mode. I thought Access would be similar to Excel (which I'm a little better versed in,) but I guess creating expressions/formulas is different with Access. I know that I probably won't see any answers populate the field column of my tables, but rather I'll just see the formula instead. Am I correct? From what I understand, the answers will show up when I run a query. I guess right now if I can know how to create a mathematical expression (I guess in a form from what I'm now gathering) and have that answer populate the field of subsequent related tables, that would be a good place for me to continue. Am I on the right track here?

 

[georgedwilkinson]

Yes, I think so. You don't/shouldn't store the results of calculations in the database unless the criteria you used to calculate them are likely to change (as in a price change for a retail system).

In a query, you'll just type in the formula as though it were a regular field in the table(s). So your column would look like:
[Nutrition Facts].[Serving Size]/[Nutrition Facts].Calories

If you're using the query builder (recommended at first), Access will give the field a name (probably Expr1) which you can change if you want.

If you want to display the calculation on the form: create a textbox on your form (unbound), and put the formula in the textbox during design time as though you were working in Excel:
=txtServingSize/txtCalories

You'll notice I put "txt" at the front of the field names. I assume you will have renamed any text boxes on your form from the default. The wizard usually names the form objects the exact same name as the table column, which leads to some confusion.

Additionally, you shouldn't name any database object (table, column, field, form, report, etc.) with special characters or spaces. You'll notice it makes it harder to type in information if you have them.

At least this is a start to answering your question. Yell back when you have more and somebody'll help out.

 

[MagicMan]

Hi,
I wrote a "How to learn to code a language like VB or use Access" response in the thread "Renumber a List". You might want to read that thread. Hopefully the advice is good.
Smiles
Bob

 

[Industriallust]

Thanks guys! I'll work with the advice you gave me for a while and see if I can get where I need to be. I'll probably end up coming back to this thread in the future. Thanks very much.

Marty

 

[georgedwilkinson]

Looking forward to it. Always happy to help.