Parameters in queries and reports Access 2007

A quick and easy to follow video tutorial example of how to create a parameter query which reduces the number of stored (repetitive) queries in your database.

A parameter prompts you for a value and passes it into your query during the run-time (execution) of the query which makes it a more flexible query as it is now a variable value which is passed and not a fixed value within the design of the query (SQL statement).

This can then serve your forms and reports which in turn triggers the prompt. You can have more than one parameter too to make extra flexible and it applies to all versions of Microsoft Access (not just 2007)!

Multi-Table Queries in Access 2007

Access 2007 enables you to create multi-table queries that can help you make sense of information you have stored in related tables in your database. When you create a multi-table query, you can join together related information that resides in different tables and make queries that produce efficient, logical and useful results. Ultimately, you can use your multi-table queries to create multi-table reports in Access 2007.

To create a multi-table query Continue reading “Multi-Table Queries in Access 2007”

Microsoft Access Database Design Concept – Step 2 of 7

This is the most important step and is the process I refer to as Reverse Engineering!

The best way to build the Access database architecture correctly is start with the end result The Reports! by sketching out what reports you want to end up with. After all, database systems are there to serve and we want information please!

As an exercise, if you were migrating from a paper based system or an older database system Continue reading “Microsoft Access Database Design Concept – Step 2 of 7”

Introducing the Microsoft Access 2010 Hybrid Application

This is a longer than normal video tutorial that I typically post on my blog but this is an important introduction for developers (and end-users too of course!) who are considering building an Access database for deployment over the internet as well as managed via the native application objects of Microsoft Access.

You will need SharePoint Services 2010 or an Access hosting service to handle ‘live’ web application’s objects and with Access 2010, simple tools to help check, manage and publish to the web.

Be patient, stick to the full video demonstration and get a feel for the true power of Microsoft Access 2010.

At the end of the day, you still need to know the objects of ‘native’ Access and the main objects which will mean learning about the Tables, Queries, Forms, Reports and RDBMS (Relational Database Management System).

Perhaps my eBooks would be a good start?

Microsoft Access Database Design Concept – Step 1 of 7

Working with Microsoft Access designing a new database, the question here is what is the core process of the database that you are trying to define? Access Database design is more of an art than a science and common sense will prevail.

By taking the time to identify the core process correctly, you will find that additional processes will plug in easily keeping the structure of the system stable and solid enough as time goes by and data volume increases. If you apply some of Microsoft Access techniques Continue reading “Microsoft Access Database Design Concept – Step 1 of 7”

Access 2007 Tutorial 1.2. Tables and Relationships

Be patient with this video, it’s longer than normal (over 7 minutes) but it covers the essentials when designing and creating a relational (one-to-many) database using two tables.

Keep the rules simple and I have three which I want to share with you:

  1. Make sure the field’s data type is the same size (and where applicable the same size).

  2. Ideally, keep to the same name fields which you are joining – it will help you. Access doesn’t really care!

  3. When creating your join, do this before you add any data in either table especially when setting the ‘Enforce Referential Integrity’ option. Microsoft Access will actually check the data integrity (if any data exists).

Setting the relationship between tables is carried out in one of two places; the Relationship Window (as in the tutorial) and the other in a query itself but doesn’t offer the ‘Enforce Referential Integrity’ option.

There is more to this – it only the start but it is clear to follow and worth a view.

How to ‘ByPass’ Microsoft Access Database Startup

A well-developed Access database will invariably have what is known as ‘Startup‘ switched on and enabled which means that when users start an Access database file it will take you to a starting screen (Access form) normally referred to as the ‘Switchboard’ (Menu).

This is used to add a simple but effective way to take end-users to a starting point and not be confronted with either the Database Window (Access 2003 or earlier) or Navigation Pane (Access 2007 and 2010) and allow access to most if not all objects – causing mayhem!

But if you wish to stop this bypass Continue reading “How to ‘ByPass’ Microsoft Access Database Startup”

Three Myths About Microsoft Access

Check out this article from Christine Harrell who documents well the main myths that I too have had to personally argued with the ‘IT experts’!

Three Myths About Microsoft Access

When a manager tries to get employees into Microsoft database training, many IT administrators will try to talk them out of it. They insist that the company would be better off with a different database system and actively discourage adopting Access.

Microsoft Access isn’t the solution for all database needs, but it is a great choice for many small and even large businesses. Unfortunately, IT people often believe many of the myths about it which makes it hard for managers to implement Microsoft database training.

Myth #1: Access Isn’t a REAL Database Continue reading “Three Myths About Microsoft Access”

Microsoft Access 2010: Outlook-Style Calendar in Browser View

One of my biggest complaints with Access of old was the inflexibility of publishing and multi-user sharing online (via the web). Earlier versions of Microsoft Access used a ‘static’ web view known as Pages and to get to any level of interaction required some programming and scripting skills.

With Access 2010 combined with SharePoint 2010 services this now provides that missing ‘automated’ link. The video demonstrates what can be done using a simple business process of an online calendar system but using your imagination and head for the skies!

This video is only a demonstration and doesn’t show you how to build and implement and Access database over the web – that’s going to take considerably more than seven minutes but at least you now know the power of Microsoft Access (2010).

Please feel free to contact me for more information.

Access Database Keyboard Shortcuts

Being a regular developer and user of Microsoft Access (and other Office applications) for a long time now, I have seen various versions, incarnations and many changes to this powerful desktop application but one aspect to most of the Microsoft Office applications that has not really changed at all (well, in most cases) are the keyboard shortcuts!

Even with the newer ‘Fluent UI’ Ribbon Bar replacing the traditional Toolbars and Menu Bars, the keyboard shortcuts have remained unchanged which meant (for me anyway) no change here then as I’m a fan of the keyboard more so than the little rodent; the mouse!

In general, keyboard shortcuts are quicker than the mouse and I have listed some useful shortcuts that you may want to note and I will let you know if it applies to Microsoft Office and not just Access for good measure. Continue reading “Access Database Keyboard Shortcuts”