How Building An Access Database To Your Own Specification Will Help

How Building An Access Database To Your Own Specification Giving You Total Control

One of my main ‘day’ jobs includes building an Access database and this would typically cover a wide range of tasks including an add-on report to an existing system, adding a new form interface making it more user-friendly, repairing errors and creating a new database from the ground up.

When applying to tender for a database build, often clients like to see my portfolio work as a way to measure my credentials as an Access database developer. Since most of my clients have sensitive data and processes, I just could Continue reading “How Building An Access Database To Your Own Specification Will Help”

Distinct Queries? How To Create An Access Database Query And Make It Unique

Distinct Queries? How To Create An Access Database Query And Make It Unique

Microsoft Office Professional ships with the MS Access database application that lets you analyse and manage large amounts of data better than say an Excel spreadsheet. Sometimes, the information stored in various tables becomes fragmented causing duplication and at some point will need to be cleaned up.

This is where distinct queries can help. Since a query asks questions to the database tables, it can also control how the information will be displayed in the output and that also includes the removing (or at least supressing) of duplicate values.

Distinct Queries? How To Create An Access Database Query And Make It Unique

There are two types of distinct queries. The first one is simply called Distinct and will look for unique values within the fields chosen for your query output and ignore all other fields from one or more tables. The other query type is known as DistinctRow and will include all the fields no matter if they are added in the output for your recordset.

The SQL code of the two Continue reading “Distinct Queries? How To Create An Access Database Query And Make It Unique”

Modifying MS Access Page Setup

Modifying MS Access Page Setup – Quick Video Demonstration

Take a look at this quick (87 seconds) video on how to modify the layout of a table using the Access page setup before you print. This can be applied to other objects in the database too including queries, forms and reports.

httpvh://www.youtube.com/watch?v=85fWTPxU84c

Modifying MS Access Page Setup – Applies To The Main Objects

In some cases you may want to print just the table and since this object wasn’t designed to pretty or functional when printing, there is a strong case to edit the page layout before printing. This also applies for a query since it has the same configuration; look and feel as  an Access database table.

You may want to take a closer look at the Access page setup options and will vary based on which object you are investigating. The best to locate these options will be load the object in normal view (or layout view) and from the Office button (2007) or the File tab to the backstage (2010) choose the ‘Print’ command and then ‘Print Preview’. You will see a Ribbon bar for all your page setup choices.

access page setupTo learn more about printing, designing and other aspects to Microsoft Access, take a look at my eBook offers which covers the main objects.

How To Open Access In Full Screen: Opening A Form As Maximized

How To Open Access In Full Screen: Opening A Form As Maximized

open access in full screenOnce users get a grip on form design techniques in Microsoft Access database, they normally want to jump ahead and customize forms in their applications that appear polished and professional. One technique they can use is to open Access in full screen view which hides other no essential screens keeping your database neat and tidy.

It requires a combination of setting various form properties during the design time mode and optionally (but ideally) applying either macros or VBA code to the form’s module too.

The final touches to this process would be to create a desktop icon shortcut to load the database file and run Continue reading “How To Open Access In Full Screen: Opening A Form As Maximized”

Differences Between MS Access Queries And Tables

MS Access Queries Versus 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 database. You can of course have more than one table which can be joined in a relationship to make your database application a relational system (using RDBMS).

Each field created in your table is Continue reading “Differences Between MS Access Queries And Tables”

When To Use MS Access And Get Some MS Access Training

When To Use MS Access

Creating your 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’s desktop database application once it has been planned, designed and implemented makes managing data and reports a breeze! Will this require some 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 a database system or the larger MS SQL Server database:

Most database applications including Continue reading “When To Use MS Access And Get Some MS Access Training”

Access Foreign Key: Why It Helps With Microsoft Access Relationships

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…

httpvh://www.youtube.com/watch?v=WqxzwF9GcQg

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.

Access DLookup Function: Why You May Need To Apply This Function

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

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 “Access DLookup Function: Why You May Need To Apply This Function”