As a snapshot and quick peek into my Access database eBook, here’s some more information to help establish your options.
This type of report can be used for printing label address information to fit a pre-defined or custom defined label sheet (normally an adhesive or plain paper sheet). It can be based on a Table, Query or another external data source altogether (i.e. Excel or Outlook).
It can also be used for creating badges, product labels, category name cards and other simple types of data layout reports.
Some users will use Microsoft Word’s Mail Merge feature instead of the basic Access Label report where data from an Access database can support other Office applications.
If you are going to take advantage of the ‘one-stop’ solution, then keeping it all within the Access database environment makes sense and therefore the Label report option will be your way forward.
There are three ways to produce such a report:
1. Using the Label report wizard tool – quick and essay to use.
2. Building a Label report from scratch – requires a plan and time.
3. Using Access VBA code to dynamically generate your Label report – the most effective but some programming is required.
This article covers just the first one as we have already shown you how to build an Access report and therefore you can extrapolate this information should you wish to attempt one.
The third option requires some VBA (Visual Basic for Application) code knowledge and is outside the scope of this guide.
In any case, the first option is the simplest and quickest way to proceed and is highly recommended. You can always then tweak it in design view mode giving you full design control.
Step 1: Make sure you have identified the source for your report; a Table or Query and select the object in the Navigation Pane (but do not open it).
I’m going to locate all my ‘UK’ customers and therefore will use a simple query first which I’ve called qry_Customers_UK.
SQL for my query:
SELECT Customers.[Customer ID], Customers.[Company Name], Customers.[Contact Name], Customers.[Contact Title], Customers.Address, Customers.City, Customers.Region, Customers.[Postal Code], Customers.Country FROM Customers WHERE (((Customers.Country)=”uk”)) ORDER BY Customers.[Company Name];.
On the ribbon bar under the Create tab, click the Labels icon (Reports section) to start the Label wizard.
You will need to know either the stationary code for your labels (or other pre-defined paper size) or customise the dimensions if it is not standard.
I’m going to use Avery – product number: L7668 which has three columns.
Click the Next > button.
Step 2: In the next screen, you set the format for your text appearance:
I have changed the font size to 10 and increased the font weight to normal.
You can change this later in the design view mode.
Click the Next >button.
Step 3: In this step, you set by plotting the fields from the source file and position into the white interactive ‘Prototype label’ based on the dimensions of the selected product sheet.
You can have more than one field on the same line if desired or click in the space below (next invisible row) and then append the next field.
I’m going to add the full address (excluding Country) along with the Company Name and Contact Name.
|Note: The ‘Prototype label’ white box should reflect the dimensions of the selected product number. If you realised that it is not the right size, use the < Back button twice and to re-select again. Click the Next >button.|
Step 4: This screen is very basic and allows you to select one or more fields to sort by if you had neglected to do this in a Query or you have based your source directly on a Table then it will be more useful.
Choose the sort options by selecting a field to append (left pane to the right pane).
I’m not going to sort here (has been dealt with in my query).
Click the Next > button.
Step 5: This is the final step where you name your new report and then either run it or go straight to the design view to modify it further.
I’ve called my report UK Customers Labels and then clicked on the Finish button.
If you close the preview mode, you move into the design time view and here like with any report, change the properties (using the Property Sheet window) and other tools from the ‘Report Design Tools’ tabs on the ribbon bar.
Close and save the report.