Math funtions

[Neimad]

What the best way to perform math functions on data in a table? I have a form and a subdatasheet in it. I want to be able to: Average out data in cells. I.E One field will have a list of numbers and I want to be able to average those out.

The other is that i want to be able to check the previous cell(year) and see if the number has gone up or down?

Many Thanks

 

[neileg]

Have you looked at the Avg and DAvg functions?

 

[Neimad]

No, But I will! Thank you . My main concern is how to interact with a data subsheet. I mean, is there a way to reference them? individual fields and records that is, (record = 2, field = cost for example?). I also take it that it wont be as simple as" = Avg ( [tblname]![field] ) "

Also, is there a function to determine if the overall number is increasing or decreasing? (i have a data field as well)

 

[The_Doc_Man]

In general, when performing what is called a "Domain" function, you place criteria as one of the function's arguments and the name of the field as another one. So you can specify DAvg( field, table, criteria )

The other way is to build a query that selects everything you wanted to summarize. Build it as a SELECT query but then using the sigma button from the iconic toolbar and change that query to a summation query. You can then choose to Sum or Average a field or take its minimum and maximum. If you have a field that includes something like DatePart() to extract only the year of a date, you can choose that field for a GroupBy rather than one of the aggregate functions. You can still place selection criteria or other GroupBy fields. There ARE restrictions on what cannot be there. Like, no detail line can exist that is neither aggregated, Grouped, Sorted, or part of your WHERE clause.

 

[Moniker]

And, no one will ever realize that you chose "Damien", written backwards, as a name.

You're asking about math functions on a machine that is all math and logic, but you haven't posted examples of what you have tried. What did you think would work but it didn't? What was the result of what you tried?

Also, stop thinking of Access as a spreadsheet. (I'm gleaning that from your "cell" reference.) While a table might look a little like an Excel spreadsheet, the similarity is only skin-deep. In Excel, you have cells and rows. In Access, you have fields and records. Completely different.

You haven't explained what you're trying to do. If you're trying to get to the data in a specific field on a subform, then the proper way to reference it is:

Forms("YourMainFormName")![YourSubFormName].Form.Recordset.Fields("YourFieldName")

Use the same format to get to a specific record first if necessary. However, you're trying to do an average, so I would think you would want the entire recordset.