How to Create an Append Query in Access [Solved]

How to Create an Append Query in Access

  1. Create a SELECT query to determine the records that will be appended. Apply anyAccess Logo required query criteria
  2. In the query design view, click on the drop-down arrow to the right of the Query Type button and choose Append Query
  3. In the Append dialog box, select the table that you want to Append To from the list of tables in the current database, or select to append this to another database and either Browse to this, or enter the full file path including the database name
  4. If needed, modify the query further so that the correct fields will be appended with the desired new data
  5. Click on the Run (!) button to run the action query
  6. When informed of the number of records to be appended in the Microsoft Access dialog box, click Yes
  7. Close the query, saving if required

Now consider the following scenario example, that will illustrate the use of a Microsoft Access append query:

Your company runs training courses, with students signing up for these courses. Student membership lasts for 2 years. All of this information relating to their details is stored in your StudentInformation table. You would like to remove any records from this table that are not of current students, so you will archive records by initially appending the data from your StudentInformation table into another table (ExpiredStudents) and then deleting these records from the StudentInformation table.

The original StudentInformation table, containing 52 records, would look like the following:

The original Student Information table, containing 52 student records

The original Student Information table, containing 52 student records

As you will see, the above table contains contact information relating to the student members. You will see from the fields included, that there is a field detailing when the Student Enrolled on the course (dtmEnrolled). This field also is displaying records older than two years old.

In our scenario, we only wish to keep records in the table where the student is still currently active. With the courses being a two year duration, we only want records that are greater than or equal to todays date minus two years.

To allow us to append records to an archive table, we must first have the archive table created, whether it be in the active database or in another database. The table should ideally have exactly the same structure as the original table and contain the same fieldnames and data type/sizes.

In our example we have an archived table created, named tblExpiredStudents, which will hold the historical records of students whose two years have expired.

We now need to create the Append Query that will copy the records from the original table to the historical archive table. In a new SELECT query, in design view we will add all of the fields from the StudentInformation table as shown:

The initial design of the query, prior to changing to the append query type

You will see that we have added a criteria to the dtmEnrolled field, as specified we want to remove any records that are older than two years from today’s date. The criteria that is applied is:


This will display only records that are older than 2 years from todays date.

Before we change the query type to an Append query, we should check the results that this displays. We can do this by clicking on the Datasheet button Datasheet button on the toolbar.

If we check the datasheet displaying the results of running the query with the DateAdd criteria applied, we will now see that the record count is displaying only 20 records. This is showing 20 records that need to be removed due to them being greater than 2 years old:

The query datasheet results, with the DateAdd criteria applied.

Once we are happy with the results that are going to be appended to the archive table, we can then change the query type to an append query. To do this we need to go back into the Microsoft Access query design view.

Once in design view, we change the query type using the Query Type button The Query Type button on the toolbar. From the query type drop-down list, change the query type to an Append Query. You will then be presented with the Append dialog box, where you can then choose the table that you want to append the data to. From the list, choose the table if this is in the same database or enter the file location and database name for an external database table:

The append dialog, where you can choose the table to append to.

Choose the table and click on OK.

You will now see the the query design includes a new row labeled Append To, which details the fields in the new table (selected previously) that the data will be appended to. You will see below this row in the query design:

The append query design, showing the Append To row in the design grid.

We now need to Run this query, using the Run button The Run button to run the action query to append the data to the other database table. The warning dialog box indicates the number of records that will be appended, click Yes to accept this:

The warning message, warning that you are going to append records to the new table.

This will now have appended (copied) the records to the archive table.


How to Append Queries in Microsoft Access [Solved]

How to Append Queries in Microsoft Access

Access LogoAn Append Query is an action query (SQL statement) that adds records to a table. An Append query is often referred to as an Insert Query because the SQL syntax uses the INSERT INTO command.

Microsoft Access 2013 Append Query Ribbon to Insert Records into a Table
Append Query Option when Designing Queries in MS Access 2013

Microsoft Access Query Ribbon to Specify Append Query to Insert Records into a Table
Append Query Option when Designing Queries in MS Access 2007 and 2010

Append Queries are very powerful and lets you combine data from multiple tables and/or queries, specify criteria and put them into fields of an existing table. Think of it as a SELECT query where you can save the results in a table. The field names of the source and target tables do not need to match. In fact, you can create expressions to combine fields, use VBA functions, etc., to insert new values into the table.

Append Queries make it easy to save data at a point in time, use the data for temporary analysis, and display it in forms and reports. Of course, once in the new table, any edits in the new table do not impact the data in the original source. If that’s needed, stick to SELECT queries.

Designing an Append/Insert Query

From the Microsoft Access query designer, you can interactively create a query and specify its type. When you select Append, you are prompted to enter the name of the table that you want to insert records:

Microsoft Access Append Query Dialog for the Table Name to Insert Records

Specify the name of the table from the combo box. The table should already exist in your database.

The query designer is similar to how you’d create a SELECT query. You can specify criteria, create expressions, link between multiple tables and queries, etc. The difference is an “Append To” row that specifies which field in the target table each column is inserted into:

Microsoft Access Append Query Design Example

Notice in the example above that the Source field is called Memo that is being inserted into the Description field of the target table. Append Queries make it easy to put fields into different field names. It can also be an expression (formula) that’s inserted into a field.

Once the query is saved, you can run it to insert the records into your target table. If you want just this query’s results in that table, empty the table first before running the append query. You can create a Delete Query to empty all the records and run that first.


How to Create Database Relationship using Access [Solved]

How to Create Database Relationship using Access

Access LogoSo you’ve made the move from a spreadsheet to a database. You’ve set up your tables and painstakingly transferred all of your precious data. You take a well-deserved break, sit back and look at the tables you’ve created. Wait a second — they look strangely familiar to the spreadsheets you’ve just disowned. Did you just reinvent the wheel? What’s the difference between a spreadsheet and a database anyway?

One of the major advantages of databases such as Microsoft Access is their ability to maintain relationships between different data tables. The power of a database makes it possible to correlate data in many ways and ensure the consistency (or referential integrity) of this data from table to table. In this article we’ll take a look at the process of creating a simple relationship using a Microsoft Access database.

Imagine a small database we’ve created for the Acme Widget Company. We want to track both our employees and our customer orders. We might use a table structure similar to the one shown below:

Notice that each order is associated with a specific employee. This information overlap presents the perfect situation for the use of a database relationship. Together we’ll create a Foriegn Key relationship that instructs the database that the EmployeeID column in the Orders table corresponds to the EmployeeID column in the Employees table.

Once the relationship is established, we’ve unleashed a powerful set of features in Microsoft Access. The database will ensure that only values corresponding to a valid employee (as listed in the Employees table) can be inserted in the Orders table. Additionally, we have the option of instructing the database to remove all orders associated with an employee when the employee is deleted from the Employees table.

Now let’s dig in and create that relationship!

1.  Open the Relationships Window from the Tools menu.

2.  Add the appropriate tables.  We’ll add the Employees and Orders tables — you can use the Shift-Click combination to select both at the same time.  Once you’ve highlighted the tables click the Add button.

3.  Open the Edit Relationships tool.  You’ll find it on the Relationships pull-down menu.

4.  Create a new relationship.

5.  Fill in the appropriate details.  The left table is the primary source of the data and the right table is the desired location of the foreign key.  In this case, our original information is stored in the Employees table and the foreign key will be in the Orders table.  The field name is EmployeeID in both cases.  Once you’ve selected the appropriate items, click OK to continue.

6.  Choose whether to enforce Referential Integrity.  In most circumstances, you will want to select this option.  This is the real power of a relationship — it ensures that new records in the Orders table only contain the IDs of valid employees from the Employees table.

You’ll also notice two other options here.  The “Cascade Update Related Fields” option ensures that if an EmployeeID changes in the Employees table that change is propagated to all related records in the Orders table.  Similarly, the “Cascade Delete Related Records” option removes all related Orders records when an Employee record is removed.  The use of these options will depend upon the particular requirements of your database.  In this example, we’ll won’t utilize either one.

7.  Open the Join Type dialog box.

8.  Select a join type.  The three options are shown in the figure below.  If you’re familiar with SQL, you might notice that the first option corresponds to an inner join, the second to a left outer join and the final to a right outer join.  We’ll use an inner join for our example.

9.  Create the relationship.

Voila!  The relationship window now reflects our new relationship!


How to Create an Update Query in MS Access [Solved]

How to Create an Update Query in MS Access

Access LogoA situation may arise where we need to update many records in a database table when certain information changes or needs to be modified. A Microsoft Access Update Query updates specified values in a table for all records or for those records that match a specified criteria.

It is possible to update records manually in a database table, either via a form or through the tables datasheet, however this may take a very long time. Changing records manually is not only time consuming, it is also inefficient, and lends itself to errors as you update the records.

The best way to handle this type of event, the updating of many records in a database table, is to use an Update Query to make the changes to data in one operation. You will save yourself time, and eliminate the possibility of manual errors.

Note: Due to the permanent effects of working with an update action query, you should always make a backup copy of your tables, or your database before attempting this option.

How to create a Microsoft Access Update Query:

  1. Create a SELECT query to determine the records that will be updated. Apply any required query criteria, and view the data that will be updated by pressing the Datasheet button.
  2. In the query design view, click on the drop-down arrow to the right of the Query Type button and choose Update Query.
  3. After you are satisfied that the information to be updated is correct Run the query using the Run Icon

Now consider the following scenario example, that will illustrate the use of a Microsoft Access Update query:

One of your product suppliers has decided to increase the price of their products by 3 percent (%) across the board. You have decided that you will use an Update Query to change all of the relevant records in your Products table.

Our original table appears like the following example:

The original Products table before we run the Update Query
The original Products table before we run the Update Query

We need to update the Unit Price field, to update all records from our supplier (Exotic Liquids) as they have informed us of a 3% price increase.

Creating the Update Query

Here are the steps that we follow to produce the required outcome:

  1. Create a new query using the Products table and the Suppliers table. Include the fields that you are going to use to update the data (ProductID, ProductName and UnitPrice from the Products table, and CompanyName from the Suppliers table)

    We have also included criteria in the CompanyName field to limit the results to only those of the Supplier that we are updating the records for.

    Also, in the image below, you will see that we have included an additional field, just to test our expression. This will give us a value for the 3% increase, just to check that the results will be returned correctly. This field will be removed before we run the update, but we will use the expression later.

    Creating a SELECT query, that will later be changed to the UPDATE Query
    Creating a SELECT query, that will later be changed to the UPDATE Query

  2. To check what results this will produce, run the query by clicking on the datasheet Datasheet view button

    Checking the results before running the Update Query
    Checking the results before running the Update Query

  3. When we are happy with the resulting data, we can switch back to design view to convert the query to an Update query.

    From the Query Type button Query Type Button on the toolbar, select Update Query Update Query Option

    In the curUnitPrice column, in the Update To cell, type in the expression [curUnitPrice]*1.03 and press enter. This expression will update the original Unit Price by 3 Percent.

    The query design should now look like:

    The Update Query design
    The Update Query design

  4. We now need to Run this query, using the Run button The Run button to run the action query to update the data in our Products table that meets the criteria that is applied. The warning dialog box indicates the number of records that will be update, click Yes to accept this:

    The warning message, warning that you are going to update records from the original database table.

Remember that the update query will permanently update records from the specified table(s), therefore it is very important that you have backed up the table(s) or database before running this object.

Check Your Update Query Results

Once you have ran the update query, you can check the results by once again changing the update query back to a select query.


How to Work with Multiple Table Queries in Access [Solved]

How to Work with Multiple Table Queries in Access

Creating Other Types of Joins

Access LogoSo far you’ve worked only with inner joins, which is as it should be because inner joins are by far the most common, particularly in a business environment. However, the three other types of joins—outer, self, and theta—can also come in handy and are discussed in the next three sections.

Creating Outer Joins

An outer join is one where all the records in one table are included in the dynaset regardless of whether there are matching records in the other table. For example, suppose you’re dealing with Northwind’s Customers and Orders tables, which are related on the common CustomerID field. An inner join between these tables shows only those customers who have placed orders. By contrast, an outer join on the Customers table displays all the records from that table, even customers who have never placed an order.

There are two types of outer joins:

Left outer join—This join displays all the records from the “left” table. For example, in tables with a one-to-many relationship, the left outer join displays all the records from the “one” table.

Right outer join—This join displays all the records from the “right” table. For example, in tables with a one-to-many relationship, the left outer join displays all the records from the “many” table.

To set the type of outer join, follow these steps:

    1. Add the tables to the query design window.
    2. Create the relationship between the tables, if one doesn’t exist.
    3. Choose View, Join Properties. Access displays the Join Properties dialog box, shown in Figure 3.12.

Figure 3.12Figure 3.12 Use the Join Properties dialog box to select the type of join you want.

  1. Option 1 creates an inner join. To change to an outer join, click either 2 (for a left outer join) or 3 (for a right outer join).
  2. Click OK.

Using Outer Joins to Find Records Without Matching Records in a Related Table

The most common use for outer joins is to look for records in one table that don’t have a matching record in some related table. For example, you can look for records in the Customers table that have no corresponding records in the Orders table; this tells you which customers have not yet placed orders. Similarly, you can look for records in the Products table that have no corresponding records in the Categories table; this tells you that you have a data entry problem because all products should have a category.

As a general rule, to see only those records without matching records in a related table, do one of the following:

  • To see records in the parent table without matching records in the child table, create a left outer join and filter the dynaset by adding Is Null as the criteria for the common field in the child table.
  • To see orphan records in the child table (that is, records in the child table without any corresponding records in the parent table), create a right outer join and filter the dynaset by adding Is Null as the criteria for the common field in the parent table.

The next two sections take you through examples of these techniques.

Finding Customers Without Matching Orders

For example, suppose you want to see a list of customers who haven’t yet placed an order. This means you want to join the Customers table and the Order table, which are related on the CustomerID field. You start by displaying all the Customers. This means, because Customers is the parent of Orders, you need to create a left outer join. Figure 3.13 shows the query setup, including the fact that option 2 (left outer join) is chosen in the Join Properties dialog box. Figure 3.14 shows the resulting dynaset.

Figure 3.13Figure 3.13 A query set up for a left outer join between the Customers and Orders tables.

In Figure 3.14, notice that the first two records in the Custom field is blank. This tells you that these are the records in Customers that have no matching records in Orders, meaning they haven’t yet placed any orders. Therefore, rather than displaying all the records, filter the dynaset to show only those where the CustomerID field of the Orders table is equal to Null. Figure 3.15 shows the revised query that adds this criterion.

Figure 3.14Figure 3.14 The dynaset created by the query in Figure 3.13.

Figure 3.15Figure 3.15 To see only those customer without matching orders, add the expression Is Null to the Orders.CustomerID field.

Finding Products Without an Assigned Category

In the Products table, each record should have been assigned an item from the Categories table. To make sure, you can build a query that looks for those Products without a matching category. This requires joining the Products table and the Categories table, which are related via the CategoryID field. Because Products is a child of Categories, you need to create a right outer join. You then add the CategoryID field from the Categories table and filter it using the Is Null criterion, as shown in Figure 3.16.

Figure 3.16Figure 3.16 To find those products without an assigned category, create a right outer join and filter the Categories.CategoryID field using the Is Null expression.

Creating Self-Joins

Database tables are sometimes self-referential, which means they contain a field with data that points to another field in the same table. A good example is the Northwind Employees table, which includes an EmployeeID field, the primary key that contains the employee identification numbers. Employees also contains the ReportsTo field, which contains the identification number of the person each employee reports to. In other words, each value in the ReportsTo field will have a corresponding value in the EmployeeID field.

If you want to know, for example, which employees have people reporting to them, you need to create a self-join—a table joined to itself—on the Employees table. Creating a self-join involves the following steps:

  1. Start a new query and add the table (Employees in this case) twice.
  2. Create a temporary join by clicking and dragging the field that contains the data (EmployeeID) to the field that contains the subset of the data (ReportsTo).
  3. Add the fields you want to use for the query to the design grid and then set up your criteria, sorting, and other query elements.
  4. For a self-join to work properly, you need to tell Access to return only unique values in the query. To do this, click an empty spot inside the query design window and then choose View, Properties (or press Alt+Enter). In the Query Properties window, click Yes in the Unique Values list and then close the window.

For more about the Unique Values property, seeCreating a Unique Values Query.”

Figure 3.17 shows a self-join on the Employees table, and Figure 3.18 shows the resulting dynaset, which displays the employees who have people reporting to them.

Figure 3.17Figure 3.17 A query set up for a self-join on the Employees table.

Figure 3.18Figure 3.18 The dynaset created by the query in Figure 3.17.

Creating Theta Joins

The joins you’ve seen so far have all worked on the premise that the join is based on the equality between two fields. In an inner join, for example, you only see records where the joined fields from both tables are equal; similarly, in an outer join, you see all the records from one table, but only those records from the second table where the joined fields are equal.

In business, however, it’s sometimes the case that you need a join that’s based on fields that are unequal. For example, Northwind’s Customers table has a CompanyName field, and its Orders table has a ShipName field. In most cases, these values should be the same; that is, if a customer places an order, that order should be sent to that company. If the shipping name isn’t the same as the customer name, it might mean either that the order was sent to the wrong company or that the company name is wrong in one table or the other. (It’s also possible that the order is correct and that the customer asked for the shipment to be sent to a different ship address.)

To check into this type of scenario, you need a not-equal join that joins two tables and shows only those records where the joined fields from both tables are not equal; for example, joining the Customers and Orders tables based on whether the CompanyName and ShipName fields are not equal.

Here’s the procedure to follow to create a not-equal join:

    1. Start a new query and add the tables you want to work with (such as Customers and Orders).
    2. If no relation exists between the tables, create a temporary join by clicking and dragging the appropriate field from one table and dropping it on the related field in the other table.
    3. Add the fields you want to use for the query to the design grid and then set up your criteria, sorting, and other query elements. Be sure to include the fields on which the not-equal join will be based (such as CompanyName from the Customers table and ShipName from the Orders table).
    4. In the Criteria cell of the field for which you want to check for not-equal values (such as the Orders table’s ShipName field), enter a comparison formula using the following general form:

Here, RelatedTable is the name of the other table in the query, and JoinedField is the field from the other table that is joined to the current field. Here’s an example for the Orders.ShipName field:

Figure 3.19 shows a not-equal join between the Customers table and the Orders table, with the not-equal criterion added for the Orders.ShipName field. Figure 3.20 shows the resulting dynaset, which displays the orders where the shipping name is different from the customer name. Notice that query has caught two subtle errors:

  • For the customer “Galeria del gastrónomo,” the accent is in the wrong place in the ShipName field (“Galeria del gastronómo”).
  • For the customer “Wolski Zajazd,” the CompanyName field has two spaces between “Wolski” and “Zajazd.”

Figure 3.19Figure 3.19 A query set up for a not-equal join on the Customers and Orders table to look for orders where the ShipName is not equal to the CompanyName.

Figure 3.20Figure 3.20 The dynaset created by the query in Figure 3.19.


How to Sort or Filter Records in Access [Solved]

How to Sort or Filter Records in Access

Sorting means organizing records in a meaningful way so that you can retrieve data faster Access Logoand in an order of your choice. For example, if you want to view records in the ascending order of the last name of volunteers, you can sort the records based on the values in the last name field. You can also sort records based on one or more fields.

Filtering means temporarily isolating a subset of records that you can use for specific tasks. For example, if you want to delete or edit all Centre County records in Datasheet view without navigating through all the records of all the counties.

Sorting records by a single field

  1. Open your database in Datasheet view
  2. Place your insertion point in any row in the field you want to sort
  3. From the Records menu, choose Sort…Sort Ascending.

Sort records by multiple fields

(fields must be adjacent to each other in Datasheet view)

  1. Point to the first field heading you want to sort
  2. Drag to select the second field heading column you want to sort
  3. From the Records, choose Sort…Sort Ascending

Filtering Records In the Datasheet view

(you can use Access’ filtering feature to display only the records that you want to view)

  1. Select the criteria or value you want to filter
  2. From the Records menu, select Filter…Filter by Selection
  3. To remove the filter, from the Records menu…Remove Filter/Sort


How to Create a Query to Find Duplicate Entries in a Table using MS Access [Solved]

How to Create a Query to Find Duplicate Entries in a Table using MS Access

Access LogoRunning into duplicate data in an Access Database can be quite annoying during the hectic workday, so I decided to write up a technique to help identify the duplicate records so you can manually handle them.

There are many causes for duplicate data: for instance if you have multiple users entering data simultaneously via linked tables, or you might inherit a table with duplicate data already entered.

First open the MDB (Microsoft Database) containing the table you want to check for duplicates.  Click on the Queries tab and New.


This will open the New Query dialog box.  Highlight Find Duplicates Query Wizard then click OK.


Now highlight the table you want to check for duplicate data.  You can also choose Queries or both Tables and Queries.  I have never seen a use for searching Queries … but perhaps it would come in handy for another’s situation.  Once you’ve highlighted the appropriate table click Next.


Here we will choose the field or fields within the table we want to check for duplicate data.  Try to avoid generalized fields.


Name the Query and hit Finish.  The Query will run right away and pop up the results.  Also the Query is saved in the Queries section of Access.


Depending upon the selected tables and fields your results will look something similar to the shots below which show I have nothing duplicated in the first shot and the results of duplicates in the other.

1           1


How to Use The Outer Join Query using Access [SOLVED]

How to Use The Outer Join Query using Access

Access LogoIn 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.

How to Use The Outer Join Query using Access

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.


How to Eliminate Duplicate Records using Access Query

How to Eliminate Duplicate Records using Access Query

Effectively maintaining an Access database requires the regular removal of duplicate records. The Find Duplicates Query wizard handles this chore quickly and easily.

When maintaining an Access database, it’s a good idea to regularly remove duplicate Access Logorecords. Duplicate records can pop up even with proficient data entry processes. For example, the same customer might be listed twice under two different account numbers. Or, two different customer records might have the same address in their Street Address fields, only one of which is correct. Thankfully, record removal doesn’t have to be a long and tedious process; you can find these discrepancies in seconds using the Find Duplicates Query wizard.

Using the wizard
To show how this technique works, we will use the Find Duplicates Query wizard to determine which customers in the Customers table, shown in Figure A, have the same address assigned to their Street Address fields. (Note that while the sample table used here has less than 20 records, the same technique can be applied to a database of any size.)

Figure A

Begin by clicking Queries under Objects in the Database Window. Then, click on the New button in the toolbar to obtain the dialog box shown in Figure B.

Figure B

Select Find Duplicates Query Wizard, and then click OK to activate the first screen of the wizard, as shown in Figure C.

Figure C

In this screen, the Customers table is selected as the table to be searched for duplicates. Click Next to continue.

Figure D

The screen shown in Figure D is used to select the field we want to search for duplicate values. For this example, Street Address is chosen, as we want to eliminate duplicate mailings to the same address. Clicking Next brings up the screen shown in Figure E, where we select the fields to be displayed along with the Street Address field in the query results.

Figure E

In the final screen, shown in Figure F, we enter a name for the query, and then click Finish.

Figure F

The results shown in Figure G indicate that two different customers have been assigned the same street address, while two other customers have each been assigned two different customer IDs. After further research, we determine that the house number for Bernadette Williamson should be 384, not 834. We can now correct the Customers table by manually deleting the duplicate records and making the necessary corrections to Williamson’s street address—right from the query results table.

Figure G

Deleting duplicates with Append Query
In the above example, only two duplicate records were found. But what if the Customers database consisted of thousands of records, and after running the Find Duplicates Query wizard, the results showed hundreds of duplicate records? Manually deleting all those duplicates from the query results table would be highly impractical. Instead, you can use Append Query to have Access delete them automatically.

First, create a copy of the structure of the table that contains the duplicates. Click on the table name Customers in the Database Window, and then click the Copy button in the toolbar. Next, click the Paste button, which will display the Paste Table As dialog box shown in Figure H. Enter a name for the copy of the table structure, as shown. Under options, select Structure Only. Click OK to create the blank Customers Without Duplicates table.

Figure H

Open the Customers Without Duplicates table in Design View and change its primary key to Street Address, as shown in Figure I. Making the Street Address field the primary key field will prevent Access from copying records to the new table that have duplicate street addresses.

Figure I

We are now ready to create an Append Query against the original table. Create a query in design view for Customers. Drag the asterisk (*) to the query design grid to include all fields from the original table. Then select Append Query from the query-type drop-down list, as shown in Figure J.

Figure J

In the Append dialog box, select the blank database Customers Without Duplicates, as shown in Figure K.

Figure K

Click the Run button. In the dialog box that asks whether you wish to append the records to the new file, click Yes. A dialog box similar to the one shown in Figure L will appear, indicating that some records could not be copied because there were duplicate values in the primary key field, in this case, the Street Address fields. Click Yes.

Figure L

The query results table will have only one record for each street address. When you are satisfied that the Customers Without Duplicates table is correct, you may delete the original table.

When to use the wizard
As shown in this article, the Find Duplicates Query wizard works well when it is used to check for duplicate entries in individual fields and when the changes are small enough to be handled manually; otherwise, it is more practical to use the Append Query when you need to delete large numbers of duplicate records. Nevertheless, to keep your data clean, it’s a good idea to run the Find Duplicates Query wizard periodically; if you do, you may never need to run an Append Query.



Filter Your Data with Access 2013

Filter Your Data with Access 2013

As you manage your business in an Access 2013 web app, the amount of information it holds will naturally grow over time.  The default views and navigation are great for getting started quickly, but eventually, wading through all of that information by scrolling through lists may not be the most efficient setup.  Is there an easier way to create a different view of the data?  In Access 2013 web apps you can create a customized filtered view in just a couple of minutes.

For example, let’s say you work for a marketing company that deals with local Car Dealerships and you have created an Access app to track the projects you manage for your customers.  If so, you might end up with a set of Projects that looks something like the example below.  Here you have a list of all your projects from A-Z.

Over time, you will have quite a few projects to manage, so you may start to break them into categories as we have above.  Each project in this list belongs to a category: Research, Marketing, or Design.  As you can see, the Templeton Triangle project shown above is a Research project.

Now, one of your research assistants – let’s call him Dave – asks if you can create a view to help him see an overview of current and upcoming Research projects.  So, you take a minute to talk with Dave and the two of you decide that a view like the one shown below would be great for him.  This is a list of all Research projects, sorted by Priority and Start Date.

To create a view like this, start by creating a new query – open up the app in the app designer and click on Advanced->Query in the ribbon.

This will open the Query designer where you can setup some rules to filter and sort a set of data.

Choose the source table for your query.  If you want to see some of your Customers, then you would likely choose the Customers table here.  In our example, we’re going to select Projects.

Once selected you’ll see the query design screen.  Here you can double click on fields from the Projects table to add them to your query.

Selecting fields this way determines which of them you would like to see and use in the new view.  As we decided with Dave earlier, we’re going to select the following fields:

  • ID
  • Priority
  • Start Date
  • Project Name
  • Status
  • Customer
  • Category

NOTE: If you would like to EDIT from the view, then you MUST INCLUDE an ID field in the query as we have done here.

Now that you have chosen the fields, there are two things left to do.

First, Dave wants to see the Projects in PRIORITY order first, followed by START DATE.  To do this, fill in the “Sort” box in each of these fields as Ascending (i.e. A to Z).

Second, since Dave is a research assistant, he’s only really interested in Research projects.  So, we’ll want to filter out any other Project types as well.  To do this, find the Category field in the query and add the CRITERIA “Research” (including the quotes).  This tells Access to only show Project that have a category of Research.

When you’re finished, the query should look like the example below.  Now you can save it, and give it a name.  We’ve named our example “Projects_Research Only”.

Great!  Now that we’re done with that, we can go about building the view.  Go back to the App Designer home screen, navigate to where you would like to add the view, and click on the  button.  Give the view a name, select Datasheet for the View Type, and be sure to select the Query you just made as the Record Source.

Now, when you click “Add New View” you will have a nice summarized view of your Research projects, sorted by Priority and Start Date.

And that’s how to design a simple filtered view in Access 2013! 

Filter Your Data with Access 2013