Tag Archives: MS Access Query Basics

Quick way to Import Multiple Excel Files into MS Access

Recently, I had requested a big size of sales/inventory data from one of our internal team. While the data was available at a reasonable level of detail, it was too big to come in one spreadsheet. So, the concerned data operator gave me the data in multiple spreadsheets (MS Excel) with each spreadsheet of significant file size running into 100s of MBs. When I went through the data, I realised I might have to add some helper columns to add some more details – but doing so in each and every spreadsheet was going to be too time consuming. So, I started thinking of importing these multiple files into a single table of MS Access, then run queries as per my needs. Quick way to Import Multiple Excel Files into MS Access

In this post, you’ll learn about this in-built Macro function in MS Access to import data known as “ImportExportSpreadsheet”.

Continue reading

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 2 of 2]

In the previous post, you have learnt how to perform ABC analysis/Pareto analysis using the DSUM function. By now, after reading that post you must have realised that there were no product categories in the previous data – as a result of which all the SKUs (regardless of their product categories) that contributed to top 80% of the sales were classified as A class items. Now, it is quite possible that a particular SKU could have sales not in the top 80% of the overall sales but in the top 80% of the category sales that it belongs to. In order words, you would want each categories to have its own A , B & C Class SKUs. This requirement is a simple extension of the following two posts:

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 2 of 2]

 

Let us see how…

Continue reading

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 1 of 2]

In this post, you will be learning to perform ABC analysis using the DSUM() function. There are instances when a small percentage of causes in your business lead to a large percentage of impacts – meaning that drawing your focus on these small percentages of causes could help you have better control over the large percentage of impacts.

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 1 of 2]

 

 

 

 

 

 

Now, Let us understand how we can use the DSUM() function that we have already learnt to perform ABC/Pareto Analysis….

Continue reading

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