CODEWITHSUNDEEP
ms-accessconcatenation
Bill King
Bill King

Reputation: 21

Unable to get Allen Browne's ConcatRelated to work on a simple table

I am trying to use the ConcatRelated function to provide a summary report of the prior day's absences, tardies, and vacations. I have tried several variations and can't seem to get it to work in an Access Query. My table looks as below:

ID A_date    Area        ATV_Shift Associate_Name Absent  Tardy Vacation Reason
-- --------- ----------- --------- -------------- ------  ----- -------- --------------
1  1/11/2015 Asm Kenmore First     Keon Wilson         1                 Sick
2  1/11/2015 Asm Kenmore First     Frank Burns         1                 Doctor
3  1/11/2015 Asm Kenmore Second    Paul Mattocks       1                 FLMA
4  1/11/2015 Decoration  First     Jane Doe            1                 Car Broke Down
5  1/11/2015 Asm Maytag  Second    John Doe            1

I need to make a query that displays the previous days data (Date()-1). The reasons need to be separated by spaces. I can get everything else to sum in a query but I am unable to get the reasons to concat. I have tried following the examples but just cant get it to function. I was only able to get it to work using a simple SQL query but that returned lines for each reason not in one cell.

Upvotes: 0

Views: 456

Answers (1)

HansUp
HansUp

Reputation: 97101

I changed your A_date values to 1/13/2015 and stored those sample data in a table named YourTable. Using that table, this is the output in Access 2010 from the query below.

A_date    SumOfAbsent SumOfTardy SumOfVacation Reasons
--------- ----------- ---------- ------------- -------------------------------
1/13/2015           5                          Car Broke Down Doctor FLMA Sick

SELECT
    y.A_date,
    Sum(y.Absent) AS SumOfAbsent,
    Sum(y.Tardy) AS SumOfTardy,
    Sum(y.Vacation) AS SumOfVacation,
    ConcatRelated(
            'Reason',
            'YourTable',
            'A_date=Date()-1',
            'Reason',
            ' '
        ) AS Reasons
FROM YourTable AS y
WHERE y.A_date = Date()-1
GROUP BY y.A_date;

Upvotes: 1

Related Questions