Translate this page
Provided By Microsoft® Translator
Ms Access Guru

Access Articles

List of articles about Microsoft Access published in Strategic Finance Magazine in the Technology* section.
* There are also great Excel articles in Technology, written by Bill Jelen, aka "MrExcel"

Quick Jump


Microsoft Access articles INDEX

Most of the articles have an Access database (ACCDB) download so you can try what you read about. Click on the Title to go to the article. Sorted from oldest at the top, to newest at the bottom. Downloads often build on each other even though articles might be independent topics.

 My#   Issue   Article Title        Description 
2016
1 Jan 2016

Creating a Graph

Create a chart with Microsoft Access. To create a query that compares sales for each month for 2 different years, combine the results from two queries that each filter the data by the specified year. The resulting query lines up data for the chart.

Add a chart control to the design view of a form using the wizard. Then modify the chart control properties to get what you want.

2 Feb 2016

Calculating Running Sums

Running sums are easy in Excel, but how do you do them in Access? Create a running sum using the built-in DSum domain aggregate function. For better performance, VBA can be used instead. Both methods are shown.

3 Mar 2016

Chart a Secondary Axis

Charts are a great way to visually show data, but what do you do when the scales of the values are different? Learn how to use a secondary axis, and format charts for a more professional look.

4 Apr 2016

Stacked Column Chart

A stacked column can be used to show how values break down by category. See how to use a crosstab query for the data source of a chart.

5 May 2016

Grouped Report from a Crosstab Query

Use a crosstab query for the source of a grouped report Display up to 10 levels of sorting and grouping on a grouped report, and show subtotals and other calculations on each level. Use the report wizard for the basics then see how to add additional calculations to sum columns of the crosstab.

6 Jun 2016

Control Names in Calculations

Add calculated controls to a grouped report to show percentages related to totals and subtotals. See a logical way to name so that the formulas in control sources are more clear.

7 Jul 2016

Building a Menu

See how to create an unbound form as a menu to navigate and open objects in your database. Add title and buttons. Use the Command Button Wizard to open reports and forms.

8 Aug 2016

Enhancing a Menu

Organize controls on a menu form using a tab control. See how to collect start and end dates and use criteria specified by the user to filter reports. Set a form to automatically open when the database opens.

9 Sep 2016

Use VBA to Modify a Crosstab Query

See how to change macros to VBA code. Construct criteria and modify the SQL statement for a crosstab query so that records are limited before the crosstab occurs. Then use VBA to open the report reflecting the specified criteria.

10 Oct 2016

Creating a Grouped Report

Often times, you will use a query to line up data from more than once source. You can then use a grouped report. Easiest may be to start with the wizard, but the formatting isn't pretty, so change it. Best-fit controls. Hide controls with key fields. Move controls from the report header to the page header. Delete unnecessary label controls and use properties to lines things up.

11 Nov 2016

Refining the Sales Report

Modify report design to make the layout more efficient and easier to read. Concatenate text and sums. Use a formula with IIF to see if an 's' needs to be added to the end of the record count description. Match indents by setting the LEFT property. Make sure there is at least one record after a group heading when pages are rendered. Set colors for sections. Add a command button to the menu form to open a report, and consider criteria the user may have specified.

12 Dec 2016

Customize the Quick Access Toolbar (QAT)

One of the best things you can do to work more efficiently is to customize the Quick Access Toolbar (QAT). Learn useful command to add and how to organize them.

2017
13 Jan 2017

Database Tables

Designing a successful database requires planning. Think about the data you have to put in, and structure it well so that what gets built on top is stable and flexible. How to decide how to organize fields into tables, and then build the tables you need.

14 Feb 2017

Data Types

The building blocks of a database are the fields in each table. An important characteristic of a field is its data type. Learn about the different data types and when to use them.

15 Mar 2017

Numbers

There are many types of numbers - whole numbers, numbers with decimal places, numbers with currency symbols or percentage signs, and data that looks like a number but isn’t to data that looks like text but is a number. Take a deep dive into the number data types you can choose. See ranges and limitations. Consider accuracy and magnitude when you choose data type. Modify basics number formats for your country using the Windows Region Settings.

16 Apr 2017

Formulas

Formulas are commonly referred to as expressions in Access. Learn how to distinguish the different parts of an expression. They can contain functions, operators, references (identifiers), and/or constants. See formula examples with number, date, and string results.

17 May 2017

The Expression Builder

The Expression Builder helps you construct formulas in Access. It can be launched from many places, such in queries and tables for calculated fields, on forms and reports in control sources, default values, validation rules, and more. The Builder Button (...) opens the Expression Builder when it applies. Learn how to make text larger and smaller, build expressions, use built-in functions and see what parameters they need, and lookup information.

18 Jun 2017

Aging Accounts Receivable

When you are tracking and analyzing accounts receivable, see how to dump amounts into categories like 1-30, 31-60, 61-90, 91-120, and 120+ days past due using DateDiff and IIF. Modify format code to show the due amount how you wish or the words "Not Overdue". See how to use the Totals Row that is available for all datasheets if you simply turn it on!

19 Jul 2017

Graphs in Query using Unicode

Even though queries show text, did you realize you can also show bar charts? Use block characters in Unicode to visually display data!
▀▀▀
▀▀▀▀▀▀▀▀▀
▀▀▀▀▀▀

20 Aug 2017

Visualizing Value Changes with Unicode

Continuing on with using Unicode characters to help you visualize your data in a query, which normally can't have graphics, see how to use up and down triangles, and circles, to indicate if a value has gone up, down, or stayed the same. Use a query to compare a value to the value in a previous record for the same field. Enhance performance using a subquery instead of DMax in the SQL statement for the query to get the record to compare to.
▼ ● ▲

21 Sep 2017

Create a Shareable Calendar

Do you have data involving dates? See how easy it is to show your data on a calendar with a free tool you can download. Use a query to line up your data, and then presto! Calendar!

22 Oct 2017

Creating a Combo Box

Combo boxes are a great way to let your users pick something from a list. They can also store one thing, such as a number, and show users friendly text. Learn important combo box properties to know!

23 Nov 2017

Main Forms and Subforms

Learn about using a Mainform + Subforms for Payments in Microsoft Access to find and enter data for customer payments on orders. Synchronize subforms with LinkMasterFields and LinkChildFields. Calculate sum and total number of orders and payments, and the balance due. Display the company logo. Learn how to undo creating a new record, shortcuts for entering data, set the Enter key behavior setting in the Access Options, and anchor controls so they stretch and shrink as the user resizes the window.

24 Dec 2017

Subform to Display Calculations

Use a subform to show the balance for a customer on a form for entering customer payments. Construct the underlying query to do calculations. Add a subform to a main form. Use properties of the subform control to specify how the subform is linked to the main form with Link Master Fields and Link Child Fields). Name is what it’s called. Width and Height specify size. Top and Left specify location. Appearance is controlled by properies such as Border Color, Border Style, Special Effect, and Visible. Locked, Enabled affect whether user can modify and select values.

2018
25 Jan 2018

Using SQL in the Row Source Property

SQL. Get comfortable with SQL -- what it is, and how to write/read it. SQL is Structured Query Language and defines the rules you need to use to get data such as: SELECT myField1, myField2 FROM myTable

Row Source is a property of a combo box or list box that specifies where the rows of data for the list will come from. It is often an SQL statement or a query, and can also be a table.

26 Feb 2018

Change a List Box Using VBA

VBA. Dynamically modify the contents of list boxes on a form by changing SQL for Row Sources using VBA so they change each time a customer changes. If you've never written code before, read this article and get started.

27 Mar 2018

UDF: User-Defined Functions

UDF. User-Defined functions are a great way to make things easier to understand, even with complex logic. If you can create a formula, then you can also write a function using VBA. The functions you write can be used in queries, forms, reports, and called by other code. Learn how.

28 Apr 2018

Use Excel Worksheet Functions in Access

Excel has a lot more built-in functions than Access ... and what Excel has, Access can use! Look at the VBA code behind a form that collects data for Excel's PMT function to calculate the monthly payment for a loan. Learn how to enhance performance and readability using With. Download the PMT Calculator tool.

29 May 2018

Wrapper Functions

Create a wrapper function in VBA to call Excel so that Excel Worksheet functions can be used in calculated fields of a query in Access. Format columns in a query to show values in red, blue, and other colors.

30 Jun 2018

Dates and Times

Dates and Times. Why isn't the base date for Access the same as Excel? See how date and time values really are numbers, and how doing math with them is easy. Data entry tips and shortcuts. Calculated field that creates a date.

31 Jul 2018

Date (+Time) Format

Date (+Time) Formatting can be used to display what you want for a date. Even though dates are stored internally in Access as numbers, format symbols (placeholders) can be used to display dates (and times) the way you want them to look. Along with standard information—such as year, month, day, hour, minute, and second, learn about other information such as quarter and day of year that you can also show.

32 Aug 2018

Windows Region Settings

Windows Region Settings can affect how data is displayed. Note differences between short dates, long dates, currency, and long time values for a few regions. ACCDB download has VBA to read international settings for Windows using Excel since Access doesn't have a direct built-in way..


Goto Top