MS Access: Concatenated Key or Join Multiple Fields

Question

Hi,

For work I usually link together multiple tables using the following criterion: Account Number, Date of Service and Physician Number. To do this, I usually concatenate the 3 fields in Excel then import to Access. Then I join the concatenated fields in a query.

I was recently told, however, that joining/linking these 3 fields would achieve this end without all the work? Does anyone have any information?

Thank you,

Answer 1

If you have 2 tables, both contains fields Account Number, Date of Service and Physician Number,

then you can use equal join to join these 3 fields in 1 query (table1's Account Number join with table2's Accout Number, table 1's Date of Service join with table 2's Date of Service, etc).

The SQL of joining 3 fields means selecting data if table1's account number = table2's account number AND  table1's physician number = table2's phycian number AND ...

Therefore it has the same result as Concatenate

Answer 2

A table's key need not be a single column, it can be multiple columns.  Even if you use an autonumber column as a 'surrogate' primary key there may well be other columns in the table which in combination constitute a 'candidate key'.  The image below shows the model for one of my on-line demo files.  You'll see how the tables in many cases have composite keys which are made up of a number of foreign keys.  Some of the foreign keys are themselves made up of more than one column, referencing the composite key of another table.

When it comes to querying the tables are joined in the same way, e.g. to return the attendances per student per courses within an optional date range entered as parameters, a query could be like this:

PARAMETERS [Enter start date:] DATETIME, [Enter end date:] DATETIME;

SELECT [FirstName] & " " & [LastName] AS FullName, Students.StudentID, Courses.CourseName, DATEVALUE(MIN(CourseSessions.CourseDate)) AS StartDate,

COUNT(*) AS Attendances

FROM (Courses INNER JOIN CourseSessions

ON Courses.CourseID = CourseSessions.CourseID)

INNER JOIN ((Students INNER JOIN CourseRegistrations

ON Students.StudentID = CourseRegistrations.StudentID)

INNER JOIN CourseAttendances

ON (CourseRegistrations.CourseID = CourseAttendances.CourseID)

AND (CourseRegistrations.StudentID = CourseAttendances.StudentID))

ON (CourseSessions.CourseID = CourseAttendances.CourseID)

AND (CourseSessions.CourseDate = CourseAttendances.CourseDate)

WHERE (CourseSessions.CourseDate >= [Enter start date:]

OR [Enter start date:] IS NULL)

AND (CourseSessions.CourseDate < [Enter end date:]+1

OR  [Enter end date:] IS NULL)

GROUP BY Students.StudentID, [FirstName] & " " & [LastName], Courses.CourseName;

In design view the above query would show the tables in exactly the same way as in the above image of the relationships window, with multiple join lines between the tables.