Inner Join Basics Access/SQL

Michael D -

UserDetails

  • Int: IDTag
  • Date: BirthDate
  • Int: Age
  • Date: JoinDate

UserDetails links to the UserList table. The linking field is IDTag. It is a number or int type. It is common to use an ID field on databases to make it easier to look up records in linked tables.

UserList

  • Int: IDTag
  • Short Text: FirstName
  • Short Text: LastName

Grabbing the IDTag field here will combine the User's first and last name and match it with their birthday, age and joindate in the UserDetails table.

Visual Query Builder

I did this visually by creating a new query. CTL + Clicking so that I have the UserList (index) table and the UserDetails (lookup) table in the select statement.

You can optionally pick one table, right click the grey space, click 'show table' and picking the desired linking table.

I then dragged from one IDTag to the other IDTag. The fields used to make the join do not have to be the same name just of the same datatype (int in this case).

Now we have to add the fields.

Notice how we are referencing the tables by their absolute name now. The syntax is [TableName].[FieldName] this is to prevent ambiguity which is where the same field name is in both tables and the software doesn't know which of the two you mean.

In this join we make

UserDetails.IDTag = UserList.IDTAg

All rows attached to each is now conjoined into the other table like a venndiagram.

SQL style query

This can be done in Access SQL code by switching the view to SQL view and typing.

I find it MUCH easier to use the SQL code it is concise and less cumbersome. Lines starting with # do not run in the query.

Joining UserList to UserDetails

SELECT
   ## All of fields wanted in query, by absolute name
   UserList.IDTag, UserList.FirstName, UserList.LastName, UserDetails.Age, UserDetails.UserBio
FROM
   ##The First Table you select
   UserList 
INNER JOIN
   ## Table2 ON Table1.FieldName = Table2.SameFieldName
   UserDetails ON UserList.IDTag = UserDetails.IDTag;

Always end the statement with a semicolon. This is how you mark the end of a query. A query is read as if it was written on one line, the ; enables us to use lines and indents and make the query more legible.

We can do this for the trails Table as well.

Joining TrailIndex to TrailDetails

SELECT
   TrailIndex.TrailID, TrailIndex.TrailName, TrailDetails.TrailDesc, TrailDetails.TrailAddress
FROM
   TrailIndex
INNER JOIN
   TrailDetails ON TrailIndex.TrailID = TrailDetails.TrailID;

As you can see the layout of join is similar to the other two tables. This is an Inner Join, this means there is one field that is the same on both tables and can be used for Linking (think the middle of a venn diagram)

This example worked because both TrailIndex.TrailID and TrailDetails.TrailID are the int or number datatype.

If both tables had a field named TrailName we could use that if they were both the same datatype eg. short text.

Sources: