System Requirements:
- Microsoft Access 97, 2000, 2002 (XP), 2003, 2007
The Problem:
If you have a one to many relationship in your database and you want to query the following (rudimentary and fairly poor) information:
table_Year
| ID | YearCode |
|---|---|
| 1 | 2008 |
| 2 | 2009 |
| 3 | 2010 |
table_Semster
| YearCode | Semester |
|---|---|
| 2008 | A |
| 2009 | A |
| 2009 | B |
| 2010 | A |
| 2010 | B |
| 2010 | C |
The query of:
SELECT table_Semester.YearCode, table_Semester.Semester FROM table_Semester WHERE table_Semester.YearCode = table_Year.YearCode AND table_Year.ID = 2;
Returns:
| YearCode | Semester |
|---|---|
| 2009 | A |
| B |
But what if you (for some reason, the why here is your own business) want to return:
| YearCode | Semester |
|---|---|
| 2009 | A,B |
or even just
2009 AB
More Information:
Larger database management systems such as MySQL, Oracle and Microsoft SQL Server can utilise built-in functions, stored procedures or cursor based logic to create a string from multiple rows, however Access is not powerful enough to support this.
However, it is possible (although not necessarily ideal) to retrieve a single string value from a one-to-many record set in access under one condition:
It only works IF there is a finite and definable number of “many” values.
Take the example above. An academic year has three semesters. Semester A, B and C. We have three values which we can enumerate as being true. As a consequence we can create a concatenated string output using a SQL SELECT statement similar to the following:
SELECT DISTINCT (
SELECT tblYear.YearCode, table_Semester.SemesterCode
FROM table_Year, table_Semester
WHERE (((table_Year.YearCode = table_Semester.Yearcode AND table_Year.ID = tblYear.ID AND SemesterCode=’A’)
) & (
SELECT table_Semester.SemesterCode
FROM table_Year, table_Semester
WHERE (((table_Year.YearCode = table_Semester.Yearcode AND table_Year.ID = tblYear.ID AND SemesterCode=’B’)
) & (
SELECT table_Semester.SemesterCode
FROM table_Year, table_Semester
WHERE (((table_Year.YearCode = table_Semester.Yearcode AND table_Year.ID = tblYear.ID AND SemesterCode=’C’)
) AS strSemester
FROM table_Year tblYear
WHERE tblYear.ID = 1 ;
What we are doing here is defining that there are three known options, searching for each option against the record sets join. Note the redefinition of table_Year as tblYear.
Each sub query returns a single permissible value from the many relationship and concatenates it to a string named “strSemester” (using the & character). The result of executing the query above would be:
| YearCode | Semester |
|---|---|
| 2009 | AB |
If you wanted comma’s to separate the Semesters you would have to use
) & “, ” & (
This on its own would of course return excess commas on the example above, i.e.
| YearCode | Semester |
|---|---|
| 2009 | A,B, |
Now, I can hear database administrators across the planet groaning right now, and yes I concur… but like I said above, the “why” that prompted you to do this is your own business. So I have no qualms in making this worse than it already is.
If you wanted to return the above without the stray comma’s at the end you would have to augment the SQL statement thusly:
SELECT DISTINCT (
SELECT tblYear.YearCode, table_Semester.SemesterCode
FROM table_Year, table_Semester
WHERE (((table_Year.YearCode = table_Semester.Yearcode AND table_Year.ID = tblYear.ID AND SemesterCode=’A’)
) & (SELECT “, ” as strComma FROM Faculty WHERE (
SELECT table_Semester.SemesterCode
FROM table_Year, table_Semester
WHERE (((table_Year.YearCode = table_Semester.Yearcode AND table_Year.ID = tblYear.ID AND SemesterCode=’A’)) <> NULL;
) & (
SELECT table_Semester.SemesterCode
FROM table_Year, table_Semester
WHERE (((table_Year.YearCode = table_Semester.Yearcode AND table_Year.ID = tblYear.ID AND SemesterCode=’B’)
) & (SELECT “, ” as strComma FROM Faculty WHERE (
SELECT table_Semester.SemesterCode
FROM table_Year, table_Semester
WHERE (((table_Year.YearCode = table_Semester.Yearcode AND table_Year.ID = tblYear.ID AND SemesterCode=’C’)) <> NULL;
) & (
SELECT table_Semester.SemesterCode
FROM table_Year, table_Semester
WHERE (((table_Year.YearCode = table_Semester.Yearcode AND table_Year.ID = tblYear.ID AND SemesterCode=’C’)
) AS strSemester
FROM table_Year tblYear
WHERE tblYear.ID = 1 ;
The horror of the above isn’t as complicated as it looks. We test for Semester A where the ID = 1 and send the result OR Null to the string. We then test it again, but this time we only send a result – the comma – back when the result is not Null, that means that if there is an instance of A the result is written out as:
A,
Repeat for B, but this time only if there is an instance of C the result is written out as:
B,
Giving us a not quite flawless answer (more tests are needed to cover every combination).For the example however the result would be:
| YearCode | Semester |
|---|---|
| 2009 | A,B |
With no trailing comma!