0

I have an SQL server database hosted on Microsoft Azure with MS Access as the front end. I have written a query in SSMS 18.8 as follows

SELECT p.Full_Citation AS [Full Citation], p.Reference, p.StudyID AS [Study ID], t.RefID AS [Database Reference], p.Primary_Secondary_Source AS [Primary/Secondary], p.Linked_Publications AS [Linked Publications], 
p.Publication_Type AS [Publication Type], STRING_AGG(STUFF(COALESCE(' ' + Trial_Name, '') + COALESCE(' ' + Trial_Number, ''), 1, 1, ''), ', ') AS Trials
FROM FTS_Trials t
INNER JOIN Papers p
ON p.RefID = t.RefID
WHERE p.Project = 'TST_TST_1'
GROUP BY p.Full_Citation, p.Reference, p.StudyID, t.RefID, p.Primary_Secondary_Source, p.Linked_Publications, p.Publication_Type;

This produces the following output:

Full Citation Reference Study ID Database Reference Primary/Secondary Linked Publications Publication Type Trials
1 NULL NULL Testing (2021) TST_TST_1-1-Testing (2021) NULL NULL NULL 85, Example, Test 1
2 NULL NULL Testing (2019) TST_TST_1-2-Testing (2019) NULL NULL NULL Test 2

The problem I am having is that when I try to run this code in SQL view of the queries in MS Access it comes up "Undefined function 'STRING_AGG' in expression.". Can anyone shed some light as to why this works in SSMS but not in Access and am I missing something glaringly obvious?

I have also tried a different approach using only the STUFF() function in a similar manner to the first answer on this question T-SQL Concatenate & group multiple rows into a single row . Again this works fine in SSMS but MS Access is telling me there is a query error in the syntax. In addition this query did not combine Trial_Name and Trial_Number like the one above does. Is there another approach I can take using MS Access?

For a little bit of background the query is going to be attached to a button that the user clicks which ideally needs to show the query and then export it to an Excel file. I have set this up for other queries but wanting to concatenate all trials (Trial_Name + Trial_Number) for a given citation in one row has made this scenario much trickier to solve.

PV_Shannon
  • 1
  • MS Access does not have `string_agg()`, `stuff()`, or really anything particularly helpful for this purpose. My recommendation is to change databases. That said, you can find string aggregation VBA functions online. – Gordon Linoff Mar 08 '21 at 11:43
  • 2
    Use a **Pass-Through query**. There you can use SQL server syntax and features. – Andre Mar 08 '21 at 11:56
  • @GordonLinoff access is merely the front end here, such a situation perhaps coming about by starting out using Access as a db, designing all the forms and reports etc, then realizing that it's an unscalable turd with locking issues abound, so is swapped to fronting a better back end database.. – Caius Jard Mar 08 '21 at 12:25

1 Answers1

0

The notion of "view" in Access has nothing to do with the real notion of VIEWs in SQL. A view in SQL is an object you create inside the database and act as a table. The SQL views can be read but also write (INSERT, UPDATE, DELETE, MERGE...) with some limitations.

So you must create the view inside SQL Server and use it as a table in Access.

SQLpro
  • 3,994
  • 1
  • 6
  • 14