Expert Access Tips

Within the Microsoft Office Suite there is one package that is used to some extent in most companies but has the least number of so-called 'Experts'. That package is Microsoft Access.

Before I started to teach Microsoft I worked in a company with what I thought was an all seeing all knowing database. I could press various buttons on my screen and it printed off reports from Staff Contact Details to Daily Order Logs. I could add new orders and search for a particular Invoice Number whilst on the phone to my customers. If the report produced did not quite meet with my exact needs I was told and led to believe that as Access was so complicated it was not worth the time to try and change it. This was nothing compared to the cries of horror when I dared to ask for a completely different set of data! That would require a consultant being booked and money being spent!!

If you have not used Access or just want a simple overview of what Access can offer I have written a Spotlight to Access that you should check out. 

I will admit that Access is the biggest and most complex of the Microsoft packages. To create a database from scratch is a process that should not be undertaken without some pretty in-depth knowledge and training. However, if you have the data already in existence within your database you can manipulate it and create your own reports without jeopardising the existing reports.

In order to create or change an object in Access you will need the relevant permissions. This could just be a specific password or it might mean your IT department setting you up with a totally different user ID. This is something that you can find out by asking the person who usually maintains the database.

A quick word of caution!

If your database is already created and up and running then beware of diving straight in and changing existing objects! Unless you are the database designer and/or you know what you are about to do, just be careful of making a design change that has consequences for other objects.

That said please do not let it deter you from creating new queries, forms and reports. If you want to enhance an existing object make a copy of it and test your changes first. I tend to create a copy of the entire database just before I sit down and start making my improvements.

Tables

Tables hold all of your raw data and when viewed on screen seem to be just like a never ending stream of data which could just as easily be in an Excel spreadsheet. While I spend the least amount of time within my tables they are a good overview of your existing data.

Freeze columns

When you are scrolling from left to right within a table you lose the first columns. If you want to keep these in view then highlight the columns you wish to see and choose Format + Freeze Columns. 

Hiding Columns

If you have any columns of data that are not always needed then you can hide them from your view of the table. Choose Format + Unhide Column and then choose the column you wish to view. 

Sub-Datasheets

When you have linked data in tables you can view the related data using sub-datasheets. Open the parent table and click on the Plus icons on the left.

Queries

Sorting in Queries

Your raw data will be sorted by order of the Primary Key field. You can change the sort order to be on any field of your query using the Sort line in Design View.

Filter for Exact Text

If you want to filter for a specific text string, such as UK or Single then type the string into the Criteria line of your query in Design View.

Filter for Today's Date

If you want to filter for Today's Date within a field, type Date() into the Criteria line of your query in Design View.

Forms

Adding Images

You can choose to add images to forms by choosing the Unbound Object Frame tool from the Toolbox. Once you have drawn the frame and chosen the image you can then choose from three Size Properties. I recommend the Zoom as this does not distort your image.

AddingButtons

You can use the Command Button Wizard to add all sorts of buttons to your form, from Next Record and New Record to buttons that open other forms and reports. Choose the Command Button tool from the toolbox and click on your form. Then have a look at the various options available.

Company Colours

The drop down menus for colours on the toolbar only show a set colour palette. If you want to use a different colour for any object on either a form or a report (Company colours are impressive!) then use the Back Colour Property. Click the three dots, choose Define Custom Colours and choose the required colour. You can even type the specific RGB numbers for your company colours.

Reports

Text Grouping

Normally if you group on a text field you tend to group on the full text string, eg. UK, USA, France. However, you can also group on the first few characters, eg. the first letter of a company name for an Alphabetical Phone List. Choose Grouping Options and choose 1st Letter from the Grouping intervals.

Grouping on Dates

If you have a Date field you can group on various intervals including Hour, Month, Year. Choose Grouping Options and choose the relevant option from the Grouping intervals.

Change Grouping & Sorting Options

Once you have created your Report you may change your mind regarding either or both of the Sorting and Grouping options. Click the Sorting and Grouping icon on the tolbar and change the relevant settings.

All in All

Whilst Access is a large and complex package it will serve you well to familiarise yourself with the various areas of the software. If you do not wish to start changing the database yourself at least you will have a clearer appreciation of what Access can produce. This will give you the ability to formulate your change requests rather than handing over vague suggestions. It also gives you the power to say to your designer 'I know it can do it!'

Even if you're already using Access, our Access Introduction course will improve on your existing skills and demonstrate how to get the most from the package. Our Access Advanced course will teach you how to enhance database functionality with advanced queries and macros. If you're unsure of which level of training best suits your needs, contact one of our Advisors, who will be able to point you in the right direction.