Using Access Queries to Produce Summary Data

In this post we shall be looking at how to use Access Queries to produce summary data on groups of records which share something in common.  The exercise which we are going to use to illustrate this involves working on a table of Order Details.  The query we are going to create will group together all the individual records which share a common value in the OrderId field, and then produce a SUM of the Amount's field for each group.  What we are effectively doing here is sorting all the records into groups of Orders and then producing a Total Amount for each individual order.  This is the table data we are going to be working with:

Figure 1: The Order Details Table.

To do this exercise we are going to use GROUP BY and SUM on the TOTALS row of the QUERY DESIGN GRID.


Summary Query Data Exercise


Before creating the Query, you will first need to create the table or download the Summary_Data.accdb database.  This consists of one Order Details table with three fields - OrderId (Number), ProductId (Number), and Amount (Currency).  The primary key is a composite of OrderId and ProductId.  If this was a full Access Database Application the tblOrderDetails would be a junction table between an Orders table and a Products table. However, for our purposes of this exercise we only need to use the Order Details table.  Once this is in place you can begin the exercise.

1. Click the QUERY DESIGN icon.  This is located in the OTHER group of the CREATE RIBBON.
2. Add tblOrderDetails to the Query Design Window from the SHOW TABLE dialogue box.
3. Add the OrderId and Amount fields to the Query Design Grid.  The quickest way to do this is to double click the two field names from the Table Diagram in the top section of the Query Design Window.
4. We now need to show the TOTAL's row in the Query Design Grid.  This is not shown by default so click the TOTAL's icon in the SHOW/HIDE group of the DESIGN ribbon.  The TOTAL's row should now appear in the grid.
   
   
5. We want our query to group all the records into individual orders, and calculate the total amount for each one.  This is where the TOTALS row comes in.  We are going to enter the GROUP BY clause in the TOTAL's row of the OrderId column.  (NB This is actually the default setting, so you should not need to change it). All records sharing the same OrderId will now become a seperate and distinct group (ie row) in our query results. Then move over to the TOTAL's row of the Amount column.  We are going to enter the SUM function into this cell.  You can type this in directly or select it from the drop down list.  This will give us the Sum of Amount's for each individual group in the query results.
   

Figure 2: GROUP BY and SUM 
entered into the TOTAL  row.

We can now run our query by clicking the RUN query or DATASHEET icon. Figure 3 below shows what our query results should look like:

Figure 3: Query results showing a summary
of our original data.

As you can see, our original records have been grouped into individual rows by their OrderId, and a sum of amount has been produced for each group/order.

Importing and Exporting Data between Access and Excel

When you work with any sort of external data in relation to Microsoft Access, you have three options.  You can Import, Export or Link.  The difference between Import and Export is simple: you are either importing  data into Access, or exporting data out of Access.  The data you are working with, in this case, is a snapshot. In the case of Importing, once data has entered your Access application from an external data source, any changes made to the original data source, are not reflected in your imported data.  The same principle applies when you export data to an external application - any changes you subsequently make in Access is not reflected in your exported data.  This is in contrast to the option we covered in the last blog post on Linking Access to an External Data Source, where the connection made is 'live'.  Any changes made in Access is reflected in the external application , and any changes made in the external application is reflected in Access.

In this blog post we are specifically going to be looking at Exporting and Importing snapshot data to and from Microsoft Excel.  NB you can also partially link an Access Database to an Excel spreadsheet, but the connection in this case in not completely live - you cannot change data from within the Access Database (which is then reflected in Excel), although data changed within Excel is reflected within Access - in other words, the connection is read-only.

Exporting Access Data to Excel

Let's begin by going through the procedure of Exporting Access data to Excel.  This is a procedure you might go through to export an Access Query or Table to Excel in order to perform further analysis.  We shall be using the Access database from my last blog post which you can download from here.

1. Open the database containing the table to be exported.  
2. Click the name of the table in the NAVIGATION PANE so that it is highlighted in orange.  Alternatively you could actually open the table.
3. Click the EXCEL Icon located in the EXPORT GROUP of the EXTERNAL DATA Ribbon.
   
   
   
   
4. When the EXPORT dialogue box opens, click the BROWSE button and select a location and filename for the newly exported excel spreadsheet.  Then click the EXPORT DATA WITH FORMATTING AND LAYOUT and the OPEN THE DESTINATION FILE AFTER THE EXPORT OPERATION IS COMPLETE check box's.
   
   

   

   Figure 1:  The EXPORT dialogue box for exporting to Microsoft Excel.

   
   
5. Then click the OK button below.  This completes the export process.  The finished result can be seen in the screen shot below:

Figure 2: Table data exported from Access into Excel.

Importing Data into Access from Excel


Now that we have exported our Access Table into Excel, lets have a go at Importing data back into a new Access Table from an Excel spreadsheet.  This is a procedure Access Developers often make when they are convert an Excel spreadsheet to Access.  For sake of convenience, we are going to Import data from the same Excel Spreadsheet which was created when we did our original Export Procedure.

1. Open the Access database into which we are going to Import the data.  You can use the same database as before, because we are going to import the data into a new table.
2. Click the IMPORT EXCEL SPREADSHEET Icon located in the IMPORT GROUP of the EXTERNAL DATA Ribbon.
   
   
3. This opens the GET EXTERNAL DATA dialogue box for Microsoft Excel.  Click the BROWSE button and search for the spreadsheet file to Import.
4. You then have three options.  You can Import the data into a new table, append the data into an existing table, or create a link.  We are going to Import the data into a new table, so click the top check box which says IMPORT THE SOURCE DATA INTO A NEW TABLE IN THE CURRENT DATABASE.  Then click OK.
   
   

   

   Figure 3:  The GET EXTERNAL DATA dialogue box for Importing to Excel.

   
   
5. This opens the first page of the IMPORT SPREADSHEET WIZARD (see screenshot below).  Our spreadsheet contains column headings which can be used as field names, so click the Check Box which says FIRST ROW CONTAINS COLUMN HEADINGS.
   
   
   
   

   

   Figure 4: The first page of the IMPORT SPREADSHEET WIZARD.

   
   
6. Click the NEXT button for the second page of the Import Spreadsheet Wizard.  This is where we are able to specify information about each of the fields we are importing - information such as DATA TYPE, INDEX, and FIELD NAME.  Click the ID column heading so that it is highlighted (if it is not already).  Then change the INDEX to YES(NO DUPLICATES) and the DATA TYPE to LONG INTEGER.  This is so we can use the ID field as the table's primary key.
   
   

   

   Figure 4: Setting the  FIELD OPTIONS in the IMPORT SPREADSHEET WIZARD.

   
   
7. Click NEXT to move to the third page of the Import Spreadsheet Wizard.  This is where we explicitly define the Primary Key for the table.  We already have a suitable column to use as the Primary Key, so we do not need Access to do this for us (the default option).  As such, click the middle Option Box where it says CHOOSE MY OWN PRIMARY KEY.  Then select the ID field from the drop down list (if it is not already selected).
   
   

   

   Figure 5: Defining the Primary Key.

   
   
8. Click NEXT to bring up the final page of the Import Spreadsheet Wizard.  This asks you to enter the name of the table.  Change the default name to tblExcelImport (a new table name that does not already exist) and then click the FINISH button.
   
   

   

   Figure 6: Selecting the new Table Name for the Imported Data.

The table has now been imported and appears in the NAVIGATION PANE of your Access Database. You are now free to open the form and view the records, and may also edit the new table in DESIGN VIEW if you wish.

Linking Access to an External Data Source

There are many reasons an Access Developer needs or chooses to link an Access database to an external Data Source.  One common reason is to increase efficiency when a team of users need simultaneous access to a database across a Local Area Network. A common practice is to store an Access file containing the database tables in a shared folder.  This is referred to the Server or Back End.  Individual Users then have a local Access database stored on their own PC's which is linked to this Server.  Each one of these local database files contains all the Access Forms, Queries, and Reports, and are referred to as Client's or Front End's.  The logic behind this set up is that once data is downloaded from the Server, any processing that is required can then be done locally, thereby freeing the Server to deliver information to other Client's on the network.

In this exercise we are going to have a go at linking an Access Database to an External Data Source.   You don't need to be on a network to try this.  The general principle of Linking works exactly the same when the Client and Server database files are stored on the same machine, and even in the same folder.

Before you begin, you will need to create a new database containing a table. Call the database LinkTestServer.accdb. This will be the data source that we will be linking to.  A simple table of made up names will do fine for this.  Alternatively, you can download this example Link Test Server Database to use for the purpose.  Once you have done this we can begin by creating the Client database, and then link it to the Server file.

1. Open Access and Create a New Database.  You can save it in the same folder as the Server file.
2. Select the EXTERNAL DATA tab on the Access Ribbon.
3. In the Ribbon's IMPORT group, click the IMPORT ACCESS DATABASE icon.
   
   
   
   
4. This opens the GET EXTERNAL DATA dialogue box.  Here we need to browse and select the name of the Access Database that we are going to use as our Server or Data Source. You will also need to click the lower option box where it says LINK TO THE DATA SOURCE BY CREATING A LINKED TABLE.
   
   
   
   
   
5. Click OK.
6. The LINK TABLES dialogue box now opens. This lists all the tables in the Server Database.  In our example there is just one: tblCustomer.  Click the name so it is highlighted in blue.
7. Click OK to complete the linking process.

You should now see the linked table represented in the NAVIGATION PANE of your Client database.

As you can see from the screen shot above, it looks similar to an ordinary 'native' table, except there is a blue arrow to its left, indicating it is a linked table.  You can now open it from within the Client database and add additional names to it as if it was a native table.  You may also base Forms on it, Query it and Create Reports from it too.  It is just the same as working with a native table except you cannot modify the table design (eg add or change fields ... etc).  The table is still located externally, but any additions, deletions or edits you make to the table's data from the Client database is reflected in the data stored in the Server database. You may like to experiment with this and see for yourself.

Using an Unbound Form to Obtain Query Parameters

In this post we are going to use an unbound form to obtain the parameters for a query.  It follows on from my last post on How to Create a Parameter Query, but this time we are going to create the form which collects the query criteria.  This is particularly useful when the query has multiple parameters, and we want make the process of running our query more user friendly.

Create an Unbound Form

This object of this exercise is to query a simple customer table, returning a particular customer record in response to entering the customers name in an unbound form. Lets begin by creating the form which prompts the user to enter a firstname and lastname in two separate text boxes.  There is also going to be a Control Button which, when clicked, runs the query that references the two text boxes on our form.

Figure 1: The form to collect our Query Parameters.

1. Click the FORM DESIGN icon.  This is located in the FORMS group on the CREATE tab of the Access Ribbon.
2. Add the first text box to the form by clicking the TEXTBOX icon from the CONTROLS group and clicking on the desired position on the design grid.  
3. Select the textbox by clicking it and then click the PROPERTIES icon to bring up the PROPERTIES sheet.  Select the OTHER tab from the sheet.
4. We are going to refer to this text box as txtFirstName.  To do this we need to type FIRSTNAME into the NAME property of the PROPERTIES SHEET.
5. Next add a second text box to the form.  
6. Select the 2nd Textbox and enter txtSurname as the NAME property on the PROPERTIES SHEET.
7. Now we need to select the whole form by clicking the small square at the top left hand corner of the design grid. We are going to change some of the form properties to make the form look like a Dialogue box.
   
8. Select the FORMAT tab of the PROPERTIES SHEET.
9. Change the RECORD SELECTORS property to NO.
10. Change the NAVIGATION BUTTONS property to NO.
11. Change the BORDER STYLE property to DIALOG.
12. Select the OTHER tab of the PROPERTIES SHEET.
13. Change the POP UP property to YES.
14. Finish by saving your form as frmEnterParameter.

Before we add the command button, we will first create the Query to be run, and the table that it is based upon.

Create Parameter Query

This follows on from my last blog post on How to Create a Parameter Query .  Before you start creating the query you will first need to set up the table which is going to be queried.  This is a simple customer table with an ID, FirstName, and Surname fields. Add around ten random names that we can use as test data later on. Once you have done this you can create the actual parameter query following the procedure below.

1. Click the QUERY DESIGN icon to create a new query.
2. Select the customer table you just created from the SHOW TABLE dialogue box.
3. Select the fields to be used in the query.  The quickest way of doing this is to double click each field name  from the table box located above the design grid.  The fields to be used are  ID, FirstName, and Surname.
4. We now need to enter the query criteria.  To do this we are going to reference the parameter text boxes on the form we created earlier.  On the Query Design Grid, enter the following syntax in the criteria row of the FirstName and Surname columns respectively.

[forms]![frmEnterParameter]![txtFirstName]

[forms]![frmEnterParameter]![txtSurname]


The parameters entered by the user at runtime will then be used by Access as the Query Criteria.

This is how the grid should look when you have finished.

Figure 2: The Query Design.

You can now save the query as qryNameSearch.

Create Command Button

Now that we have created our query, we can go back to the form we created earlier and add a command button which runs the query when clicked.   To do this we are going to use the COMMAND BUTTON WIZARD.  Here is the procedure.

1. Open the form we created earlier in DESIGN VIEW.
2. Ensure the USE CONTROL WIZARDS icon is selected.  It is located in the CONTROLS group of the DESIGN ribbon, and should be highlighted in orange.
3. Click the BUTTON CONTROL icon (from the CONTROLS group), and position it on the design grid.  When you click on the desired position, the COMMAND BUTTON WIZARD begins.
   

   

   Figure 2: The Command Button Wizard.

4. Click MISCELLANEOUS for the category, and RUN QUERY for the Action. Then click NEXT.
5. Highlight the name of the Query we want to run.  The one we created earlier was called qryNameSearch.  Click NEXT.
6. Choose whether you would like text or a picture to be displayed on the command button.  Click NEXT.
7. Enter a meaningful name for the Command Button.  Lets call ours ctlRunQuery.

We are now in a position to try out our form and query.  Open the form in FORM VIEW and enter a customer name in the two text boxes.  You will need to use a name that you entered as test data when you set up the table.  Then click the Query Command Button.  The query should then run returning the customer record you just selected.

How to Create a Parameter Query

Parameter Queries are a great way to add interactivity to your Access Database.  Instead of entering a fixed Query Criteria in the Query Design Grid, we enter a question which prompts the user to enter the criteria in a dialogue box when the query is run. In this exercise we are going to create a Parameter Query to produce a list of surnames based on a value entered by the user.

1. Create a table with three columns - ID, Firstname, and Surname.
2. Enter a some test data into the table. Between five and ten random names should do for this purpose.  You might like to give some of the records the same surname.
3. Create a new query by clicking the QUERY DESIGN icon.  This is located in the OTHERS group of the Access CREATE ribbon. 
4. Once the Query Design Window opens, select the Table you just created from the SHOW TABLE dialogue box.
5. Then select the three fields contained in this table. The quickest way of doing this is to double click each field name listed in the table box in the upper section of the screen.  These will then appear as field headings in the QUERY DESIGN GRID.
6. The last stage is to enter the user prompt in the criteria row of the Surname column.  As you may remember, this has to be enclosed within square brackets.  Our prompt is going to be [Enter Surname].

Figure 1: A Parameter Query created in the Query Design Grid.

Then when you run the query you get this message:

Figure 2: Enter Parameter Value Dialogue Box.

All you need to do now is enter one of the surnames you added to your test data in stage 2, and click OK.  Then any record with that surname is produced in your query results.

Another great thing about Parameter Queries that you might like to try, is using them as the record source for a form.  When the form is loaded the query runs asking you to enter a parameter value as before.  This time, however, once you click OK, the form opens displaying a set of records based on the value entered.

Using a Subform Link to Open a form at a Specific Record

Imagine a scenario where you are looking at a record displayed on a form. The form contains a subform displaying a number of related records summarized in datasheet view.  This tip shows how we can open a new form at a specific record when we click a particular link on the subform.  As we shall see, it is a convenient way to navigate between forms when there is an underlying Many to Many Relationship structure in place.

To do this we are going to use the example of a Customer Order form.  The main section of the form displays the Customer Order, and the Subform displays the Order Details stored in the junction table.  When a user clicks the product link in the Order Details subform, the Product Form opens at the record for that particular product.

Figure 1: The Orders Form.  Clicking the product link in the Order Details Subform
opens the Product Form (see Figure 2 below) at that particular record.

Figure 2: The Products Form displaying the record specified in the Subform in Figure 1 above.

When the user clicks the link, the textbox's On_Click Event fires, triggering a short VBA subroutine.  This is the section of code responsible for opening the Product Form at the relevant record:

Dim varWhereClause As String
varWhereClause = "ID = " & Me!productId
DoCmd.OpenForm "frmProducts", , , varWhereClause

It begins by defining a string variable to hold an SQL Where Clause.  The next line sets the string variable.  Notice how the end of the string references the productId field of the subform field that has been clicked.  The final line uses the DoCmd OpenForm Statement to open the Products Form.  The varWhereClause string variable is used as the statement's WhereCondition, thereby opening the form at that particular product record.


Here is the full procedure for putting all this in place:

1. Create the main Customer Order Form (with the Order Details Subform).  
2. Create the Products Form.
3. Next you need to go back and edit the Order Details Subform.
   
   
4. Click on the ProductId field, and then open the PROPERTIES SHEET. 
5. Under the FORMAT tab, change the IS_HYPERLINK property to YES.  Then change the DISPLAY_AS_HYPERLINK property to SCREEN_ONLY.  This changes the appearance of the ProductId field to a hyperlink style.
6. Under the EVENT tab of the PROPERTIES SHEET, select the ON_CLICK cell in the grid.  Then click the three dot symbol on it's right to open the CHOOSE BUILDER dialogue box.  
7. Select CODE BUILDER from the list, and click OK to open the VBA Editor.
8. Past the code listed below in between the lines, "Private Sub ... " and "Exit Sub", in the VBA Editor.

On Error GoTo myError
Dim varWhereClause As String
varWhereClause = "ID = " &  Me!productId
DoCmd.OpenForm "frmProducts", , , varWhereClause
leave:
Exit Sub
myError:
MsgBox Error$
Resume Next

A Gentle Introduction to Access SQL

SQL is a language used by database applications such as Microsoft Office Access, SQL Server and MySQL.  Although people using Access at a basic level do not need to know much, if anything, about the SQL language, the deeper we go into database design, the more important it becomes.  This post is intended to be a 'gentle' introduction to the subject!

SQL stands for Structured Query Language.  As the name implies, it is used in the creation of queries.  Whenever we create a Query using the Access Query Design Grid, Access converts the information we provide into SQL Code. We can view and edit this code by selecting SQL VIEW from the RESULTS group of the QUERY DESIGN ribbon. But why go to the trouble of learning SQL when we can just use the Design Grid?  There are many reasons for this.  For advanced users there are things which can be done in SQL that are too complex for the Design Grid to handle. In addition to this, SQL is also used within the Access Visual Basic programming langage thereby allowing us to automate queries and use variables in query criteria. (See my post on Automating an Update Query)

However, SQL is also used in other areas of Access such as the properties windows where we can define Record Sources for forms, Row Sources for Combo Box controls, and criteria for filters to name just a few.  As such, even at a relatively basic level, it is good to have a general awareness of SQL, and maybe some knowledge about how to create and edit simple SQL Statements.

Lets take a look at a simple SQL Statement used to query a database table.  In this example, the table is called tblCustomer.  We are going to select three fields from this table - FIRSTNAME, SURNAME, and CITY. The criteria we are going to use ='bolton' under the CITY field.  The query is designed to show a three column list of records where the value contained in the CITY Column is 'Bolton'.  Here is the SQL Code:

SELECT FirstName, Surname, City
FROM tblCustomer
WHERE City="bolton"



NB: When you look at code using Access SQL View you find that the syntax is slightly different.  Extra brackets are put around the expression following the Where clause; and the field names after the Select clause are written with a table name   [full stop] field name, like this - tblCustomer.FirstName.  This is how Access codes it own version of SQL.  It is, however, perfectly capable of reading the simpler version printed above.  Just be aware that Access will code statements slightly differently for its own purposes.  

As you can see there are three line to this SQL Statement.  The capitalised words at the beginning of each line are SQL keywords (or 'Clauses') and the small case words following relate to database fields, tables and criteria.  The Statement begins with the SELECT clause.  This is saying we are going to select the following fields (ie FirstName, SurName, and City) in this Statement.  In the next line we have the FROM clause.  This is saying that the fields selected above are taken from the following table (ie tblCustomer).  The final line is the WHERE clause.  This is saying that we are only interested in records where the following expression is true (ie city=Bolton). If we had created this Query using the Query Design Grid, it would have looked like this:

Figure 1: The equivalent query created using the Query Design Grid.

One thing that may strike you when you compare the SQL Statement with the Query Design Grid, is how simple and brief the Code actually is. A simple application of SQL is to enter a statement like that above for a form's RECORD SOURCE, as opposed to using a standard query created using the Grid.  This saves us having to create a seperate query, and minimises the number of Objects appearing in our database window. Just enter the whole SQL statement, as a single line, directly into the RECORD SOURCE cell of the PROPERTIES WINDOW.

Figure 2: An SQL Statement used as a forms RECORD SOURCE.

This has been a brief and simple introduction to Access SQL.  There is, of course, so much more to the subject. I intend to post more articles some time in the future which go into more detail.

Using a Combo Box to Search for a Record

In this tip we are going to look at how we can use a Combo Box control on a form to search for a particular record.  It works by clicking an item from the Combo Box's drop down list.  This activates a Visual Basic for Applications (VBA) Procedure using the forms ON CHANGE Event. Once the code executes, the form seamlessly displays the selected record.

Figure 1: Products Form with a Combo Box Search facility.  Selecting an item
searches for the relevant records and displays in on the form.

Lets begin by creating our Combo Box from scratch.

Create Combo Box from Scratch

1. Open your form in Design View.
2. Ensure the Wizard icon is deactivated.  If it is, just click the Wizard Icon so that it is no longer highlighted.
3. Click the Combo Box Icon.
4. When the Mouse Pointer changes to the Add Combo Box Symbol, click an area on the Form Design Grid where you would like it to go.

You now have a empty Combo Box Control on your form.  If you wish, you can resize the box and add some text to the label.  The next task is for us to enter the ROW SOURCE property.  This is the source of the data which will appear in the Combo Box's drop down list (take care not to confuse this property with the combo box CONTROL SOURCE, as we are going to keep our control unbound).

The Row Source Property

1. Select the Combo Box Control by clicking it with the mouse.
2. Click the PROPERTY SHEET icon. This brings up the Combo Box's PROPERTY SHEET.
   
3. Select the DATA tab.
4. Enter the ROW SOURCE property.

When you  enter the Row Source property, you can select a Query/Table from the Drop down list, or write an SQL Statement directly onto the property grid. Another option is to click the three dots symbol at the end of the row to bring up the QUERY DESIGN Window. Then it is just a case of creating your query.

Figure 2: The Combo Box PROPERTY sheet for
the form in figure 1.

In our example shown in figure 1, I used tblProducts as the ROW SOURCE for the Combo Box. This, of course, is the same table that is used as the RECORD SOURCE for the main form.  I set the COLUMN COUNT PROPERTY (from the FORMAT tab) to 2, so that we get two columns in the drop down list - that is, the ID field, and the ItemName field.  I also set the BOUND COLUMN property (from the DATA TAB) to 1, so our Combo Box stores the value from the first column (ie the ID field) when the user makes a selection from the drop down list.

The next stage is to enter the VBA code.  We are going to use the Combo Box's ON CHANGE event.  This

event triggers as soon as the user selects an item from the drop down list.

Enter VBA Code

1. Select the Combo Box Control by clicking it with the mouse.
2. Open the PROPERTIES window.
3. Select the EVENT tab.
4. Select the ON CHANGE event by clicking its row in the grid.
5. Click the three dots symbol on the far right of the row. This opens the CHOOSE BUILDER dialogue box.  
6. Select CODE BUILDER and click the OK button.  This opens the VISUAL BASIC editor.
7. Copy and Paste the code (listed below) between the PRIVATE SUB and END SUB statements.   You may need to edit the FINDFIRST Statement on line 4 (replace ID with the field you are searching for).

On Error GoTo myError
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "ID = " & Me!ctlSearch
Me.Bookmark = rst.Bookmark
leave:
Me!ctlSearch = Null
If Not rst Is Nothing Then Set rst = Nothing
Exit Sub
myError:
MsgBox "Record Not Found"
Resume leave

Figure 4: The VBA Editor

The main section of code works by cloning the forms's record set, which is stored in an object variable called rst. The FINDFIRST method is then used to search the cloned record set for the item selected in the Combo Box by the user.  Once found, the forms Bookmark property is then set to that of the cloned recordset.  This results in the Products Form seamlessly displaying the record selected from the drop down list.

The Many to Many Relationship

Last month we learnt how to create a comparatively simple One to Many Relationship.  In this post we shall go one step further and create a Many to Many Relationship in the context of a Customer Orders database.

The Many to Many Relationship differs from the One to Many, in that the latter involves just two tables.  The table on the One side of the relationship potentially having multiple corresponding records in the table on the Many side.  It is depicted in the Access Relationships window like this:

Figure 1: The One to Many Relationship.

By contrast the Many to Many Relationship consists of three tables.  The two tables on the Many sides of the Relationship are joined to a third Junction Table connecting them together.  It is depicted in the Access Relationships window like this:

Figure 2: The Many to Many Relationship.

In the screen-shot above, we see that tblOrder and tblProducts are joined together in a Many to Many Relationship.  TblOrderDetails is the third Junction Table mediating between the two. This mediation takes the form of two ordinary One to Many Relationships, with the junction table set up to be on the Many side of both. Let us now examine the reason why this is so.  

When a customer makes an order, there may be a number of products purchased.  As such we have a One to Many scenario - One order potentially 'containing' Many products. However, one type of product may also appear in a number of different orders.  This too, is a one to many scenario, but this time going in the opposite direction. As such, we need the Junction Table in between to record each product appearing on a particular order, and each order where a particular product appears.

One more thing: you may notice that there seems to be two primary keys pictured in the Junction table of Figure 2 above.  These are also the foreign keys from the other two tables in the relationship. There is really only one primary key, but it consists of the unique combination of the two primary keys from the two tables on the Many sides of the relationship.  As such, one particular product type can only appear in any particular Customer Order once, and vice versa.  

To understand this more clearly, you may find it helpful to visualise the layout of an invoice.  Each individual invoice pertains to a particular order for a particular customer.  The invoice includes a list of all the different products purchased for that order.  The same product types may also appear on different invoices (for the same or different customers).  The data from these lists are derived from the Junction table.
   

Creating a Many to Many Relationship


Lets have a go at creating a Many to Many Relationship ourselves.  We will begin by downloading a database containing tblOrder and tblProducts .  There is also another table called tblCustomer which is joined to tblOrder in a One to Many Relationship.  We will then create the tblOrderDetails Junction Table and the Joins which make up the Many to Many Relationship.

Setting up the Exercise

1. Download or create the tables to be used in this exercise - ManyToMany_Exercise Tables.
2. Open the database and select the DATABASE TOOLS ribbon.  
3. Click the RELATIONSHIPS icon in the SHOW/HIDE group.  Here are the tables we are starting off with.
   
   

   

   Figure 3: Start of Many to Many Exercise

   
   

Create Junction Table

1. Select the CREATE tab on the Access Ribbon.
2. Click the TABLE DESIGN icon from the TABLES group.
3. Enter the first two fields in the TABLE DESIGN GRID.  This is OrderId and ProductId.  Select NUMBER as the DATA TYPE for both fields.
4. We now need to designate both these fields as the joint primary key.  Select both rows by holding down the CONTROL key on your keyboard and clicking the two blue sections on the far left of the Grid.  Both rows are then highlighted.  You can now click the PRIMARY KEY icon in the TOOLS group of the DESIGN ribbon.  Two primary key symbols appear now on the far left of the two rows.
   
   

   

   Figure 4: Selecting the Primary Key on two fields.

   
   
5. Add the remaining two fields to the Grid.  These are Quantity (DATA TYPE: NUMBER) and CostPerUnitPaid (DATA TYPE: CURRENCY).
6. Close the table, saving it as tblOrderDetails when prompted.

Creating the Joins

1. Select the DATABASE TOOLS tab on the Access Ribbon.
2. Click the RELATIONSHIPS ICON in the SHOW/HIDE group.
3. The Junction Table we just created cannot be seen at first.  To make it visible, click the SHOW TABLE icon in the RELATIONSHIPS group of the Access Ribbon.  Double click tblOrderDetails from the SHOW TABLE dialogue box to add it to the RELATIONSHIPS window.  Close the SHOW TABLE dialogue box.
4. Click and drag the new table to a location between tblOrder and tblProducts.
5. We are now going to create a One to Many Relationship between the ID field of tblOrder, and the OrderId field of tblOrderDetails.  Do this by clicking  the tblOrder.ID field, and dragging over to tblOrders.orderId.  When you release the mouse key the EDIT RELATIONSHIPS dialogue box opens.  Click the three tick boxes and click the CREATE button to create the relationship.
   
   

   

   Figure 5: The Edit Relationship Dialogue Box.

   
   
6. Next we need to create a One to Many Relationship between the tblProducts.ID field and tblOrderDetails.productId.  Follow the same procedure as above to create this relationship.

The Many to Many Relationship is now in place.  Your RELATIONSHIPS window should now look like this:

Figure 6: The Many to Many Relationship.

Lets end this post by taking a brief look at the Many to Many relationship in action.  Obviously we have not created any tables or reports to enter or display information in a user friendly manner (this is something I hope to cover in a future post).  We can, however, see how all this information is connected together using the example of a single order.

The screen-shot below shows an order which I created for one of the customers in the customer table.

Figure 7: Three nested tables showing an order made by customer Tracey Smith.

As you can see there are three nested tables. The top level table shows the customer record from tblCustomer.  The name of the customer here is Tracey Smith and her customerId is 2.  The mid-level table shows the order record from tblOrder. There is just one order on the database for this customer.  The order ID is 18, and the orderDate is 28/4/2011.  The inner table shows the order details from tblOrderDetails. As you can see there are four product items for this order.  The product ID's are 3,2,1, & 5. The customer ordered one of each item (see the quantity field).

In the next screen-shot (figure 8) we see the products table. Each row is expanded to show a set of records from tblOrderDetails corresponding to them.  As such, we see the orders within which each product appears:

Figure 8: The Products Table (tblProducts) showing the order details (tblOrderDetails) for each item.

As you can see, Order 18 (which we looked at above from the Customer Order side of the Many to Many Relationship) is seen here from the Product side, along with all other orders.  If you look carefully you will see that an instance of OrderId 18 appears under each productId (ie 3,2,1, & 5) which we mentioned above.  As such we see how the data contained in our tables appears from both sides of the Many to Many Relationship.

Of course, this is a somewhat fragmentary perspective from which to view our data.  That is why we need  forms, queries, and reports to enter, process and display all this information in a user friendly manner.  This is something that I hope to cover in a future post.

Automating an Update Query Using DoCmd.RunSQL

This tip shows us how to create and execute an Update Query using the VBA DoCmd.RunSQL Statement.  We shall use the example of a Products table, and our objective is to update the CostPerUnit field of a particular record matching a set criteria.  To do this the user will open an unbound form to select a product from a combo box, and enter a new updated price in the textbox.  

Above: Unbound Form to select a product from a Combo Box list, and enter its new price.

The code which creates and executes the SQL Statement will run when the Update command button is clicked.  Information entered by the user is referenced by the code (as Me!ctlProduct and Me!txtNewCost) and integrated into the SQL statement.

Above:  This code runs when the users click the Update command button.

The varSQL variable contains the SQL from the Update Query which we are going to run.  This can be built using the Access Query Design Grid, and switching to SQL view to copy the statement.  The two form controls with our user input are built into this variable, and become part of the Statement. The next line uses the DoCmd.RunSQL statement to the run the SQL contained in our string variable.  This will then act like a standard Update Query: the relevant product is found, and the CostPerUnit field is updated to the new price specified by the user.

The Outer Join Query

In my last blog post we learnt about the Inner Join Query, and had a go at creating one.  In this post we are going to do the same, but this time with the Outer Join Query.  Let us begin by looking at how the two different Join types differ.

Query Joins in general connect two or more tables so that the query results present data as though it is from a single table.  This is done in a coherent manner whereby corresponding records from separate tables are presented with each row of data in the query results matching.  In the previous post on Inner Joins we used the example of a Customer Table and an Order Table to produce a single row of data which displayed a Customer Name with his or her Order.  For a record to appear in the query results the Inner Joine required each customer (in tblCustomer) to have at least one order (in tblOrder) and vice versa.  It is at this point that Inner Join and Outer Join Queries differ.

Suppose for example, we had a number of customer records with no corresponding orders in the orders table (lets say the orders were accidentally deleted at some point).  If we run an Inner Join Query, those customers without order records would not show in the results.  However, if we run an Left Outer Join Query all customers would appear regardless of whether or not they had corresponding records in the order table (provided, of course, the customer met any query criteria which was set). If a customer had no corresponding order records his or her query results row will just contain the customer's details with empty fields in place of order details.

This brings me to one last point: we need to specify whether the Join is a Left Join or Right Join.  So what does this mean?  Basically by selecting whether the Outer Join Query is left or right, we are letting Access know which table in the Join is going to display all the rows matching a given criteria, and which side will only display data corresponding to that chosen table.

Lets now have a go at creating an Outer Join Query.  The exercise below uses two tables, tblCustomer and tblOrder.  We are going to use the FIRSTNAME, SURNAME and CITY fields from the customer table, and ITEMORDERED and DATE from the orders table.  Our query criteria will filter out all customers who live in Bolton.  You can download an Access Database with these tables here, or enter them manually from the screenshots below:

Above: Date for tblCustomer.
Below: Data for tblOrder.

NB There is a One to Manly Relationship between the two tables
between tblCustomer.ID and tblOrder.CustomerId.

How to Create an Outer Join Query


We are going to do this exercise in two stages.  The first stage is to create a standard Inner Join query using the Access Query Design Grid. In the second stage with will modify the query so it becomes an Outer Join Query.


Stage One - Create an Inner Join Query.

1. Open the database you downloaded above.
2. Select the CREATE tab on the Access Ribbon.
3. Click the QUERY DESIGN icon in the OTHER group.
4. Select tblCustomer and tblOrder from the SHOW TABLE dialogue box.  Then close the dialogue box.  Both tables should now have appeared in the top section of the QUERY DESIGN GRID.  Since there is already a one to many relationship between the two tables, this is represented in the Query Design Grid as an Inner Join.
5. Select fields FirstName, Surname, and City from tblCustomer.  Then select fields ItemOrdered and Date from tblOrder.
6. Enter the query criteria ="bolton" in the CRITERIA row of the CITY column in the lower section of the grid.  

Your query should now look like the screen shot below:

If you run the query now (before we change it to an Outer Join Query) you should get this result shown below:

Above: the query run as an Inner Join before modification.

You may notice that although our customer table contained two records with Bolton as the City (our criteria), only one record was displayed.  This is due to the fact that we ran the query as an Inner Join and the excluded record that has no corresponding order in the tblOrder table.

Lets see what happens when we change the query to an Outer Join type:

Stage Two - Modify the Query to an Outer Join.

1. Open the Inner Join query we just created in DESIGN VIEW.  The QUERY DESIGN GRID opens.
2. Double click the Join between the two tables to bring up the JOIN PROPERTIES dialogue box.
   
   

   

   The JOIN PROPERTIES dialogue box.

   
   Notice how the Join Properties box shows the Left Table Name (tblCustomer) and the Right Table Name (tblOrder). This ties in with what we discussed above regarding Left and Right Joins. We want all customers to be displayed regardless of whether they have a corresponding record in the orders table.  As tblCustomer is the Left Table shown in the JOIN PROPERTIES BOX we need to modify our query to a Left Outer Join.  To do this we need to click Option Two where it says "Include ALL records from 'tblCustomer' and only those records from 'tblOrder' where the joined fields are equal".  
3. Click Option 2 in the JOIN PROPERTIES dialogue box and click OK.

Your Left Outer Join is now in place.  It is represented in the Query Design Grid like this:

Representation of the Left Outer Join in
the Query Design Window.

Notice the joining line between the two tables now has an arrow pointing from left to right, ie from tblCustomer to tblOrder.  Our query will show all records in tblCustomer (matching our ='bolton' criteria, and only orders corresponding to those customers matching the set criteria.  Lets run the query and see:

Results from the modified Left Outer Join Query.

Now both customers from Bolton appear in the query results.  As you can see, Andrew Johnson's record has no corresponding records in the orders table.  When we ran the Inner Join Query previously, this customer did not feature in the results for that reason.  Now that we have modified it to a Left Outer Join Query, this customer record is no longer excluded.

Now then, suppose we removed the criteria from this query and added a new order record in tblOrder without a corresponding customer record.  What do you think would show if we ran the Left Outer Join Query again?  Please feel free to try this.  You will see that all customers with their corresponding orders are displayed.  However, our new order record (without a corresponding customer) is excluded from the query results.  See what happens next if we modify the Join to a Right Outer Join Query.  Do this by :

1. Opening the Query Design Window.
2. Double clicking the existing Join line between the tables to open the JOIN PROPERTIES dialogue box.
3. Select Option Three where it says "Include all records from 'tblOrder' and only those orders from 'tblCustomer' where the join fields are equal"

This has changed our Query to a Right Outer Join.  Trying running the query now.  

Query results when from a Right Outer Join.

The new order record now appears in the results whilst the customer record with no order is now excluded.  This is the difference between a Left Outer Join Query and a Right Outer Join Query.  As such we see that having a Left or Right Join determines which table is effectively 'leading' the query.