Having trouble writing a SELECT query

You can't do that, it's a limitation of using excel as a datasource, the sql engine simply isn't comprehensive enough. This isn't trivial even In mssql, sql just isn't designed to do that without hacking around.

To use your terminology, it depends what 'flavour' of SQL the data source supports. AFAIK (though I could well be wrong here) you can only use standard SQL with Excel and Access (at least you could last time I used access 2003!). As you are no doubt aware SQL is designed to fill a specific hole, set based, declarative queries of a relational database. However, as you have found out, traditional vanilla sql is also very simple and quite limiting, as such most database vendors have extended 'base' sql to add additional functionality. Each database then departs from the standard sql syntax and publishes their own superset - T-SQL for MSSQL, PL/SQL for Oracle, SQL/PSM for MySQL etc... By using these databases, you can leverage this functionality.

As way of an example, here's what your concat query would look like in MSSQL:
MSSQL

PHP Code: 

SELECT [tblsong$].id, 
       [tbltitle$].[name], 
       Stuff((SELECT ', ' + Cast(artist.[name] AS VARCHAR(max)) 
              FROM   [tblartist$] artist 
                     INNER JOIN [tbjsongartist$] 
                             ON fkartist = artist.id 
                                AND fksong = song.id 
              FOR xml path(''), type).value('.', 'VARCHAR(MAX)'), 1, 2, '') 
       artists 
FROM   [tblsong$] song 
       LEFT JOIN [tbltitle$] 
              ON [tbltitle$].id = [tblsong$].fktitle 
GROUP  BY [tblsong$].id, 
          [tbltitle$].NAME 


And here it is in MySQL (the Group_concat function makes this much more cogent IMO)

MySQL

PHP Code: 

SELECT [tblsong$].id, 
       [tbltitle$].[name], 
       Group_concat(DISTINCT artist.[name] ORDER BY artist.[name]) AS artists 
FROM   [tblsong$] 
       LEFT JOIN [tbltitle$] 
              ON [tbltitle$].id = [tblsong$].fktitle 
       LEFT JOIN [tbjsongartist$] 
              ON [tbjsongartist$].fksong = [tblsong$].id 
       LEFT JOIN [tblartist$] artist 
              ON [tblartist$].id = [tbjsongartist$].fkartist 
GROUP  BY [tblsong$].id, 
          [tbltitle$].[name] 


I honestly don't know whether Access now offers any extensions to standard SQL, it's been a long time since I've used it - though a quick google suggests that you'd have to write a UDF in VBA to accomplish this particular task.

I too disagree with Norie, were you trying to store your data in this manner then there'd be a de-normalization issue, however there is a big difference between data storage and presentation - I do however find your decision to store the song title in a separate table a step too far in terms of Normalization, are there really that many songs with the same title? Though there may be something that I missed in your design.

To use your terminology, it depends what 'flavour' of SQL the data source supports. AFAIK (though I could well be wrong here) you can only use standard SQL with Excel and Access (at least you could last time I used access 2003!). As you are no doubt aware SQL is designed to fill a specific hole, set based, declarative queries of a relational database. However, as you have found out, traditional vanilla sql is also very simple and quite limiting, as such most database vendors have extended 'base' sql to add additional functionality. Each database then departs from the standard sql syntax and publishes their own superset - T-SQL for MSSQL, PL/SQL for Oracle, SQL/PSM for MySQL etc... By using these databases, you can leverage this functionality.

As way of an example, here's what your concat query would look like in MSSQL:
MSSQL

PHP Code: 

SELECT [tblsong$].id, 
       [tbltitle$].[name], 
       Stuff((SELECT ', ' + Cast(artist.[name] AS VARCHAR(max)) 
              FROM   [tblartist$] artist 
                     INNER JOIN [tbjsongartist$] 
                             ON fkartist = artist.id 
                                AND fksong = song.id 
              FOR xml path(''), type).value('.', 'VARCHAR(MAX)'), 1, 2, '') 
       artists 
FROM   [tblsong$] song 
       LEFT JOIN [tbltitle$] 
              ON [tbltitle$].id = [tblsong$].fktitle 
GROUP  BY [tblsong$].id, 
          [tbltitle$].NAME 


And here it is in MySQL (the Group_concat function makes this much more cogent IMO)

MySQL

PHP Code: 

SELECT [tblsong$].id, 
       [tbltitle$].[name], 
       Group_concat(DISTINCT artist.[name] ORDER BY artist.[name]) AS artists 
FROM   [tblsong$] 
       LEFT JOIN [tbltitle$] 
              ON [tbltitle$].id = [tblsong$].fktitle 
       LEFT JOIN [tbjsongartist$] 
              ON [tbjsongartist$].fksong = [tblsong$].id 
       LEFT JOIN [tblartist$] artist 
              ON [tblartist$].id = [tbjsongartist$].fkartist 
GROUP  BY [tblsong$].id, 
          [tbltitle$].[name] 


I honestly don't know whether Access now offers any extensions to standard SQL, it's been a long time since I've used it - though a quick google suggests that you'd have to write a UDF in VBA to accomplish this particular task.

I too disagree with Norie, were you trying to store your data in this manner then there'd be a de-normalization issue, however there is a big difference between data storage and presentation - I do however find your decision to store the song title in a separate table a step too far in terms of Normalization, are there really that many songs with the same title? Though there may be something that I missed in your design.

To use your terminology, it depends what 'flavour' of SQL the data source supports. AFAIK (though I could well be wrong here) you can only use standard SQL with Excel and Access (at least you could last time I used access 2003!). As you are no doubt aware SQL is designed to fill a specific hole, set based, declarative queries of a relational database. However, as you have found out, traditional vanilla sql is also very simple and quite limiting, as such most database vendors have extended 'base' sql to add additional functionality. Each database then departs from the standard sql syntax and publishes their own superset - T-SQL for MSSQL, PL/SQL for Oracle, SQL/PSM for MySQL etc... By using these databases, you can leverage this functionality.

As way of an example, here's what your concat query would look like in MSSQL:
MSSQL

PHP Code: 

SELECT [tblsong$].id, 
       [tbltitle$].[name], 
       Stuff((SELECT ', ' + Cast(artist.[name] AS VARCHAR(max)) 
              FROM   [tblartist$] artist 
                     INNER JOIN [tbjsongartist$] 
                             ON fkartist = artist.id 
                                AND fksong = song.id 
              FOR xml path(''), type).value('.', 'VARCHAR(MAX)'), 1, 2, '') 
       artists 
FROM   [tblsong$] song 
       LEFT JOIN [tbltitle$] 
              ON [tbltitle$].id = [tblsong$].fktitle 
GROUP  BY [tblsong$].id, 
          [tbltitle$].NAME 


And here it is in MySQL (the Group_concat function makes this much more cogent IMO)

MySQL

PHP Code: 

SELECT [tblsong$].id, 
       [tbltitle$].[name], 
       Group_concat(DISTINCT artist.[name] ORDER BY artist.[name]) AS artists 
FROM   [tblsong$] 
       LEFT JOIN [tbltitle$] 
              ON [tbltitle$].id = [tblsong$].fktitle 
       LEFT JOIN [tbjsongartist$] 
              ON [tbjsongartist$].fksong = [tblsong$].id 
       LEFT JOIN [tblartist$] artist 
              ON [tblartist$].id = [tbjsongartist$].fkartist 
GROUP  BY [tblsong$].id, 
          [tbltitle$].[name] 


I honestly don't know whether Access now offers any extensions to standard SQL, it's been a long time since I've used it - though a quick google suggests that you'd have to write a UDF in VBA to accomplish this particular task.

I too disagree with Norie, were you trying to store your data in this manner then there'd be a de-normalization issue, however there is a big difference between data storage and presentation - I do however find your decision to store the song title in a separate table a step too far in terms of Normalization, are there really that many songs with the same title? Though there may be something that I missed in your design.