Using MS Access to create a Running Total or a Cumulative Sum (Grouped Data) – Part 2 of 2

As you have seen in the previous post, you can easily create a running total on a given set of data. A slight modification can be added to this by making MS Access re-start the running total at certain point as decided by you.

Using MS Access to create a Running Total or a Cumulative Sum (Grouped Data) – Part 2 of 2

In this post, you’ll learn how to do so….

Continue reading

Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

While analysing data, there are many instances when you need to create a running total (also known as a ‘Cumulative Sum’). In this post, you will learn to create a running total using MS Access Query. Actually, if you know how a DSUM() function works, then creating a running total is very easy.

Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

Let’s see how we can use this MS Access function to create a running total using just a query design grid.

Continue reading

ReaderQuery : How to extract the last activity done with the client ?

In this post, you’ll learn to solve a specific query raised by one of our reader. The query is as follows:

ReaderQuery : How to extract the last activity done with the client ?

Lets see how to solve this..

Continue reading

How to Select Top ‘n’ or Bottom ‘n’ records from a dataset?

In this post, you will learn how to create a query to extract Top ‘n’ or Bottom ‘n’ records from a particular dataset. There are  instances when you’ll need to extract top or bottom ‘n’ records within a particular group.  In order to do so, MS Access provides a nifty feature  ‘Return’ – Select Top 'n' or Bottom 'n' records from a dataset in MS Access that is used to extract the required Top or Bottom values. This feature is available under the Query setup in the Design Tab of the ribbon.

Select Top 'n' or Bottom 'n' records from a dataset in MS Access

So, lets get started with the actual scenarios……

Continue reading

DSUM() function –The Art of Writing a Criteria : 3

In this post, you’ll learn to work with DSUM() function with date as a criteria. It is very similar to the way you create the text criteria with the only exception being the delimiters used. In case of dates, you’ll use hash (#) as a delimiter instead of a single quote (‘ ‘) that you use in case of text values.

DSUM() function –The Art of Writing a Criteria : 3

The date criteria enables you to extract data within a particular time period or before/after a certain date as explained in this post…

Continue reading

DSUM() function –The Art of Writing a Criteria : 2

In the previous post, you have learnt how DSUM() function works as well as how to specify a text criteria. As mentioned earlier, the criteria could be a text criteria or a number or a date. Depending on the data type of the criteria, there is a slight change in how you construct the criteria part of the DSUM() function in terms of the delimiters to be used as well as the placement of the criteria value.

[Note: If you are new to DSUM() function, it is recommended that you read this post first : DSUM() function – The Art of Writing a Criteria : 1]

DSUM() function –The Art of Writing a Criteria : 2

In this post, you are going to learn more about specifying the number criteria. Let us see how…

Continue reading

DSUM() function –The Art of Writing a Criteria : 1

As you learn more in MS Access, you will find that DSUM() function or for that matter any other domain aggregate functions such as DAvg(), DCount() etc. have interesting applications in data analysis. Now, these functions are very easy to understand in terms of what they do but the only confusing part is the criteria part of the function. I have seen many people including myself getting a bit uncomfortable and making silly mistakes while constructing the criteria in this function.

DSUM() function –The Art of Writing a Criteria : 1

In this post, you will learn what exactly does DSUM() function do and more importantly how to construct the criteria properly. So, lets begin……

Continue reading