Osama Abu Shammala Osama Abu Shammala - 11 days ago 6
SQL Question

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

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:
enter image description here

The desired query result:
enter image description here

MJH MJH
Answer

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
;

unformatted


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
;

formatted