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

Business Scenario

Let’s say you have received a set of data in 25 different spreadsheets. Each spreadsheet has say 26 fields. Now, instead of working on each of the 25 sheets individually, you want to import them into a single Access file. This way you would then have to work on just one big set of data.

So, one way of doing that was to import the data using the wizard under the “External Data” tab of the application but soon I realised that it was too slow. Further, I did not have much time or inclination to write a macro code to facilitate this import (major reason – I didn’t know how to!).

So, finally I came across a built-in Macro function in MS access that helped me to import the file. It was significantly faster than the wizard and probably slower than any code – but at least I was not required to know or write any code.

So, lets get started with the steps required to start using this function to import data.

Step 1 – Create Empty Destination Table

Step 1: Let say you have received the following sample data:

Examine the files and list down the columns as well as the data type of the data under each field.

Based on the data provided, list down the columns and the data types separately in a notepad/excel. This is important since you are going to need these column names and datatypes while creating a new empty table in MS Access.

Background work:

Just to be on the safer side, you need to make note of the following things (these points will serve as checkpoints to see whether or not data transfer has taken place correctly):

1. Make a note of the number of records of data in each of the spreadsheet (in this case 25). So, if there are n-records in each file, the final access table should have 25n records.
2. It is advisable to check whether a particular field in each of the 25 sheets holds the data of the same datatype. For e.g.: values in Date_Field in 24 sheets could be of the type date but in the 25th file it might be stored as a text. Such differences can introduce errors while importing the data. You just have to ensure that the data that you are importing is uniform.

Once you have examined and confirmed the file structure, fields and the datatypes of each field, you will now create an empty Access table as follows:

1. You can either create it using a GUI (i.e. create query in query design grid) – but that would be a bit tedious given that you need to create 26 fields.
2. Instead you will create a SQL query as follows using Create “Query Design” function. On clicking Create Query, the query design grid will open. You need to again right click on the open query grid (as shown) and switch to SQL view.

In the SQL view, then type the above code. The SQL windows will now look as follows:

Before hitting the Run Option, make sure you first Save the query. On clicking Run, you’ll find that a new Table has been created.

Before you start the data transfer, do a quick check to see if the final table created is as expected. As can be seen from above, the table has indeed been created as expected. 

Step 2 – Setup Data Import Macro

Now that you have created the empty table, you’ll start the data import process.

Go to Create and Click on “Macro” under “Macros & Code”.

A new screen will pop-up as shown below:

Under the “Design” tab of the new screen, you’ll find that “Action Catalog” is activated by default. In addition to that, you also have to activate “Show All Actions” by clicking on it once.

After doing so, on the right hand side “Action Catalog” window, locate the option “ImportExportSpreadsheet” and double click on it. (Note: If “Show All Actions” is not clicked, you won’t find the option “ImportExportSpreadsheet”.)

Once you can see the “ImportExportSpreadsheet”, you need to double click it. After double clicking it, you’ll see a small pop-up in the new screen that will ask you to provide the following details:

Note that, since you need to import 25 different files into the Access table, you can go about it in the following two ways:

1. As described above, you first need to specify the filename of the first file, save the Macro and then Click Run. Likewise, you need to do for all the 25 files – just edit the filename and click run. In this case, you’ll be clicking RUN option 25 times. But this is not advisable. I’d rather give instructions to import 25 files only once and then have myself click “Run” just once every time I need to import the 25 files. 
2. Under the new Macro Window, you’ll click “ImportExportSpreadsheet” 25 times so that you now have 25 windows open within the Macro Screen. Or Copy the first windows (Ctrl+C) and then (Ctrl+V) 24 times. Here, you can also specify which file should be imported first. In this case, you’ll have to Run the query only once.

On Clicking save, you’ll see that the macro has been created. If you want to edit the macro in future, you can do so by right clicking it and by choosing the design view (just like you do for any query or table).  

Just before you Click Run, you can add a custom message to notify you when the data import is complete. It can be done as follows:

Save the Macro and name it as you like. Then Click on Run and the data transfer/import should begin.

Once the data transfer is complete, the customised message will pop-up indicating that the process is complete.

And once the data transfer is complete, you can go to the table to do a quick sense check.

Alert: Error & Exceptions

Now, here’s a homework for you guys.

While importing the data, it does happen that you might set a field to a particular datatype but the the data that is actually getting imported might be of a different datatype in few rows – either due to some error or an oversight in the data source.

E.g.: Let’s say you have a column “StorageBinNo” which is set as a “Text” datatype. And in few rows of that field there are numbers or dates present in the source data. So, while importing the data, MS Access will try to convert these number and dates and store them as text.

However, in some cases, MS access will not be able to successfully convert data types into a datatype that the field is set to in the table. A quick example would be  trying to import “Text” in a field set as “Date”. The tool will not able to import text and save it as  a date.

In such cases, after the import is complete, Access will create a table titled “Field1$_ImportErrors” and it would specify the Error (i.e. Type Conversion Error), Field name in which the error is encountered and the row number.

In conclusion, be mindful if there are any errors in the data source. These will be handled in the following two ways:

1. Access will indicate errors by listing the errors in an another table. The cell that it shows has errors will contain no data.
2. There will be no visible notification but there can be critical data loss. This is one thing that you need to be extremely mindful of while running data imports.

Given that there are following most commonly datatypes used in MS Access, I would want you to experiment a bit and try to import different datatypes into fields of some other datatype.  So, create a field of “Currency” Datatype and try importing text, long, date etc. and share your observations with everyone here in the comments section.

1. Memo
2. Byte
3. Integer
4. Long
5. Single
6. Double
7. Currency
8. Date/Time

Warning!

Importing data to Access may not be as straight-forward as it looks. On the contrary, it could be risky if you are not careful enough with the data types. Sometimes, you may not even get an error though the data transfer has not occurred as you expected. Keep an open eye for such fields that contain multiple data types.

Summary

In this way you can import multiple excel files into a single Access Table. Please try to run this macro on you own and let our readers know whether you face any problems. Also, keen to know whether this method is helpful or whether you still prefer using the data import wizard.

1. Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 1 of 2] and
2. Using MS Access to create a Running Total or a Cumulative Sum (Grouped Data) – Part 2 of 2

Let us see how…

Real Life Scenario

You have now received category wise SKU sales data and your manager wants you to run an ABC analysis such that each category will have its own A,B & C  class. Just when you thought that you can handle this request, it strikes you that in this particular case, there are categories involved. You realise that the new ABC analysis would require you to tweak the query, but you just cant figure out how?

You simply stare at the data in front of you and keep on thinking…..

Classifying ABC across each category

As we have seen by now, whenever you want to make use of DSUM() function to come up with a running total, you need to prepare the data by first sorting the data in descending order and then adding an extra column of unique values. This column of unique values will act just as a helper column while constructing the DSUM() function. In this case you’ll be using a “Make Table” query.

On clicking the “Run” option, a new table will be created as follows but without a column of unique values:

On saving the above changes, the new table will be created as follows:

Once you have the new table ready in the above format, you need to create a query on this table in order to  calculate the running total:

Step1:

Running total/Cumulative Sum – Here, you will construct a DSUM function with a criteria, as shown below:

Step 2:

Running Total%/Cumulative Sum% – Here, first you will create a helper column containing the total category value across the corresponding category’s record. Then using this helper column and the running total column above, you will calculate Running Total% (or CumPerc) by making use of a simple division”/” operator.

Step 3:

ABC Classification – Here, using a IIF() function you’ll classify each SKU into A/B/C class such that top 80% will be A class, next 15% will be B class and the remaining 5% will be classified C class.

The final query would look like as follows:

On running the overall query, you will get the following result:

Summarising the Results

Now that you have the classification ready, you just need to create another query based on the above query to summarise the results just like you did in the previous post.

On running the query, you’ll get the final summary as follows:

Next Steps:

Please practice this and previous post, create similar queries using your data and share the results with all our readers.

If you are facing any problem understanding this post, it is probably a good idea to go through the following post to refresh the basics:

• DSUM() function –The Art of Writing a Criteria : 1
• DSUM() function –The Art of Writing a Criteria : 2
• DSUM() function –The Art of Writing a Criteria : 3
• Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2
• Using MS Access to create a Running Total or a Cumulative Sum (Grouped Data) – Part 2 of 2
• 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….

Work Scenario

You are working on analysing the inventory of your company. The objective is to identify key SKUS out of the many SKUs that are worth focusing. One efficient way to do this is by running an ABC Analysis. Given the large dataset that needs to be analysed, you wonder how you can accomplish this using MS Access……

What is Pareto Analysis?

Pareto Analysis is an analysis technique based on the idea that only a small percentage of “causes” normally lead to a large percentage of “effects”. It is commonly referred to as a “80-20” rule. It means that in most of the activities, 80% of the effects are caused by only 20% of causes. Note that the number 80-20 is to be taken as a thumb-rule. It is important to understand that the real essence of this technique is to identify those small numbers of factors (not necessarily 20%) that are leading to a bulk of the effects (not necessarily 80%).

Some of the few examples of Pareto Analysis are:

• 20% of the Products accounting for 80% of the company’s sales revenue. So, it makes sense that the management should focus its attention on 20% of these products.
• 15% of the features in a software causing 82% of the bugs. This way the product manager can direct the quality team and the engineers to focus on those 15% of the features.
• 22% of the SKUs in the inventory accounting for 85% of the materials requests from the production department. This information can enable the procurement/inventory department to maintain reasonable level of stock for these 22% of the SKUs.

As you can see from the above examples, Pareto Analysis has its applications in a wide variety of fields.

ABC Analysis – An extension of Pareto Analysis

In inventory management or in SCM in general, ABC Analysis is commonly used to classify or categorize inventory in three different classes – A Class, B Class and C Class. ‘A’ class items are those that account for 80% of the effects, ‘B’ Class are those that account for 15% of the effects whereas ‘C’ Class items account for the bottom 5% of the effects.

Example: A warehouse may classify all its SKUs based on daily average sales such that:

• A Class SKUs – The ones accounting for 80% of the average daily sales will be classified as A Class SKUs i.e. Fast Moving SKUs.
• B Class SKUs – The ones accounting for the next 15% of the average daily sales will be classified as B Class SKUs i.e. Medium Moving SKUs.
• C Class SKUs – The ones accounting for the bottom 5% of the average daily sales will be classified as C Class SKUs i.e. Slow Moving SKUs.

On classifying these SKUs, you’ll often find that out of the total SKUs that are in the warehouse inventory, a very small number of SKUS will be A Class items and yet would be accounting for 80% of the average daily sales. Also, there would be large number of SKUs in the inventory accounting for just 5% of the average daily sales.

So, it naturally makes sense for the management to direct their focus on these small percentage but high impact A Class items and sort of spend less resources on managing the C Class SKUs.

Steps to Run ABC Analysis

When you receive the data, you first need to identify the following two things:

1. The items in the data that you want to categorise as Class A,B or C. The items could be SKUs, products, customers or product features
2. The items in the data on the basis of which you want to classify – these could be sales (categorising SKU or products on the basis of their sales) or purchases requests (categorising the customers on the  basis of the purchase requests they make and so on).

Note that for ABC Analysis, you will be categorising data starting with the highest value down to the lowest value such that top x% of the data will fall in highest category and the bottom x% will fall in the lowest category.

So, once you get the data follow the following Steps:

1. Create a query to arrange the data in descending Order.
2. After this you need to Auto number the data. So, create a table based on the above query using a “Make-Table” query. (Note that we are doing this step to avoid the Running Sum error in case there are duplicate values in column [AverageDailySales]

To know more, please refer the post: Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2

Once you have the new table ready, you’ll now have the data in the following format:

In order to perform ABC Analysis, you need to perform the following steps on the above table:

STEP 1 : Calculate the Cumulative Sum or Running total using the DSUM function (See previous post for Steps)

STEP 2 : Calculate Cumulative Percentage – In this step, you need to determine each record forms what percentage of the total AverageDailySales. So, this step 2 will consists of two sub-steps as follows:

• Step 2a – Calculate the grand total of the field [AverageDailySales] and display that result across each record.
• Step 2b – Across each record, calculate the running total will be what percentage of the total AverageDailySales.

On running the above query:

STEPS3: Classify into A-B-C Classes using IIF() statement – Once you have the cumulative percentages, you would now want to classify the SKUs such that the top 80% (starting from the topmost record) would be A Class, the next 15% (i.e. from 80% to 95%) would be B Class whereas the bottom 5% (i.e. from 80% to 95%) would be C Class items.

In this case, you will make use of an IIF() function. An IIF() function is a simple function. This function tests for a particular condition as provided by the user. If the condition is TRUE, then it provides a value as specified by the user. If the condition is FALSE, even then it provides a value as specified by the user. As you might have noticed, the user needs to specify the “Condition”, and the value to be returned if the condition is TRUE or FALSE. In fact, instead of providing the  value, you can also provide a Nested IIF() function that would RUN, if the values are TRUE or FALSE.

Now using this IIF() function in the query as follows:

On Running this query, you get the final result as follows:

Summarize Results:

The only final thing you would now like to know from the above result is a small table summarising:

1. The number of SKUs that fall within each class (A, B or C) and their contribution (in%) to overall sales.
2. And sales contributed by each category and its percentage to the total sales.

So, in short you are looking at a result something like shown below:

Since, you already know that there were total 195 SKUs under analysis, (X+Y+Z) should be equal to 195 and roughly a small percentage of X should account for a relatively large percentage of M – thereby validating that indeed A class SKUs are those small % of SKUs that account for a relatively high percentage of total sales.

To do this, you would summarise the above query by writing another query.

1. In order to count the SKUs, you could simply select the field that you want to count and in the Total row, select Count OR you could use an expression with function Count ([SKU]). Either ways, you’ll get the same result.
2. In order to calculate the SKU split %, you would want to divide the number of SKUs in each Class by the total SKUs under consideration. To do so, you’ll use the following formula :
   
3. Similarly, you can use the same formula to calculate the total sales across class A/B/C as well as the percentage of sales of each class to the total sales.

The query will look as follows:

On running the query, you’ll get the following result:

In order to add a “Total” row, click the option Totals under Home. You should get the following result:

As you can see, 21.54% of the SKUs account for almost 80% of the sales. More importantly in C Class, there are almost 49% of the total SKUs that account for just 5% of the total sales. You can naturally conclude that it’s better to focus on those small percept of A Class SKUs that account for 80% sales rather than high number of C Class SKUs that account for only 5% of the total sales.

So, in this way you can run Pareto Analysis/ABC Analysis using MS Access.

Next Steps:

It is important to note that the above analysis does not take into account the product category. This means that there is a possibility that there could be some product categories which may not have any SKUs in A Class at all. But there are instances, when you would want to classify the SKUs in such a way that each product category will have its own ABC class. In the next upcoming post, you’ll learn to run such analysis across different product categories i.e. each product category will have its own ABC Class.

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

Work Scenario

You have received a similar set of data that you received in the previous post. The only difference is that along with the list of products and their sales in dollars, you now also have the column category that identifies each product.

The data that you have received is as follows:

Even here, you want to calculate the running total – BUT you want the tool to restart the running total for every category. i.e. you are expecting the result something like shown below:

Calculating Running Total based on Groups /Category

To begin, you’ll follow the same 5 steps that you followed in the previous post (Part 1 0f 2) . The only difference being the way you construct the criteria part of the DSUM () function:

The query will be created as follows:

Important : Please note the use of Multiple Criteria in the DSUM() function above. To learn more about it, please read the post : DSUM() function –The Art of Writing a Criteria : 1

On running the above query, you’ll get the following result.

This way, you can get the desired output. Note that in this example, you have sorted the Amount in Descending Order.

You can similarly create the query by sorting the data in Ascending Order or by just keeping the Amount in as-is order or random order. You will have to make minor changes in the query – to refresh your memory please read the previous post. 

Summary

You have now learnt how to create a running total (cumulative sum) whether you are working on a simple list or on a grouped list where you want MS Access to re-start the running total at every new groups.

Homework

I would like you to practice this technique on a new set of data – preferably Northwind database. Practice this technique by keeping the data in all the three sort order – descending, ascending and as-is order.

Once you are done, share the learnings with all by posting comments.

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

Work Scenario

You have just received a raw data containing a list of products along with its sales volume in dollars. You are in the process of doing different analysis for which you need to create a running total.

There are three different ways you want to create a running total:

1. Create a running total with products in descending order i.e. products with the highest sales volume will come first while the lowest will come last.
2. Create a running total with products in ascending order i.e. products with the lowest sales volume will come first while the highest will come last.
3. Create a running total in the given order of products (any random order).

What is a Running Total??

Running total is the sum of sequence of numbers in such a way that each new sequence of number is added to the sum of the previous sequence. A better way to understand this is by looking at the example below:

“One of the advantages of running total is that it helps you to know the total sales made so far at any point of time i.e. in the given example, since you are creating a running total you’ll know the total sales made so far every time a new entry is made”

In short, every time a new record is added, the sale of the new record is added to the sum of all previous records.

Understanding the Data

The data you have received is as follows:

As you can see, there are total 25 products along with their sales volume in dollars (titled ‘Amount’). The data is in a given random order i.e. the sales volume is neither in ascending order nor in descending order.

Running Total with Product Amount in “Descending Order”

Let us begin with the 1^st point in the scenario.

In order to get the products in descending order, you create a Select query by selecting the two columns (fields) that you need. Then apply a descending sort on the column amount as shown in the figure below:

1. Select the field Product in the query design wizard.
2. Then select the field Amount and change the sort order to descending
3. Add a third field and label it as RunTot. In this field, create a DSUM() expression as follows:

RT: DSUM(“[Amount]”, “[Table]”, “[Amount]>=”&[Amount])

At every record, the DSUM () examines the criteria and returns the sum of all the records that meet the criteria. So, on running the query you get:

However, there is an exception here. The above formula will NOT work when there are two products with the same value i.e. when there are duplicate values.

Let’s say that product 14 and product 15 now have equal values (i.e. duplicate values) as shown below:

On running the query on the table with duplicate values, you’ll get:

As you can see, the DSUM () will return the same result for both the product instead of calculating a cumulative sum or running total. What we really need is that DSUM should add the same amount to the previous total regardless of whether or not they are equal. This is happening because of the way the criteria has been written in the DSUM () function. The criteria make a reference to the same column that contains the duplicate value i.e. in this case it reference to the column [Amount] which also contains duplicate values, thereby returning misleading results.

Resolving the issue of Duplicate Values

In order to avoid this, you need to find a way to add a column that would contain unique numbers. Then, you can create a criteria such that it makes a reference to this column of unique numbers instead of referencing it to the column that contains the duplicate values. 

Now, in MS Access, there is no straight forward & simple way to uniquely number each row in a column. So, there’s a workaround which is explained as below:

1. Create a simple query based on the given data with the field [Amount] in descending order. This is something which you have done earlier also. Save this Query as ‘Descending’ (or whatever name you like).
    
   
2. Now, create a new Table based on the above query as shown.This table will be created using a “Make Table” Query.

   

   

   

3. On Clicking Run, you’ll get :
     
4. Once you create the table, insert a new column titled ‘No’ and its data type as Autonumber. This will create a table with the same fields that you need along with an additional column that uniquely identifies each record.

   

   

5. Once this table is created, then create another query based on this newly created table. The new query should include the following DSUM function.

RT: DSUM (“[Amount]”, “[Table]”, “[No]<=”& [No])

[Note: The column with Autonumber is in ascending order, hence we are using <= operator. If ever, you already have a unique number column that is descending order, then you can use >= operator in the criteria part of the DSUM () function.]

  {For more on DSUM function: Read DSUM () Part 1, Part 2 and Part 3}

On running this query, you get the following desired result :

So, in short, it is always a good practice to add a unique number column while you are creating a running total.

Running Total with Product Amount in “Ascending Order”

Creating a running total with products in ascending order is exactly same as descending order except for the comparison operator sign:

RT: DSUM (“[Amount]”, “[Table]”, “[Amount]<=”& [Amount])

At every record, the DSUM () examines the criteria and returns the sum of all the records that are less than or equal to the amount in current row.

Again, here too the query will return misleading results if there are duplicate values.

Hence, you need to create a new column that uniquely identifies each rows. So, you start by creating queries as mentioned below:

1. Create a simple query based on the given data with the field [Amount] in ascending order now.
2. Now, create a new Table based on the above query as shown. Once you create the table, insert a new column titled ‘No’ and its data type as Autonumber. This will create a table with the same fields that you need along with an additional column that uniquely identifies each record.
3. Once this table is created, then create another query based on this newly created table. The new query should include the following DSUM function.

RT: DSUM (“[Amount]”, “[Table]”, “[No]<=”& [No])

[Note: The column with Autonumber is in ascending order, hence we are using <= operator. If ever, you already have a unique number column that is descending order, then you can use >= operator in the criteria part of the DSUM () function.]

On running this query, you get the following result as expected:

Running Total with Product Amount in “Random Order or As-Is Order”

When it comes to creating a running total with the product amounts in random order or as-is order in which they are received, then you need to have another column with unique numbers either in ascending or descending order.

In this case, now, you need to add a column with unique numbers as done previously. Please check the screenshots below:

In this way, you can create a running total regardless of their sort order or whether they have duplicate values or not.

Summary

In short, in order to create a running total you need to follow the steps given below:

[Note: The use of domain aggregate function DSUM () can make your query run slow depending on the number of records]

Next Steps

In the next post, you will learn how to create a running total when the products are categorized and you need to re-start the running total at each category. You want the result to look like as shown in the table below:

Lets see how to solve this..

Solution

As per the query raised, let’s try to create the two tables – Tbl_Client and Tbl_Activity. The dummy tables are as follows:

As you can see, the first table ‘TblClient’ contains the following fields:

• ID (Primary Key – Uniquely Identifies each customer)
• Full Name – This  holds the full name of the customer.

Whereas the second table ‘TBLActivity’ contains the following fields:

• Activity_ID (Primary Key – Uniquely identifies each transaction with each customer in the table)
• ID – (Foreign key – This field in the table ‘TBLActivity’  uniquely identifies the row of another table ‘TblClient’. It establishes an one-to-many link between the two tables)
• ActivityDate – This field list the date on which a particular activity was done with the client.
• ActivityType – This fields describes the type of activity done with the client.

Now, you want to create a query such that the query should return the last activity done with the clients. Likewise, this needs to be done for all the clients.

This is very similar to the example discussed in the previous post : Select Top ‘n’ Records across each Group

The query for this example can be constructed as follows:

1. In the Outer query, you first select the field [FullName] from table TblClient, then select the fields [ActivityDate] and [ActivityType] from the other table TblActivity.
   Since you want to find the final activity done with the client, you would be interested in knowing the final date on which you last dealt with that client and made an entry in the system. You would then find the activity corresponding to that final date. To do so, you should sort the field [ActivityDate] in Descending Order.
2. In the criteria section of the field [ActivityDate], specify another query – a Sub-query. This query would select the top 1 date for each of the customer. Since we are interested in final/ latest date, you would sort this query in DESCENDING order. You then enclose this query within the In() operator.

The final query is as follows:

On running this query, you get the desired output as follows:

Thus, in this way, you can create a query to extract the last activity done with the client.

Having done this, please share with everyone if you have any alternate way of achieving the same result.

So, lets get started with the actual scenarios……

Real Life Scenario

You have been assigned a new business unit as an inventory manager and you want to know the following:

• The Top 5 products that are below the target inventory level
• Similarly, the Bottom 5 products that are below the target inventory level.
• For all the categories present in your data, find Top 3 products across each of the category.

Select Top ‘n’ or Bottom ‘n’ Products

As an inventory manager, you are trying to understand the current level of your company’ stock. As per your company’s inventory policy, you have assigned a target inventory level across each products. This is the minimum level of stock that each product needs to have at any point of time. If your inventory for a product falls below this level, then you need to place a purchase order with the respective vendor. Besides that, you also have access to the information on the current inventory levels across each products.

So, now you are interested in analysing the difference between the current inventory levels and the target level across each products i.e. you want to analyse the ‘quantity below target level’ for each the products –

‘Qty below target level’ = Target level – Current level.

To begin with, you want to know the top 5 products that have the maximum difference between the target levels and their current levels i.e. the top 5 products with maximum ‘Qty below target level’ 

There is a very easy way to achieve what you have discussed above.

The Northwind database comes with a query ‘Inventory’ on which you just have to create a simple SELECT query as shown in the screenshot below:

On running this query, you get the required output:

Now, let us see how the result changes when you change the sort order :

• You change the sort to Ascending:

  

The above result is nothing but ‘Bottom 5’ products. 

• You do not mention sort at all:

As can be seen from above, the query simply returns the top 5 records in whatever order they are present in the source table.

Select Top ‘n’ Records across each Group

Now, instead of just knowing the Top ‘n’ products or Bottom ‘n’ products, you might want to analyse say Top 3 products across each category.

For example if you have 4 categories, then you will be expecting something like :

Ideally speaking, you would want Access to list all the categories present in the data and for each category list the top 3 values.

This can be achieved by:

1. Creating a Query that would select the Category Name.
2. Then Creating another Query that would return the top 3 values for each row of the query above. To do so, you’ll have to embed or nest the 02^nd query in the 01^st Query. This type of query is called as a ‘Sub-Query‘ where the first query is referred to as an Outer Query while the second one is referred to as an Inner Query.

Normally, an inner query is executed first and the results of the inner query are then used as criteria for the outer query. But in this case, the inner query is expected to run for every new category in the outer query. In other words, the inner query will run repeatedly for every category that might be selected by the outer query. This type of sub-query is nothing but a ‘correlated sub-query’.

The query is constructed as follows:  

As you can see from the screenshot above, the inner query selects the top 3 values of the column [Qty Below Target Level] for every category in the outer query . It does so every time the category in the inner query is equal to the category in the outer query.

Below is the SQL view of the query in the query grid above:

Note that both the first query as well as the second query is referring to the same table ‘Inventory1’. However, in the outer query the table will be used to select the category name along with the quantity field whereas in the inner query the table will be used to select top 3 values for each category. As you can see, in both the queries the tables are going to have different role. Hence, you cannot use the same table name in both the queries. However, you can create an alias of the original table. Creating an alias in the second query allows you to refer the same table but with different name while the first query is still referring to the same table with the original name.

Let’s say for a category = ‘Beverages’ in the outer query, the inner category returns 3 top values of the column [Qty Below Target Level] for the same category. Let’s say those 3 values are 750, 690, 120.

As you might have noticed the inner query is enclosed within the In() operator. So, inner query on returning the 3 values would look like :

In (750,690,120)

This now becomes the criteria for the outer query under the column [Qty Below Target Level]. On evaluating the outer query, you get the top 3 values across the category ‘Beverages’.

Similarly, it repeatedly runs the inner and outer query for each of the categories.  On running the above query, you get the desired output as follows:

In this way, you can select Top or Bottom ‘n’ records across each Category.

Other Points to Remember

1. You need to apply sort on the column that contains the top or bottom values. The sort could be in Ascending or Descending order depending on whether you need top or bottom values.
2. This feature could also be used during testing your queries. In real life,  you’ll normally encounter huge datasets while analysing the data. These datasets will have thousands of rows and multiple columns. So, every time you create a query and try to run it, the resulting dataset will also be huge. So, the whole activity of running the query on such a huge dataset and returning a resulting dataset takes a lot of time.
   Further, it is not as if you are going to write a query in one go. You’ll first write a part of a query, then run to validate the results of the query. Then again go back to add another criteria to the existing query or maybe add another calculated field, then again run this query to confirm whether or not you are getting the expected results. In order to do all these iterations, you’ll be running the query multiple times. Given the huge dataset, this activity is then going to consume a lot of time.  However, using the feature discussed in this post can save you a lot of time. Once you are satisfied that the query is returning the expected result, you can then remove the Top clause.
3. There is an alternate way to enter the TOP ‘n’ values. As can be seen from the screenshot below, the top 5 or 3 values could be entered in the property sheet under the General Tab. The property panel is activated by right clicking and clicking on ‘Properties’ option.

Homework

You can further practise this feature by extracting TOP % or Bottom % of the records. Everything remains the same – you just need to enter the % values instead of integer values.

So, how about you create a query to get the expected result for the following scenario:

• Top products that accounted for 75% of total sales.
• Bottom products that accounted for 20% of the total sales

Once you are done, you can share your observations with all the readers on this blog. Good luck.

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

Work Scenario

You are analysing the inventory data of your company and you want to know the following details:

• Scenario 1 : The total inventory sold on a particular date (say 03/22/2006)
• Scenario 2 : The total inventory sold across different days.

You want to solve the above two scenarios using DSUM() function. Now, that leaves you thinking..

DSUM function with Date Criteria

Since you are working on inventory, you’ll access the Inventory Transactions table from the Northwind database.

In this table, you would require the following fields:  

• Transaction Date – This field indicates the date on which the inventory transaction took place.
• Quantity – This field indicates the total quantity of inventory being handled.
• Transaction Type – This field indicates whether the inventory was sold, purchased or whether it was on-hold.

Before you start creating any query, it is always a good practice to go through the table and its fields that you’ll be analysing and have a look at the type of the data that they are holding.

In the above mentioned table “Inventory Transaction”, though the [Transaction Modified Date] appears to be of the format mm/dd/yyyy, it is actually of the format mm/dd/yyyy hh:mm:ss AM/PM.

Now, since you want to know the total inventory sold on a particular date, the time component is irrelevant to your analysis. So, you will have to disregard the time  by using the following functions:

• format(“[]”, “mm/dd/yyyy”). But the output will be a a string of the format “mm/dd/yyyy” and you need a date format.
• Hence you’ll further use the cdate function over the format function: 
  cdate( format(“[]”, “mm/dd/yyyy”))

So, lets create a new query, to get the desired result as explained above:

On running this query, you get the following output:

Now that you have the dataset in the desired format, you can start working on the scenarios:

Scenario 1

In this scenario, you know outright that you need the result for one specific date only.

1. So, from the from the given dataset, you might want to filter the inventory transactions of that particular date. (You might see the benefit of this step if you are dealing with thousands of records  of inventory transactions across hundreds of different dates.)
2. Then you group all the transactions on the required date (03/22/2006 in this case) and apply a dsum() function to sum the field [Quantity] based on the criteria of date as well as transaction type (i.e. inventory that was sold).

The criteria for DSUM() function will be written as follows:

The query looks like as follows:

And the output is :

Other possible conditions for dates are as follows:

Scenario 2

In order to find the total quantities across all the dates, you’ll provide a direct reference to the field [Mdate] containing the date values while constructing the DSUM() function. The criteria in the dsum() function will be written as follows:

You’ll construct the query exactly the same way you did in scenario 1 except that in this scenario you would be referencing to the field instead of specifying a direct value:

The query for scenario 2 would look like :

And on running this query, the output is :

In this way, you can get the desired result as required in scenario 2.

Summary

So, you  have seen how a DSUM() function can be created and more importantly how a criteria can be constructed based on the following three data types:

1. Text  – DSUM() function –The Art of Writing a Criteria : 1
2. Number – DSUM() function –The Art of Writing a Criteria : 2
3. Date – DSUM() function –The Art of Writing a Criteria : 3

Just to refresh your memory, the criteria for all the three data types can be constructed as follows:

Also, in this post you have learnt the following two functions:

1. Format () – This function converts values in specified string format
2. Cdate() – This function converts values into dates provided the value passed as an argument is a valid date expression.

It is recommended that you read, practice and use these functions and share with us any interesting results.

[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]

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

Work Scenario

You and your team want to know the total invoice value of all the orders placed. Its a pretty straight forward requirement but you want to use DSUM() function to achieve this.

DSUM function with Number Criteria

In order to run to get a result as required in the work scenario, you’ll be using the table “Order details” and the following field: Order Id, Unit Price, Quantity.

As a recap, the formula for DSUM function is as follows:

Also, by now you might be knowing that the criteria can be specified in two ways:

• Provide a direct value:

Lets say you want to find the total invoice value of an Order ID = 40. To do so, you’ll create the criteria by directly specifying the number as follows:

“[Order ID]=40”

Notice that when you want to specify a number criteria there are no delimiters required. You can simply specify the number directly after the = sign.

On constructing and running the query as explained above, you’ll get the following output:

And the output is:

• Provide a reference to a field 

You might want a similar output as shown above but you would need it for all the orders in the system. In such a scenario, you will have to construct the criteria by providing a reference to the field holding the order numbers. It will be constructed as follows:

“[Order ID]=”&[Order ID]

Note that the Order ID is the field that is holding value of data type – number.
The query would look like as follows:

On running this query, you’ll get the following result:

Summary

In this post, you have seen how to create a DSUM function when the criteria that you are passing is of a number data type.

In the next post, you’ll learn to pass a criteria of data type – Date

• DSUM() function –The Art of Writing a Criteria : 3
• 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……

What is DSUM() and what does it do?

DSUM() function as the name suggests is a function which helps you to sum the field (i.e. a column) specified in a particular table based on a certain criteria, if required.

The function is as follows :

As can be seen from above, there are two ways in which you can write a DSUM() function:

1. Construct a DSUM() function with no Criteria
2. Construct a DSUM() function with a Criteria

Lets look at each of the two ways of working with DSUM() function.

DSUM() with No Criteria

In order to understand how a DSUM() function behaves when no criteria is specified, let us have a quick look at the following example :

Lets say, you want to sum the [Quantity] field in the table ‘Order details’ to see the total units ordered.

On running this query, you’ll get the following result:

DSUM() With Criteria

In order to understand how the DSUM() function behaves when a criteria is specified, let us first analyse the criteria part of the DSUM() function.

In the above snapshot, in place of = sign, you can also use other comparison operators like <,>,<= or >=.

Further, there are three types of criteria that can come after these comparison operators. Those are :

1. Text 
2. Number
3. Date

Remember : The text/number/date can either be specified directly or it can be specified by referencing it to a column containing the required  text/number/date  values. This will get clearer as we go through more examples below.

DSUM() with Text Criteria

Let’s consider a scenario where you want to determine the total shipping fee paid across all the cities to which you have shipped your products. In order to do so, you’ll refer the table ‘Orders’ which holds all the related details of the shipped orders. The details you would need are:

• Field to be summed : [Shipping Fee]
• Table : Orders
• Other fields required : [Ship City];

You are somewhat expecting a similar output as follows:

The query will be constructed as follows:

As can be seen from the screenshot above:

1. You drag the field [Ship City] in the query grid.
2. Then, to begin with, just specify the DSUM() function without the criteria. On running this query, you’ll get the following. (Don’t forget to click the Totals button)
                                                  

   The total sum of the entire field [Shipping Fee] will come across each of the cities. BUT, this is not what you want. You want the total shipping fee paid across EACH city.

3. So now, you introduce a criteria in the DSUM() function by referencing directly to the field [Shipping City]. By referencing directly to the field, you are telling DSUM that for say a city A sum up the shipping fee corresponding to that city A only. Similarly, do the same for all the other cities present in the data. (Note the field [Shipping city] that you are referencing to contains Text values. The way you construct the criteria depends on the type of the data held in the criteria field)

The criteria argument is written as follows:
(text highlighted in red is the part where you will specify the criteria)

This means that the entire criteria argument will be passed as string to the MS Access data engine. Because criteria value is going to be of text data type, it will be included within single quotes‘’ as follows:

Now, pay attention now – Here comes the puzzling part.

Before you understand how to construct a criteria while referencing directly to a field, let us see how we construct it when we directly provide a text value.

1. Provide a direct value :
   If the criteriavalue that you are going to specify is a direct text like CityA, then you would directly specify that text within the single quotes as:
                                                        “Cfield =‘CityA’”
   It would mean that DSUM would perform addition of the field shipping fee for the city ‘CityA’ only. In our example if we specify the city ‘Miami’, then we would get the same total across all cities as follows:
                                      
   
2. Provide a reference to a field :
   We want DSUM() to calculate and display the sum corresponding to respective cities. So,we would reference directly to the field as discussed at the beginning of point 3 before.

The criteriavalue itself is going to be a text, hence we would require a string within a string. In other words, the criteria string “Cfield =criteriavalue”will further include another string. That another string would be concatenated as
“&[Ship City]&”

So, you’’ll create the query as follows:

On running the above query, the result is as follows:

Note: Different data types will require different delimiters. Delimiters tell Access that the value that it is going to encounter next is a text value or a date value or a numerical value. So, for text value the delimiter is quotes, for date value the delimiter is # whereas for numerical value there is no delimiter required.

Multiple Criteria

You might have noticed that in the query constructed above, there was only one criteria – City :

The multiple criteria can be constructed as follows:

On creating this query in the query grid and after running it, you’ll get the following output :

Summary

So, in this way as explained above, you can create a DSUM function by specifying the criteria as per your need.

A summary of different criteria types is as follows:

I guess you peruse and practice this concept before you move on to the other criterias (like number and date). Please go through this post carefully, try out different examples and share some interesting results/comments with everyone here.

Next Steps : DSUM() with Other Criteria

In the next post, you’ll learn to create a DSUM() with other criterias such as number and text.

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