C# MS Access query to return string aggregate

Question

I have a One-to-Many relationship in my database between the [Customer] and [Car] tables where one customer may have zero or more cars.

My project requires me to output a listing of all customers with a single column showing the vehicle registration(s), [VehicleReg], from the cars. Where a customer has multiple cars all their the vehicle registrations must be shown separated by commas. Where a customer has no cars the column should be blank.

This is the basis of the query but I can't work out how to return the registration numbers.

SELECT [Customer].[FirstName], [CustomerLastName], COMMA SEPARATED VEHICLE REGS FROM [Customer] LEFT JOIN [Car] ON [Customer].[CustomerId] = [Car].[fkCustomerId]

The output I am looking for would be like this

FirstName   | LastName    | VehicleRegistrations
-------------------------------------------------
John        | Smith       | MY51 4RT
Joe         | Mason       | MU08 5TH
Connor      | Norman      |
Graham      | Naughton    | HT09 6TY, HT11 8UQ
Lilly       | Adams       | JK55 8HY

I am using a MS Access 1997 database, C# and .NET 4.0.

Performance isn't an major issue at this stage.

The only working solution I have so far is to save the vehicle registrations into a column in the [Customer] table. While this will work it will involve manually keeping the new column in sync with any changes made to the vehicle registrations in the [Car] table, something that isn't too difficult but may be a dangerous approach once I pass the project on to other to maintain.

Many Thanks

Try this it it helps, `http://stackoverflow.com/a/1048235/405673` — Furqan Hameedi, Jun 26 '12 at 14:16
where are you outputting this data? on a datagridview on a win form application? — nicholas, Jun 26 '12 at 14:31
@nicholas The data is loaded into a list of custom entities and bound to a grid (Infragistics UltraWinGrid) — user1085489, Jun 26 '12 at 14:37

score 1 · Answer 1 · answered Jun 26 '12 at 14:36

1

You need a helper function in order to achieve this

Public Function JoinFromRecordset( _
    DataSource As String, Optional Delimiter As String = ";", _
    Optional Columns As Long = 1) As String

    Dim db As DAO.Database, rs As DAO.Recordset, s As String, col As Long

    Set db = CurrentDb
    Set rs = db.OpenRecordset(DataSource, dbOpenForwardOnly)
    Do Until rs.EOF
        For col = 0 To Columns - 1
            If s = "" Then
                s = Nz(rs(col))
            Else
                s = s & Delimiter & Nz(rs(col))
            End If
        Next col
        rs.MoveNext
    Loop
    rs.Close: Set rs = Nothing
    db.Close: Set db = Nothing
    JoinFromRecordset = s
End Function

Now you can write

SELECT FirstName, CustomerLastName,
    JoinFromRecordset('SELECT VehicleReg FROM Car WHERE fkCustomerId=' & 
        CustomerId) As VehicleRegistrations
FROM Customer

answered Jun 26 '12 at 14:36

Olivier Jacot-Descombes

104,806
13
138
188

great suggestion. just checking, the user intends to run the select query from a .NET application. presumably this means the function is defined in the mdb, and will the table adapter be able to invoke the custom function? – nicholas Jun 26 '12 at 14:47
Sorry, I missed that. My suggestion works only in Access applications. – Olivier Jacot-Descombes Jun 26 '12 at 15:03
+1; was looking for a more elegant solution than a `DConcat` function I had written. I like the control this gives you. It can also be written with ADODB::Recordset::GetString() – transistor1 Nov 29 '12 at 19:46

score 0 · Answer 2 · answered Jun 26 '12 at 14:58

I would keep the database as it is and run the report manipulation in your application, something similar to how I've done it below, using the Northwind database and joining Customers to Orders, with Order Id taking the place of your reg number.

Create two classes:

public class Customer
{
    public string Name { get; set; }
    public IEnumerable<Order> Orders { get; set; }
}

public class Order
{
    public int Id { get; set; }
}

Create a dataset for your data, with Data tables for Customers and Orders and a relationship between the tables on the foreign key. I used a typed dataset to automate the adapter creation but the same principle applies if you hand-roll.

Create a ViewModel class that gets the data and arranges it.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SO_StringAggregate
{
    public class ViewModel
    {
        public ViewModel()
        {
            // populate the data set
            var dataSet = new NorthwindDataSet();
            using (var customersAdp = new NorthwindDataSetTableAdapters.CustomersTableAdapter())
            using (var ordersAdp = new NorthwindDataSetTableAdapters.OrdersTableAdapter())
            {
                customersAdp.Fill(dataSet.Customers);
                ordersAdp.Fill(dataSet.Orders);
            }

            // populate your domain objects
            var customers = dataSet.Customers.ToArray().Select(cust => new Customer
            {
                Name = cust.Company_Name,
                Orders = cust.GetOrdersRows().Select(order => new Order { Id = order.Order_ID })
            });

            this.Customers = customers;

            // build the report
            StringBuilder report = new StringBuilder();
            string formatString = "{0,-30}|{1}";

            report.Append(string.Format(formatString, "Name", "Order Ids"));
            report.Append(Environment.NewLine);
            Customers.ToList().ForEach(cust => report.AppendLine(string.Format(
                formatString, 
                cust.Name, 
                string.Join(",", cust.Orders.Select(o => o.Id).ToArray()))
                ));

            // display the report
            Report = report.ToString();
        }

        public IEnumerable<Customer> Customers { get; set; }

        public string Report { get; set; }

    }
}

You can then display the data in a view that binds to the Report property.

<Window x:Class="SO_StringAggregate.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" Height="350" Width="525">
    <Grid>
        <TextBox FontFamily="Consolas" HorizontalAlignment="Stretch" VerticalAlignment="Stretch" Text="{Binding Report}" />
    </Grid>
</Window>

Didn't see your UltraWinGrid comment above. In that case, create a JoinedReg property on your Customer class that returns string.Join(",", this.RegistrationNumbers.ToArray()) — Barracoder, Jun 26 '12 at 15:05

C# MS Access query to return string aggregate

2 Answers2