无法获得艾伦·布朗的《 Concat》有关在简单桌子上工作 [英] Unable to get Allen Browne's ConcatRelated to work on a simple table

问题描述

我正在尝试使用 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 值更改为2015年1月13日，并将这些示例数据存储在名为 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;

这篇关于无法获得艾伦·布朗的《 Concat》有关在简单桌子上工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！