Possible to concatenate multiple records grouped by date? (AC2007)

[AOB]

Hi guys,

I have a rates table with 3 fields : [RateDate], [CurrencyID], [FXRate]

I also have a currencies table with 2 fields : [CurrencyID], [CurrencyCode]

Where [CurrencyCode] is just the 3-character currency code (i.e. EUR, GBP, USD etc.)

The rates table uses a composite primary key across [RateDate] and [CurrencyID] - i.e., there should only be one unique record for each combination of date and currency.

Not every date will be present in the table and, within each of those dates, not every currency will be present (but at least one, obviously)

I want to write a query which will return two fields. The first field will contain all of the unique dates in the table for which the [FXRate] field (for any currency) is blank / null. Straightforward enough :

SELECT [tblRates].[RateDate]
FROM [tblRates]
    INNER JOIN [tblCurrencies]
        ON [tblRates].[CurrencyID] = [tblCurrencies].[CurrencyID]
WHERE [tblRates].[FXRate] Is Null
GROUP BY [tblRates].[RateDate]

The tricky bit is the second field I want to return. Namely, a concatenated string of all the currency codes which are missing the rates within that date. It's purely for reporting purposes, so I can see at a glance which rates are missing for which currencies and on which dates.

I can get the query to return the currency codes :

SELECT [tblRates].[RateDate][COLOR=red], [tblCurrencies].[CurrencyCode][/COLOR]
FROM [tblRates]
    INNER JOIN [tblCurrencies]
        ON [tblRates].[CurrencyID] = [tblCurrencies].[CurrencyID]
WHERE [tblRates].[FXRate] Is Null
GROUP BY [tblRates].[[COLOR=black]RateDate][/COLOR][COLOR=red], [tblCurrencies].[CurrencyCode][/COLOR]

But this gives me a separate record for each date / currency combination in the returned dataset. I just want one record per date and all of the currency codes in a single string (separated by some delimiter) as a text field.

Is this possible and how would one go about it?

Thanks!

AOB

 

[pbaldy]

Something like this?

http://allenbrowne.com/func-concat.html

 

[AOB]

Something like that, yes... - thanks Paul!

 

[pbaldy]

No problem!