For XML Path - to Concatenate records into View to Join to Primary ID (quickly!) (1 Viewer)

[Rx_]

Plese look this over. Does anyone have suggestions for a better method? Or, have any ideas for a better way to address the objective.

The last For XML Concat example was difficult because it involved a Primary Key (PK) plus two fields that needed to be formatted in a specific way.
This is a very basic (simple) example of creating a SQL View from a single PK and a Text field.
Access Queries columns can use a custom VBA Function to basically group By and Concat the text values into a single line.
Example: Table with PK and A.K.A. Field
Cust_ID A.K.A. Text
1101102 Joe
1101101 Tom
1101102 Joey
1101102 Jo
1101101 Tommy
SQL View Output
1101101 Joe, Joey, Jo
1101102 Tom, Tommy

To write a Query (or View) in MS Access - this can be accomplished by writing a VBA Function and calling it inside the Query. However, there is a loss of efficiency when using SQL Server Linked Tables. The ODBC layer can't understand the custom VBA Function. This leads to a loss of other indexing and SQL Efficiencies when this column is used inside a query with other SQL Table Joins.

It would be more efficient to creat my View with table joins and the field with the Concat on the SQL Server side. Then create the final product as a Linked Table to MS Access. Using a filter on the Linked Table with an Access Query is actually very efficient with the ODBC layer.

In this Example (see attachments) the SQL View will use the PK of ID_Wells and concat the data from the Wells_Lease_DirHz field.

Steps:
1. In hte SQL Server Management Studio Object Explorer - Right Click on the View and Create New View - a new View will appear.
2. The Table can be added to the top, in this case it is Wells_Lease_DirHZ.
3. Dont select anything - go to the SQL payne and enter the code.
Try to substitute your Table and field names.
Note: the P1 and P2 assignments require the full path
Question for later: My Test DB is RegulatoryDBT - if this object is copied from this Production DB to the Test DB - what would happen?
4. Use the ! to run your example and see either the error message or the results below.
5. Once it runs to your satisfaction, then use the SAVE AS to give this view a name.
6. This View can now be joined just like a read-only table to the master Veiw that uses the same PK.
7. Use the mater View (where this example represents a column) as a Linked Table for an MS Access Query. For large recordsets, the time savings will be amazing.

In the Regulation industry, my MSAccess Query to drive reports had 15 fields with VBA Functions to evaluate recordsets up to 750K records that needed to be filtered down to hundreds of records. The concat is the most simple of the functions. However, five of those in a single Access Query can be time consuming.

The downside to this query is "Don't use Reserved XML strings in the data".

SELECT  p1.ID_Wells,
       stuff( (SELECT ',' + Dir_HzPass
               FROM  [RegulatoryDB].[dbo].Wells_Lease_DirHz p2
               WHERE p2.ID_Wells = p1.ID_Wells
               ORDER BY Dir_HzPass
               FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
    ,1,1,'')
 As Wells_Lease_DirHz
 FROM [RegulatoryDB].[dbo].Wells_Lease_DirHz  p1
 Group by ID_Wells;

Search for my other XML Path example on this thread.
These can become complex. Still, they run much faster on SQL Server than pushing the data over ODBC to MSAccess.