Series-Based Functions

A series or collection-based function is one that considers a particular column and performs an operations on all of its cells. For example, if you have a particular column in which users enter a string, you may want to count the number of strings that have been entered in the cells under that column.

Sponsored Links:

Related Topics

Built-In Functions Fundamentals
Once again, since Microsoft Access doesn't inherently provide a programming environment, it relies on logical functions to take care of this aspect. The Choose() function is one of those that can test a condition and provide alternatives. The Choose() function works like nested conditions. It tests for a condition and provides different outcomes depending on the result of the test.
Learn: The Switch Function, Arithmetic Functions, String Functions, Date and Time, Series-Based Functions, Domain-Based Functions, Business Functions and Finance Functions.


Finance-Based Functions
Microsoft Excel provides a series of function destined to perform various types of financially related operations. These functions use common factors depending on the value that is being calculated. Many of these functions deal with investments or loan financing.


Database Numeric Values
A number is a digit (0, 1, 2, 3, 4, 5, 6, 7, 8, or 9), a combination of digits, or a combination of one or more digits, a separator, and one or more digits. Microsoft Access supports three categories of numbers and there are various ways you can apply one to a field. You can work in either the Datasheet View or the Design View of a Table. In the Datasheet View, you can use some of the fields from the Field Templates and this options has some limitations. In the Design View, before specifying the particular numeric type of a field, first set its Data Type to Number.
Learn: Integer-Based Data Types, Decimal Data Types, Arithmetic Functions, Series-Based Functions, Business Functions, Finance Functions.


Queries-Based Functions
These functions can also be used in queries and even included in SQL statements. The SQL interpreter of Microsoft Access can recognize these functions as long as you use them appropriately.
Topics: Queries and Built-In Functions, Summary Queries and SQL Aggregate Functions.


Introduction to Built-In Functions
Before creating a new function that perform a specific task, first find out if that function exists already. The library built in Microsoft Access is very large (one of the largest you will ever see in the industry) and provides functions that cover many subjects, including general topics, algebra, conversion, finance, accounting, date, time, and strings, etc. The available functions are highly reliable so you can safely use them.
Topics: Statistical Functions, Date and Time-Based Functions, The Win32 API.


Domain-Based Functions
A domain-based function is used to get a value from another object and deliver it to the object in which it is being used or called. The general syntax of these functions is:


Getting the name of a chart series name
I have a form containing four check boxes and a chart. I use some VBA code to update the chart based on the check box values. Sometimes one check box is selected, sometimes two and so on. Depending on how many check boxes are selected the colours in my line chart change for each series. I want to keep the colour consistent for each series.
With this in mind, Im trying to add some code that runs through each of the four chart series names and changes the colours to a specified colour but I'm not sure how to refer to the series name in code.
I have some code as follows:...


Showing a selected value from a field
What I need is.

A textbox to show what number the next record in a table is going to be.

I have a column called "Series". This works like cars or a television series. There is series 1, 2, 3 & so on. What I would like a textbox on a form to do is show me the current series and what number the next in series would be


Can i do this with textboxes?
What I need is. A textbox to show what number the next record in a table is going to be. I have a column called "Series". This works like cars or a television series. There is series 1, 2, 3 & so on. What I would like a textbox on a form to do is show me the current series and what number the next in series would be.


MYSQL functions within Access
As we know UUID() and NOW() functions are MySQL functions. I like to set equivalent functions and features in Microsoft Access.

What will be the same functions and where to put?


SQL Aggregate Functions
Using the SQL aggregate functions, you can determine various statistics on sets of values. You can use these functions in a query and aggregate expressions in the SQL property of a QueryDef object or when creating a Recordset object based on an SQL query.


Colors of series in a Chart
I have a chart in one form that has a different number of series depending on which location I choose.

So, for example, if I choose Washington, there would be 5 different series, A,B,C,D,E. If I choose Florida, there would be 3 series, A,B,C.

Is there a way to lock the colors in so that A is always the same color, regardless of how many different series are present for that location? I've tried manually assigning the colors, butthat only works until I switch location-- the next place re-selects its own colors.


Help with a Union Query
I am still working on my home library. I have incorporated quite a few bells and whistles and am learning more every day. My major problem is that every time I add a "KooL" feature I think up several more to add.

Here is the current "shiney":

When reading a series I sometimes don't know/forget which title will come next in the series. Sure, I could pick up several books in the series and spend time flipping through pages but I wanted something a little quicker and easier. So.

I added two columns to my library database: "Series", and "Series Order". I have spent time flipping through pages (hopefully, one time only) and have set up these two columns with the name of the series and the order in which the title will fall in the series.

The query for each series chooses the name of the series and the series order comes with it. All I have to do is sort and, Wham-Bama-Lama, I have what I want. No problem so far.

However, When a short story is part of a series I can't just write a query to include all of the books 'cause the short story will be left out.

So I figured I could write a Union Query that would get what I wanted (The Series is "Jig the Goblin"):


Running Queries with Macros?
I have a basic question:
I'm new to access and I'm just learning about how to create macros.
Can you please tell me if it's possible to run a series of queries by using a Macro that includes these queries in sequence?

It's my understanding that you can run a series of macros in a group and the macro group will run in sequence. Therefore, my question is, can macros be based on queries and if this is possible, is there a limit in how many macros / queries can be ran in sequence?

My purpose is to run a series of queries in the most efficient and fastest manner. They are always ran in sequence and based on the results of each query, but running each individual query manually, is a very time consuming process


Updating a Column Value Based on other Columns in the table
how to update a column based on the values of other columns?
IdNo RevId MinorRevId Status
789 A a ?
789 B b ?
890 0 a ?
890 0 b ?
890 1 b ?

status is the column used to find the latest Revision id, hence the rule is
for each Id,
Check RevId higher value ( Either B from A,B series or 1 from 0,1 series )* value may be in A,B. series or in 0,1. series
Check MinorRevId higher Value ( b from a,b)
and update the status to "Y" for tht record alone.
and for the other records need to updated as "N".
hence the above table will be
IdNo RevId MinorRevId Status
789 A a N
789 B b Y
890 0 a N
890 0 b N
890 1 b Y
For example in case a new entry comes,
IdNo RevId MinorRevId Status
789 A a N
789 B b N
789 B c Y
890 0 a N
890 0 b N
890 1 b Y
the contents should be modified as shown above


Saving animations in ppt slides
I have copied an excel graph in ppt. After creating custom animations by series, I edited some of the excel graph series so they do not animate. In other words, lets say I wanted to animate only 3 out of a group of 6 series of data. Whenever I save the file, it reverts back to animating every series in the graph. I want to save with only the three data fields that I need animated and not all of the series.


MS Access Functions
Here is a list of the most commonly used functions in Access. This is alphabetical listing of pointers to those articles which explain these functions in more detail.


Pivotchart With Series on Different Scale Axes
My client has decided they like the pivotchart format better than the regular chart format, especially for its dynamic quality ( it can expand and contract with form adjustments.

One chart I am having trouble replicating in pivotchart format is one that plots two data series. One series is made of columns representing large numbers, generally several hundred orthousand, and the other data series needs to be a line which usually ranges between 0-10.

Obviously they cannot be represented on a y-axis with the same scale. This is simple to handle in MS Chart.

Is there a way to make a pivotchart with a dual-scaled y-axis, and map a data series to each one?


Function in Report is repeating itself?
I call some functions from a report but this report seems to have an issue; It runs all functions in correct order, twice.

Is there anyone who can tell me why it is doing this and how I can prevent it?

The functions are public and are being called from a common module.


Aggregate Functions in SQL and Microsoft Access
With SQL there are many functions available that allow you to perform or manipulate calculations on your data. These functions can be used as part of the SQL SELECT statement.