Category Archives: Access

Access, Graphics

Stacked area graph for cash profile

Here we have data about cash level in a bank account. Then we have defined high and low limit for good liquidity. The value of cash account should not fall below low limit and should not exceed high limit.

Just note, as we use stacked areas, the upper limit will be 100 + 500, not 500. If you would like the high limit to be 500, change the cell C2 to 400. Our low limit is 100 and high limit is 600.

We need to have a graph to show if our cash balance is in between the limits. Then we can visually follow the cash balance and limits.

Let’s start creating the graph.

Activate the area, select insert – chart, choose combo.

For the low limit, select stacked area. That is the area chart in the middle.

Select also the chart type for high limit for stacked area.

The graph looks like this. It is not yet presentable. Check that both limits have chart type stacked area.

Activate the lower area, press right mouse button and press fill.

Select no fill, as we want the area to be transparent. Only the area between the limits is colored.

This looks already better, but it needs still some finetuning. The background is too dark.

Press right mouse button, select fill and choose a lighter color.

For me, a light background color looks better.

We could still make background color less striking.

Press right mouse button on top of the background, press fill, select gradient and more gradients.

I set transparency for 80 %.

Now the graph looks even better.

I removed the labels for high and low limits.

We can see visually that cash dropped twice during the period considered below the low limit. The cash line is not in the light-yellow area. Both times cash was just below the limit. 

Now we are ready for the management meeting to present cash profile.

Access, Excel

Unique values, comparing two data ranges

You have two data ranges, D1 and D2, which are nearly identical. How to check what are the differences between the two ranges.

The first time I had this issue, I used COUNTIF function. Range and criteria are the arguments for COUNTIF. How many times criteria exists in the range.

In G3 I have COUNTIF, which calculates how many times does “8287” exist in D1 data range. If the value is zero, then the value is not found in D1. If the value is one, then the value exists once in D1.

In the same way, I have COUNTIF in B column. How many times does a value in D1 data. The sentence in B3 is =COUNTIF($F$3:$F$34;C3).

I added the header for the B column and filtered the values with zero COUNTIF. Values 1290 and 7008 exist in D1 range but not in D2.

Values 9002 and 3812 exist in data range D2 but not in D1.

We had some manual steps to find out differences between data ranges. One way to automate the process, is to use FILTER function.

FILTER is doing the same thing as I did with manual filtering. We take the values from C3:C34 where B3:B34 is zero and from F3:F34 where G3:G34 is zero.

We can have COUNTIF as embedded function inside the FILTER. COUNTIF function can be used with conditional formatting.

Select the first data range and take home – conditional formatting – manage rules.

Rule type is “Use a formula to determine which cells to format”.

Write the rule:

=countif($F$3:$F$34;C3)=0

Note that Excel defaults absolute reference, but criteria needs to be relative reference C3.

The results.

Repeat the process for the D2 data range, activate F3:F34.

Now the rule is =countif($C$3:$C$34;F3)=0 .

Highlighted values are not existing in another data ranges.

If you have the data in Access, let us use the following SQL query:

We select D1 values with left join, or outer join, to D2 with condition that D2 is empty. So, we join D1 to D2 but only when D2 is empty. Then we get values which are only in D1 but not in D2.

To have the other way, the values which exist only in D2 but not in D1, we need to have a mirror from previous query.

We are selecting D2 values from D2. We join D2 to D1 but the condition is that we want only D2 values when D1 is NULL.

I am sure that there are other ways to compare two data ranges to check which values exist only in one data range but not in another one. For me most practical way is to use conditional formatting. That does not populate any extra cells, only unique values are highlighted.

Access

Reporting with Access

In my previous blog, I presented how to get data below in a reportable format in Excel. The same can be done with Access.

year;month;account;amount

2025;1;1000;167

2025;1;1010;640

2025;1;2000;15

2025;1;2000;965

2025;1;2010;278

2025;2;2010;738

2025;2;2010;973

2025;2;2010;108

2025;2;1000;947

Select new data source – from file – text file.

Browse the file. I have not created the table, so I selected the first radio button.

I selected the delimited option, as the data is separated by semicolon.

The first row is header row and the parameter is selected.

Field options are left untouched.

Access is creating a primary key field with running number.

Give the name of the new table.

Before you press finish, you can check advanced…

All the fields and data types can be reviewed. All the fields have long integer data type.

The data is neatly in Access table. Field names are taken from the file.

A simple query once data is in place.

The sum is the same as calculated with Excel.

Sales are queried by year and month.

In my previous blog I demonstrated data – text to columns and TEXTSPLIT functionalities in Excel to see the data in readable format. However, Access is suitable also for reporting purposes. Flat file was in this case easy to import into Access. I did not create the table, but the table was created while importing the data. In my test data I did not have decimals. Sometimes decimals have caused issues for me.

Access

Pivot with database

Here is a simple data. The columns are product, region and sales.

To count how many products were sold per region, we need a pivot report.

This was an easy task to create pivot table in Excel. Region was selected as columns and product as rows. Sales is a numeric value per region and product combinations. Now we can see how many of each product was sold in each region. Pivot gives us a big picture about the data. Our sample is very limited that we could see how pivot is structured.

We have the same data in Access.

This is our table in design view, we have product and region as text columns and sales as number column.

In validation rule I have set allowed values, validation rule was entered also for products.

Select create – query wizard.

Select crosstab query wizard. This is a list of standard queries.

Select the table where the data is taken.

Row heading is selected.

Column header is selected.

The value context is the sum of sales. In this screen you can select if the sums are included in the report.

Name your query.

The results are the same as in Excel.

If you have data in Excel, you don’t have to move that to Access to have a pivot report. However, if you have the data in Access, you don’t have to move the data into Excel to have a pivot. Access includes also pivot functionality. The Pivot is called a crosstab in Access.

To test the pivot also in MySql, I created a similar table than in Access. Id is a primary key with automatic numbering. Product and region fields have checks that only allowed values are entered. This is validation rule in Access.

Then I populated the table with values.

The Pivot is reported with this SQL query:

In this query you first define the header for rows, we have the pivot with products in rows. Aliases No and So will be column headers. We are counting the sum for products in rows and regions in columns. If the region is North, then the sum for sales fields is counted. That number is presented under No column per each product. Product is not in aggregated functions, therefore we have GROUP BY product sentence.

For some reason, the SQL query does not work with Access, but it works with SQLite.  

Pivot with SQL is not so practical as with Excel. If you don’t want to transfer the data from SQL into Excel, you may have pivot in SQL editor, too. If you once download huge dataload from a system into Access, for example, might crosstab query be useful. Access is a useful tool also for pivot reporting.