Inner Join Basics Access/SQL
A brief rundown of an Inner Join in SQL and MS Access
- Int: IDTag
- Date: BirthDate
- Int: Age
- Date: JoinDate
UserDetails links to the
UserList table. The linking field is IDTag. It is a
int type. It is common to use an ID field on databases to make it easier to look up records in linked tables.
- 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
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
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
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.