无法让 Allen Browne 的 ConcatRelated 在一张简单的桌子上工作

问题描述

我正在尝试使用 ConcatRelated 函数 来提供前一天缺勤的总结报告、迟到和假期.我尝试了几种变体，但似乎无法在 Access Query 中使用它.我的表格如下所示:

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

我需要查询显示前几天的数据 (Date()-1).原因需要用空格隔开.我可以在查询中总结所有其他内容，但我无法找到连接的原因.我已经尝试按照示例进行操作，但无法使其正常运行.我只能使用一个简单的 SQL 查询来让它工作，但是每个原因都返回了行，而不是在一个单元格中.

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.

推荐答案

我将您的 A_date 值更改为 1/13/2015 并将这些示例数据存储在名为 YourTable.使用该表，这是以下查询在 Access 2010 中的输出.

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;

这篇关于无法让 Allen Browne 的 ConcatRelated 在一张简单的桌子上工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！