Welcome to Microsoft Access Database Tutorial

Posted on May 10, 2012 by Ben

Friday, May 18, 2012
This website is about learning how to build an Access database using the most effective techniques and tools available with very little or no knowledge and get you up and running without the need to learn all the ‘geek’ speak keeping it Jargon free that most trainers and consultants like to impress you with!

If you are new to Microsoft Access or wish to know what it is, please take a look at
An Introduction To Microsoft Access.

This website contains blog posts, products and general tips to help users find out all about Microsoft Access latest news and articles I feel worth mentioning and recommendations of videos and books on the subject.

microsoft access courses onlineOnline Coaching?


As a Thank You for Visiting
Claim Your Free eBook NOW!

Posted in Microsoft Access | Comments Off

Differences Between MS Access Queries And MS Access Tables

Posted on May 15, 2012 by Ben

MS Access Queries Versus MS Access Tables

An Access database is a collection of various objects which include tables and queries. It’s important to know the distinction between MS Access queries and their close cousin; the table and below I’m going to give you a summary to get you started.

ms access queriesMS Access Tables

To hold and archive real data (or physical data) you use a table in your Access database. You can of course have more than one table which can be joined in a relationship to make you Access database a relational database (RDBMS).

Each field created in your Access table is Continue reading →

Posted in Access Queries, Access Tables, Database Theory, Microsoft Access | Tagged , , , , | 2 Comments

When To Use MS Access And Get Some MS Access Training

Posted on May 10, 2012 by Ben

When To Use MS Access

Creating a MS Access database is not as intuitive as say creating a spreadsheet in Excel but it does provide a richer set of tools and functionality which leads to when to use MS Access as your first choice?

How to use Microsoft Access once it has been planned, designed and implemented makes managing data and reports a breeze! Will this need MS Access training?

when to use ms accessLet’s address when to use MS Access and below are some of the key points to consider the pros and cons when comparing say an Excel spreadsheet with an MS Access database or the larger MS SQL Server database:

Most database applications including Continue reading →

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

Access Foreign Key: Why It Helps With Microsoft Access Relationships

Posted on May 7, 2012 by Ben

Access Foreign Key: Why It Helps With Microsoft Access Relationships

I’m often asked about the Access foreign key and why we should use it in the first place. Well, to start with, take a look at this quick one and half minute video tutorial below to cover one use for it which briefly also explains one reason…

Access Foreign Key: My Additional Comments On Microsoft Access Relationships

In the video it clearly explained that an Access foreign key is the other end of a primary key’s relationship when connecting two tables together (as shown with the ‘Dept’ ID).

This will help with Microsoft Access relationships when connecting two or more tables together for your queries and other output options that uses queries as the database engine (known as JET and ACE) looks to indexed fields first; in other words, primary and foreign keys when connecting and running reports.

This will speed up the output delivery and can make a huge difference with large volumes of data.

access foreign keyOne other added benefit therefore is to set an Access foreign key to fields, which users tend to apply criteria and sorting on a regular basis too as the Access compiler will again look to these fields first when running those queries.

If you want to know more about Microsoft Access databases, then why not take a look at my eBook offer on understanding Microsoft Access relationships which demonstrates more about indexing with sample data to test and evaluate.

Posted in Access Tables, Access Utilities, Database Theory, Microsoft Access | Tagged , , , , | 1 Comment

Access DLookup Function: Why You May Need To Apply The MS Access DLookup

Posted on May 2, 2012 by Ben

Access DLookup Function: Why You May Need To Apply The MS Access DLookup

Using the Access DLookup function may be called upon where users need to output a value from other data sources (i.e. tables or queries) where there is no direct relationship.

Hang on a moment! I though Microsoft Access was a relational database system (RDBMS) so why calculate to connect to a value?

Yes it is an RDBMS but depending on how the database was designed and built or if you had inherited an Access database this could well mean having to handle workarounds and the collection of ‘D’ based Access functions are at your disposal.

access dlookupThe Access DLookup function amongst Continue reading →

Posted in Access Functions, Access Utilities, Microsoft Access | Tagged , , , , | 1 Comment

How To Create An Access Database From Excel

Posted on April 29, 2012 by Ben

How To Create An Access Database From Excel

To create an Access database from an Excel spreadsheet is simple enough to setup and apply. However, there are some preparations involved as data is being migrated from a ‘flat-file’ environment (which is what data in a spreadsheet provides) to a relational database (RDBMS - which is one of Microsoft Access’s strengths).

Take a look at this video tutorial to see some of the final steps in migrating and populating data to create an Access database from an Excel spreadsheet…

How To Create An Access Database From Excel

In this video which by the way is very well explained you need to have an existing database in place which means having relationships created and other objects. In fact, you do not have to have any objects in place as part of the import routines will generate new tables.

Have a plan, sketch out the data sets that will become separate tables in your Access database. Know you primary key fields (fields that will be responsible when joining tables together). Also know the data types as in the video it mentioned the contact number being stored as a number with an input mask. In some cases, it could be a text data type instead.

Creating an Access database will take some time and good planning and getting to know other important objects like queries too. In the video tutorial, it did mention the APPEND query!

create an access databaseTo learn more about Microsoft Access, check out my eBooks on how to create an Access database and in the meantime why not sign up for your
FREE copy of how to import data into Microsoft Access.

Posted in Access Tables, Access Utilities, Microsoft Access, Microsoft Access Training | Tagged , , , , | 1 Comment

Microsoft Access Training: Everyone Needs It! Start Training MS Access Now!

Posted on April 25, 2012 by Ben

Microsoft Access Training: Everyone Needs It! Start Training MS Access Now!

microsoft access trainingIf you have dabbled with Microsoft Access but still struggle, then Microsoft Access training is essential to avoid the pitfalls and master the design, methodologies and functionality of such a powerful application.

Training MS Access has been my privilege and vocation for nearly twenty years and whichever method you use to learn Microsoft Access, there are pros and cons in the way you master the application and the time it can take.

I found quick article below to highlight some of the methods around to consider…

Is Microsoft Access Training Necessary?

There was a time when spreadsheets were sufficient for managing the massive waves of data that information Continue reading →

Posted in Database Theory, Microsoft Access, Microsoft Access Training | Tagged , , , , | Leave a comment

Access Navigation Pane: How To Use And Customise Microsoft Access Navigation Pane View

Posted on April 22, 2012 by Ben

Access Navigation Pane: How Customise Microsoft Access Navigation Pane View

access navigation paneWith the introduction of Access 2007 (and 2010), users will have noted not only the Fluent Ribbon Bar but the new Access Navigation Pane replacing the older database window.

This utility has some nice and simple features to further enhance how you manage your Access objects and in particular, filter and search objects when sharing an Access database.

From the standard object type including tables, queries, forms and reports, users can add as many groups and collect related objects in one view which makes it a very handy tool for collaborating workgroups and roles.

Objects are not copied (duplicated) when being assigned to a group but instead have a shortcut link pointing to the natural storage of the object type.

 

Access Navigation Pane: How To Use And Customise Microsoft Access Navigation Pane View

The following Continue reading →

Posted in Access Utilities, Microsoft Access | Tagged , , , , | 1 Comment

Access Where Clause: The Difference Between Group By and Where in Microsoft Access Queries

Posted on April 19, 2012 by Ben

Access Where Clause: The Difference Between Group By and the Access Where clause

Simple Access queries show detail, line by line records but summarising your data requires the Groups and Totals query. However, how you apply criteria may have an effect if you do not follow the rules. Using the Access ‘Where’ clause can be a better way to apply criteria to your query.

This video tutorial (less than 5 minutes) will explain the use for an Access Where clause query instead of using the ‘Group By’ option…

Access Where Clause: The Difference Between Group By and the Access Where clause

To clarify and confirm, the Access Where clause cannot be seen (or is visible) to the output of an Access query unlike the ‘Group By’ option which affects data distribution.

This is a useful attribute especially when handling complex criteria that sits in the background and is processed first before the output is called which is what the ‘Where’ clause means.access where clauseTo learn more about this type of query and other advanced Access queries, take a look at my eBook offer on More Access Database Queries.

Posted in Access Queries, Microsoft Access | Tagged , , , , | 1 Comment

Microsoft Access Color: How To Pick And Set The Exact Color In An Access Database

Posted on April 15, 2012 by Ben

Microsoft Access Color: How To Pick And Set The Exact Color In An Access Database

First of all, I want to apologise (or apologize!). I’ve used the term Microsoft Access color in my title and not Microsoft Access colour (the correct spelling). This is because our American cousins built or at least assembled Microsoft Access together setting the captions to the local language – so please excuse the misspelt words.

Setting colour in your forms and reports is easy enough to find and apply even with looking at the ‘Custom’ tab when picking a colour by graphically picking from a colour pallete or setting the Red, Green, Blue (RGB) values. However, what if you wanted to pick an exact colour match but do not know the RGB code, then here’s a useful quick way to find the RGB value from a visual screens users wish to capture.

Microsoft Access Color: How To Pick And Set The Exact Color In An Access Database

There are several free downloadable colour utilities and one I use is via this link; Quick HTML Color Picker tool.access colorTo find the exact Access colour required and apply it to your form or report you need to:

  1. Open the downloaded utility which is a pop-up small window.

  2. Navigate to the colour you wish to capture it’s RGB code which can be any Windows based view whether it be a web page, corporate document with branded colours or any application you wish to view.

  3. Click the ‘Pick Color’ button from the pop-up window which changes you cursor to a picker icon and a preview window which follows you mouse movements.

  4. Click on the desired colour and voila! The RGB code is generated back at the pop-up tool.

  5. Now take this colour code and apply it to you designs.

Access colour is really simple and effective. There are no excuses left to developers saying colour shades applied are a near match.

If you need to know how to format forms and reports or any other control and attribute options to these objects, then my eBook offers are at your disposal (for a small fee but guaranteed).

Posted in Access Forms, Access Reports, Access Utilities, Microsoft Access | Tagged , , , , | Leave a comment