How can I do a SQL inner join where a value in ANY column matches?

Question

I have two tables in an Access database. One table is an attendance table and the other is a students table. The attendance table contains dates and student ID's. ID is the primary key in the students table and attendanceDate is the pk in the attendance table. What I want is a query that will return dates and student names so I can display them back to the user. The complication is that the Attendance table contains up to 75 student ID's for each date, so if I do a standard inner join it would have to contain 75 "OR" clauses which is quite cumbersome, and Access will only allow twenty something inner joins anyhow. Is there a way I can do something like this:

SELECT a.attendanceDate FROM (Attendance as a WHERE a.[attendanceDate] BETWEEN #01/01/2020# AND #01/31/2020# INNER JOIN Students AS s ON s.[studentID] = a.[ANY_COLUMN]);

In this example it should return attendance records for the month of January 2020 something like this:

01/02/2020 'Mike Johnson'

01/10/2020 'Mike Johnson', 'Mary Smith'

01/25/2020 'Mary Smith', 'Chad Jones', 'Kyrie Erving'

Attendance has 75 columns for StudentsID? That would be poor data model. — ComputerVersteher, Jan 31 '20 at 08:40
I agree with ComputerVersteher that your problem is the inappropriate data model. You should change it. Otherwise many of your queries will have to be "cumbersome" (and slow for that matter). — Thorsten Kettner, Jan 31 '20 at 08:43
And as to your results: Is `'Mary Smith', 'Chad Jones', 'Kyrie Erving'` supposed to be one string? As far as I know, MS-Access features no string aggregation, so you will have to do this in your app (VBA-Script?) and only select atomic data instead (`01/25/2020 'Mary Smith'`, `01/25/2020 'Chad Jones'`, `01/25/2020 'Kyrie Erving'`). — Thorsten Kettner, Jan 31 '20 at 08:49
The return values should be in the form of a record set or table — SendETHToThisAddress, Feb 06 '20 at 07:33

ComputerVersteher · Answer 1 · 2020-01-31T10:32:04.750

Quickfix: Create a form with Attendance as Recordsource and create a Combobox for each Student column withSelect id, Studentname From Studentsas datasource,bound column = 1 and width column1 =0. That will show all students, one column each.

If you want to have the students as one separated string, replace id with name and concat all columns to one:

SELECT
    attendanceDate,
    Dlookup("Studentname","Students","id = " & StudentID1) & "," &
    Dlookup("Studentname","Students","id = " & StudentID2 ) & "," &
    ... 
    Dlookup("Studentname","Students","id = " & StudentID75 ) AS AllStudents
FROM
    Attendance

But fix your data model ASAP!

As @Thorsten Kettner noted: two fields, one for date, one for studentid. PK is now the combination of date and id.

You can combine the rows with same date by using GetList in a query.

How can I do a SQL inner join where a value in ANY column matches?

1 Answers1