Join Queries in Microsoft Access

JOIN

This time, addressing a subject a little confusing, especially when entering into the scene there are join (links) nested. The links between tables are for the so-called streamlining the database, in other words the main aim is to have the least amount of duplicate data, because it would be detrimental to the size of the data store and thus the complexity and speed of data processing. Let’s take an example:

Customers table
CustomerID (long integer increment) Name (text), Code (text), State (Text), Surname (text)

It can happen for example that John Smith and Alan Smith relatives are common and are both the same, then I archive the first record with that municipality and the second with the same value of the municipality. We can rationalize the database by simply dividing the Customers table in two tables, customers on the note and the list of municipalities united by a report as the unique identifier of the municipality.

Customers Table
CustomerID (long integer increment) Name (text), IDState (long integer) 
States Table
IDState (long integer increment), Code (text), City (Text), Surname (text)

In this way just to bring together a select query with the values in common and this precisely is done through the join. Let us find the name and the municipality of all customers whose names begin with F:

SELECT [Customers.Name], [States.State] FROM Customers INNER JOIN ON States Customers.IDState = States.IDState WHERE Customers.Name LIKE ‘f *’

The syntax for the join is to take the first table (INNER JOIN) with the second and put them together using the same element (ON x1 = x2). In this case we used a “INNER JOIN” with which the first table contains significant elements as the second, in practice they are on the same level, so I can have in the results only customers with at least one municipality. If you want to have customers in the selection even without a common I do the “LEFT JOIN” and give more prominence to the table on the left, which in our case is the Customers table.

SELECT [Customers.Name], [States.State] FROM Customers LEFT JOIN ON States Customers.IDState = States.IDState WHERE Customers.Name LIKE ‘f *’

In this case the selection I would have no common customers. For the opposite case, the “RIGHT JOIN” and then shared without customer in selection:

SELECT [Customers.Name], [States.State] FROM Customers JOIN RIGHT ON States Customers.IDState = States.IDState WHERE Customers.Name LIKE ‘f *’

Access is easy to build a join (connection), you can create it in the query or if the 2 tables should always go together you can also create as part of the entire database, select the Tools menu, reports and create a new report . The graphics are very intuitive as the left and right joins are represented by lines with arrows. Nested links to other tables and just plug in the syntax are also brackets to divide the various parties:

SELECT Customers.Name, States.State, Banks.Bank FROM (Customers LEFT JOIN ON States Customers.IDState = States.IDState) LEFT JOIN ON Customers.IDBank = Banks.IDBank

Next time we’ll talk about aggregate functions.

<<Previous Lesson