How to perform column formulas 2

[mtownbound]

Just want to have one of the columns perform a mathmatical function using two of the other columns as sources(kinda like in excel).

What's the easiest way to do this??


Thanks!!!!!

 

[LittleSmudge]

If by "Columns" you mean FIELDS in a table then Don't. It breaks sooooo many Normalisation and Data integrity rules.

IF by "Columns" you mean Controls on a continuous form then in the control's ControlSource put

=[txtName1] + [txtName2]

etc..

Use the Names of the other Control boxes and whatever mathamatical functions you need.


'ope-that-'elps




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[mtownbound]

Yes, "Column" meaning fields. If this is such a violation, maybe I should just perform this calculation in excel on the whole column. Any other suggestions


Thanks!!

 

[WildHare]

One of the easiest ways to do this is to reference the query in Access that you want, as an embedded object in a worksheet.

Let Access maintain, sort and manage the raw data - let Excel do all the crunching and calculating.

DATA / Get External Data / New Database Query / and then point to your saved Access query...



JMH


Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[mtownbound]

Thanks for the info JMH!! Now not to get simple, but what's the wildcard(?) to perform a function for the whole column?


Thanks!!

 

[WildHare]

Huh? You lost me on that one.

Can you elucidate? Or at least, de-obfuscate somewhat.. ?



JMH


Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[mtownbound]

sorry about that :-o

Just trying to perform a function that says "column D" = "Column A" divided by "column B". However, not just for one cell, but all the "D" cells.

Hope I made it a little clearer

 

[LittleSmudge]

You have to ask yourself WHY you are doing this.

The usual answer comes into the category of "Because I want the user to see the result on a Form or in a Report."

SO the solution is to apply the second line of my original post.
You store ColumnA in FieldA in the Table, You store ColumnB in FieldB in the table

and then one the Form or report you put a TextBox control bound to FieldA and call it ColumnA and you have a text box control bound to FieldB and call it ColumnB. Then you have an UNBOUND text box control with the Control Source
= [ColumnA]/[ColumnB]

The important point being that you NEVER "Store" the result of any calculation in a table. You store the Operands only.


Job Done.


'ope-that-'elps.





G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[WildHare]

'smudge has it pegged, if that's all you want to do, then in the report, or in a query, do the field-to-field calculations and display the results.

Thanks, GLS

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[mtownbound]

I get the "data only in the db, calculations in the spreadsheet", but what is the syntax in Excel to do the division for the whole column? I've tried 'Quotient(A,B)', and MOD(A,B) but neither is working. Any suggestions??

Thanks again for the assistance!!!!

 

[WildHare]

Are you asking an Excel question or an Access question?

Excel Division:

= ColxRowx / ColYRowY, e.g. = C2/D2

Use the fill handle to drag the formula all the way down the column.

In Access Query grid:

Expr1: [FieldNameX] / [FieldNameY]

As you can see, the division symbol is the forward slash.

Now, in WORD, you can put these guys in a table, and in one of the cells, type the formula the same way, keeping in mind that Word tables use a ROW-COL identification syntax instead of the COL-ROW syntax of Excel. Use Table/Formula and type the division calculation.

In Powerpoint, insert a slide with a table object and then....well, never mind.





Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[mtownbound]

Well it started out as an Access issue, then slowly transformed into an Excel question. Anyway, I finally got it straight and I'm calculating in Excel, storing in Access, and reporting with Crystal(Now if I could just get somebody in the Crystal Forum to help me with an issue)!!!

Again, thanks for all the help!!!!