How to insert calculated fields with range functions in Access??

[SebastianColombia]

Hello to you all!! I had already posted a thread like this, but I've decided to make a video, since I think it would be easier for me to explain what I'm trying to achieve.



So..here it is: https://www.youtube.com/watch?v=5NrjkKIh-c8

Basically what I'm trying to do is insert a calculated field in Access with the kind of function you create with ctrl+shift+enter. Is it possible to create such a function??

Thanks

[ItsMe]

In Access, you do not normally store calculation in tables. Tables are for storing data. Calculations are usually performed in Reports or Queries. I suggest you create a query using the Query Designer. There are functions that can be used in your query. There are tools in the query designer that can help you with adding functions to Totals queries. At the top of the Designer, in the Ribbon, there is a Totals tool. Look for the Sigma icon and the word, Totals.

Now, with a table added to the design surface, you can add fields to the Design Grid by double clicking fields within the table.

Towards the bottom of the Query Designer window, you can use the Total: row to select an option from the Total field. For instance, you may want to change the selection from Group By to Sum or to Max.

After you look into this, you may discover that you need something more complex than what these tools offer. Remember that you can save one query object and place the query you just saved inside another query object, as a subquery.

[SebastianColombia]

In Access, you do not normally store calculation in tables. Tables are for storing data. Calculations are usually performed in Reports or Queries. I suggest you create a query using the Query Designer. There are functions that can be used in your query. There are tools in the query designer that can help you with adding functions to Totals queries. At the top of the Designer, in the Ribbon, there is a Totals tool. Look for the Sigma icon and the word, Totals.

Now, with a table added to the design surface, you can add fields to the Design Grid by double clicking fields within the table.

Towards the bottom of the Query Designer window, you can use the Total: row to select an option from the Total field. For instance, you may want to change the selection from Group By to Sum or to Max.

After you look into this, you may discover that you need something more complex than what these tools offer. Remember that you can save one query object and place the query you just saved inside another query object, as a subquery.

ItsMe thanks for answering..Ok so let's say that I want to make a query in which I'm going to insert some calculated field. My question is: Is it possible to create a function the kind of one you create in excel using ctrl+shift+enter as I show in the video?? The function uses the whole field as the argument.

Thanks!!

[ItsMe]

Is it possible to create a function the kind of one you create in excel using ctrl+shift+enter as I show in the video??

Yes, however, it may not be as straight forward as you would like it to be. You will have to familiarize yourself with tables and queries in Microsoft Access. It may be as easy as using the Max() function. You will have to play around with your data and see if that is what you are looking for. Spreadsheets are not like tables.

I suggest you follow the suggestion in post #2 and look into the Max() function.

Towards the bottom of the Query Designer window, you can use the Total: row to select an option from the Total field. For instance, you may want to change the selection from Group By to Sum or to Max.

[SebastianColombia]

Thanks ItsMe, I guess I'm going o spend several weeks playing around with my data.