How To Create A Running Totals Query In Microsoft Access

How To Create A Running Totals Query In Microsoft Access

If you wanted to calculate a running sum (or a cumulative total) for a report; this is simply set by using the correct ‘Running Sum‘ property for a selected control like a numeric bound field (text box) and all you need to do is decide whether this will be over a group or the whole report.

However, this doesn’t exist in a query and to mimic the report feature to recreate running totals query in Microsoft Access, you will need to be a little creative!

running totals query in microsoft accessEven though MS Access queries you do have a ‘group by’ and ‘where clause’ features where you can choose one of the aggregate functions, there is Continue reading

Posted in Functions, MS Access, Queries | Tagged , , , , | 2 Comments

Microsoft Access Form Designs: Using VBA With On Timer Event

Microsoft Access Form Designs: Using VBA With On Timer Event

This Access form will self-destruct in 10, 9, 8, 7….

Using the unknown or unused Timer Event for your Microsoft Access form designs can be quite effective and really automate your background processes.

I was recently asked if this application has a timer control which one could illustrate as a count down or trigger code on a set time.

microsoft access form design on timer

You can show a real time count down by using basic controls like a text box or launch another form (or report) or even Continue reading

Posted in Forms, MS Access, VBA | Tagged , , , , | Leave a comment

How To Add A Flash Movie SWF File To A Microsoft Access Form

How To Add A Flash Movie SWF File To A Microsoft Access Form

Working with Microsoft Access form designs requires patience and a plan especially when wanting to work with other form controls over and above the standard tools made available by default.

One particular ActiveX control is the ‘Shockwave Flash Object‘ which will allow you to play flash video (or SWF file formats) in an Access form.

Adding A Flash Movie To A Microsoft Access Form

You will need to first load the library file if not added or enabled in order to render this control ready for use. That means Continue reading

Posted in Forms, MS Access, Utilities, VBA | Tagged , , , , | Leave a comment

Microsoft Access 2007 Onwards – How To Create Access Reports From Scratch

Microsoft Access 2007 Onwards – How To Create Access Reports From Scratch

We all know that information will get out of an Access database is as only good as the data we input. This can also be measured when building Microsoft Access Reports.

Here’s a five minute video tutorial I found that I found that explains the simple process of building an Access 2007 report from scratch...take a look…

Using the pre-built report templates or wizard features are a good start but to really have a handle of good report designs, you need Continue reading

Posted in Microsoft Training, MS Access, Reports | Tagged , , , , | 1 Comment

Microsoft Access Reports – Passing Totals From A Subreport Into The Main Report

Microsoft Access Reports – Passing Totals From A Subreport Into The Main Report

Working with Microsoft Access Reports can be time consuming and fiddly to master and one of the common challenges is correctly writing calculated expressions between main reports and their embedded sub-reports.

If you are not familiar with writing expressions, then I suggest you start with the built in Expression Builder utility which will navigate and build the correct references.

microsoft access reports totals

With the Expression Builder, users can create a hierarchal reference if loaded via the main report design with Continue reading

Posted in Microsoft Training, MS Access, Reports | Tagged , , , , | Leave a comment

Converting Microsoft Access Databases Between Versions (ACCDB/MDB)

Converting Microsoft Access Databases Between Versions (ACCDB/MDB)

With the release of Access 2007, a newer file format (ACCDB) was introduced and suddenly users and developers were confronted with a mix of versions and formats to contend.

Therefore, converting Microsoft Access databases between versions required some careful and additional planning for your database. 

Here’s a five minute video tutorial which demonstrates using Access 2013 where and how to convert between the two common file formats.

Converting Microsoft Access Databases Between Versions (ACCDB/MDB)

From the tutorial, it clearly highlighted the ease of use and users can convert an Access database with the additional ability to just copy individual objects as a quick copy and paste alternative. 

However, there are some additional points to highlight here that will Continue reading

Posted in Database Theory, MS Access, Utilities | Tagged , , , , | Leave a comment

MS Access Database Forms: Using The SelStart And SelLength Properties

MS Access Database Forms: Using The SelStart And SelLength Properties

Have you noticed when you navigate between control fields in your MS Access database forms that it automatically selects the entire content (if not empty) instead of placing the cursor to the end of the last character?

ms access database design form

Using some simple Access VBA code, you can now control the placement for the cursor to mimic and automate placing the insertion point Continue reading

Posted in Forms, MS Access, VBA | Tagged , , , , | Leave a comment

What Types Of Things Can I Do With A Microsoft Access Database?

What Types Of Things Can I Do With A Microsoft Access Database?

Microsoft Access offers a variety of different ways to how a database can be used. There are six general types of applications which a database will naturally fall under. They are:

• Personal or One-to-one use

• Small-business (SME) use

• Departmental use

• Corporation-wide use

• Front-end only use (client/server databases)

• Web use

Of course the above uses can be merged into each other but at the very beginning when planning your Access database, you need to know the scope and general use – hence the above categories.

microsoft access database design processes

At a basic level, you can use Microsoft Access to develop simple, personal database-management systems (which can also be relational – RDBMS). Some users automate everything from their music CD collections to their home finances and is very easy to use with the built in templates and where applicable wizard tools that make MS Access look like a product that anyone can use.

With the prompts for a new database and after Continue reading

Posted in Database Theory, MS Access | Tagged , , , , | Leave a comment

How to Publish An Access Database To SharePoint – MS Access On The Web

How to Publish An Access Database To SharePoint – MS Access On The Web

With the release of Access 2010 and better still, version 2013, publishing your Microsoft Access database on the web couldn’t be easier.

Check out this short three and half minute video tutorial showing how you link and synchronise between SharePoint (online Access Services) and your ACCDB database file within Microsoft Access

How to Publish An Access Database To SharePoint – MS Access On The Web

Just a few points to be aware of when considering Continue reading

Posted in MS Access, Utilities, Web | Tagged , , , , | Leave a comment

SQL Commands: MS Access SQL Versus SQL Server – The Differences

SQL Commands: MS Access SQL Versus SQL Server – The Differences

It’s a common mistake when MS Access developers assume their know SQL commands being common to all platforms. Yes, there is a common standard syntax but at the same time, there are variances between software systems.

It’s like choosing a flavour of ice cream; different tastes but it’s still ice cream and if you mix them, again different tastes but may not always be compatible to the palette!

I’ve seem many questions come up on forums about the differences and conversions between MS Access SQL and T-SQL (SQL Sever) which there are of course some differences between the two language syntax’s.

sql commands ms access sql server

So, here’s a few example SQL commands to help you out with or when needing Continue reading

Posted in Database Theory, MS Access, Queries | Tagged , , , , | 1 Comment