MS Access Database: Filter Data for a Report

A common technique that I use to make a more professional looking user interface for filtering data for a report in an MS Access database, is to create a form to ask for criteria for the report. One example would be a report that is used to show data for a particular time period.

Step 1
Create a query to feed data to the report. Add any necessary 
parameters (like the “between [start_date] and [end_date]” style) and test.

Step 2
Create the report, using the query from step 1 as the record 
source. Test the report thoroughly. Obviously, Continue reading “MS Access Database: Filter Data for a Report”

The 5 Most Common Tasks in Understanding an Access Database

One of my tasks as an Access database consultant is to carry out and audit  Access database applications that have been built and re-developed over the years due to:

  1. An individual has accidentally been locked out of a database and can not gain access requiring an official ‘hack’.

  2. The developer (a former employee) has since left the organisation and there are no technical and end-user documents.

    Continue reading “The 5 Most Common Tasks in Understanding an Access Database”

Access 2007: Get up to speed

microsoft access screeshot video

Watch video here

A great overview for new users to Access as wells as for users migrating from the earlier versions of Microsoft Access (2003, XP, 2000, 97!).

This video tutorial makes it look easy to create and manage an Access database because it really is easy to do so!

This quick guide can also apply to the later version of Microsoft Access (2010) though the ‘Office‘ button has now been replaced with a ‘File‘ tab taking you to the ‘Backstage‘ environment.

You will need to get up to speed on how to build more in depth tables, queries, forms and reports as well as understand database terminology and methodologies including how relational databases work (rdbms).

Take a look at my eBook offers.

Creating a Multi-Table Report With Sub-Reports in Access 2007

The Report Wizard in Access 2007 can help you create a single-table or multi-table report. Multi-table reports help you look at individual records or data collections grouped by a logical category. For example, if your database holds information about sporting goods stores, and you want to create sales reports for each store, you could create a multi-table report that shows you the sales volumes for the individual stores. A sub-report might contain information about individual product categories sold within each store.

To create a multi-table report with sub-reports in Access 2007, first you’ll need to Continue reading “Creating a Multi-Table Report With Sub-Reports in Access 2007”

Why Use Microsoft Access Database Forms

Microsoft Access forms allows data to be presented, managed and controlled in a user-friendly environment making your database system more intuitive to use.

Designing Access forms is critical especially for other users who will be responsible for the day-to-day running of a database and who have very little knowledge of the structure of an Access database or any of its background processes.

This type of object is deemed Continue reading “Why Use Microsoft Access Database Forms”

Using Pivot Tables to View Data in Access 2007

A good overview and introduction to using Access database pivot tables. This video tutorial gives you enough to get you going and experiment with the flexible options of a pivot table and is clearly explained – hence the 10 minute video.

The key thing to remember is that you need to think about the grouping and analysis of the data required for this type of report which probably means a query and not a table in which your pivot table will be based.

Using the RDBMS (see my eBook offers for more help) is ever more present and important here too to help collate the correct field arrangement.

Finally, users tend to lean towards using Excel pivot tables (because they are used to it)  but Access is generally much better!

Access Database – Top Value Query

Using Access database queries really control the reporting aspects of your data; asking those questions. It is the heart of any well designed database system.

There are many types of queries and one in particular can serve you well especially if you are wanting to create a league table, prioritise records or suppress the ‘lesser’ recordset.

If you want to create for example a top 10 league table using a Top Value query is the way forward.

Using a simple query and sorting by a key field Continue reading “Access Database – Top Value Query”

Using Access 2010 – 5 Quick Tips

Whether you are a newbie or seasoned Access database user, it’s always useful to know some quick tips to help navigate and control the application’s environment.

Here are 5 quick tips to using Microsoft Access 2010 (some are exclusive to this version!):

1. The Ribbon Bar

This is great for the new user but for those who have migrated like myself became a little disorientated. Two little tips here; Get to know the Ribbon Bar it is reasonably well organised and in most cases logically placed and to help you along the way, you can always download a great little Flash application (from Microsoft) which is free.

The other tip is Continue reading “Using Access 2010 – 5 Quick Tips”

Access 2007 Demo: Use the Expression Builder

An excellent video tutorial on how to use the Access database Expression Builder tool. It clearly explains the features of this powerful and useful tool making good use of the terminology with ‘jargon’ free explanations.

This tool can be used in a query, form, report, macro and to a lesser extent tables too and applies to all versions of Microsoft Access (as far back as you really need to go!).

Here’s a tip to bear in mind (especially when using this tool for a query). Before utilising the expression builder tool in a query, first save the query as it will not show all the fields for an unnamed query object limiting the flexibility of this tool.

In Access 2010, this tool has been updated and has a different look and feel to it but essentially can be used in the same way.

Microsoft Access Database Design – Step 3 of 7

The next step is the Data Design Field for Your Microsoft Access Database.

From your reports, break down into natural units that will be the foundation for your Access database design. In simplified terms, an orders database system might consist of many tables but a core process like an order transaction could be made up from five tables (as described further in this article).

The Data Design (your fields) should start to get a little easier to complete this task providing you have spent enough time on the previous two steps of this series. This step handles Continue reading “Microsoft Access Database Design – Step 3 of 7”