How to combine text values from multiple rows into a single cell in Access query

Question

I have a table for added discount or motivation for employee salaries each month in the year. This table contains a field that can contain only the words "Discount" or "Motivation" using lookup.

Every month I can add more than one discount or motivation, so when I want to pay the salaries in the end of the month, I want these data grouped in one row for each employee.

This is the table that takes the discount and motivation during the month. The table name is Table1:

The desired query result:

Have a look here: http://stackoverflow.com/questions/19478272/converting-mysql-code-to-access-group-concat-and-a-triple-join — Tim Biegeleisen, Nov 29 '16 at 09:00

score 0 · Accepted Answer · answered Nov 30 '16 at 00:46

With one exception, the following query provides your desired results. The exception is that the Total_Discount and Total_Motivation columns are not formatted as currency (which may be undesirable, because the formatted columns would be strings instead of numbers).

SELECT t.The_Year
     , t.The_Month
     , t.Emp_Num
     , Nz(DSum("Money_Amount","Table1","Discount_Motivation = 'Discount' AND The_Year = " & t.The_Year & " AND The_Month = " & t.The_Month & " AND Emp_Num = " & t.Emp_Num), 0) AS Total_Discount
     , ConcatRelated("Reason","Table1","Discount_Motivation = 'Discount' AND The_Year = " & t.The_Year & " AND The_Month = " & t.The_Month & " AND Emp_Num = " & t.Emp_Num) AS Discount_Reasons
     , Nz(DSum("Money_Amount","Table1","Discount_Motivation = 'Motivation' AND The_Year = " & t.The_Year & " AND The_Month = " & t.The_Month & " AND Emp_Num = " & t.Emp_Num), 0) AS Total_Motivation
     , ConcatRelated("Reason","Table1","Discount_Motivation = 'Motivation' AND The_Year = " & t.The_Year & " AND The_Month = " & t.The_Month & " AND Emp_Num = " & t.Emp_Num) AS Motivation_Reasons
FROM Table1 t
GROUP BY t.The_Year, t.Emp_Num, t.The_Month
ORDER BY t.The_Year, t.Emp_Num, t.The_Month
;

However, if such formatting really is necessary, you can do so by simply wrapping the lines for those columns in a Format function, and providing "Currency" as the second argument:

SELECT t.The_Year
     , t.The_Month
     , t.Emp_Num
     , Format(Nz(DSum("Money_Amount","Table1","Discount_Motivation = 'Discount' AND The_Year = " & t.The_Year & " AND The_Month = " & t.The_Month & " AND Emp_Num = " & t.Emp_Num), 0), "Currency") AS Total_Discount
     , ConcatRelated("Reason","Table1","Discount_Motivation = 'Discount' AND The_Year = " & t.The_Year & " AND The_Month = " & t.The_Month & " AND Emp_Num = " & t.Emp_Num) AS Discount_Reasons
     , Format(Nz(DSum("Money_Amount","Table1","Discount_Motivation = 'Motivation' AND The_Year = " & t.The_Year & " AND The_Month = " & t.The_Month & " AND Emp_Num = " & t.Emp_Num), 0), "Currency") AS Total_Motivation
     , ConcatRelated("Reason","Table1","Discount_Motivation = 'Motivation' AND The_Year = " & t.The_Year & " AND The_Month = " & t.The_Month & " AND Emp_Num = " & t.Emp_Num) AS Motivation_Reasons
FROM Table1 t
GROUP BY t.The_Year, t.Emp_Num, t.The_Month
ORDER BY t.The_Year, t.Emp_Num, t.The_Month
;

Great, Really thank you so Much, you are the best :) – Osama Abu Shammala Nov 30 '16 at 07:34 — Osama Abu Shammala, Nov 30 '16 at 07:34

How to combine text values from multiple rows into a single cell in Access query

1 Answers1