CODEWITHSUNDEEP
sqlms-accessms-access-2007
Hughes
Hughes

Reputation: 347

Access Concatenating Values in a Query

I have a query, [Query1], with employee names, projects, days, months, and years.

In another query, [Query2], I take all the values and put them into a cross table. My rows are "Year, Month, Employee." My column is "Day." My values are Projects.

The problem is that for one date, there may be more than one project assigned to an employee.

When I attempt to put the projects as values into a table using IIf(Count(*)>0,[Project],""), I get an error because there may be more than one possible value for the project, and access doesn't know which one to choose.

I need a way to Concatenate the values if there is more than one Project.

Ex: 

[Query1]
Bill |  CC555  |  28  |  03  |  2014
Jim  |  CC999  |  29  |  03  |  2014
Jim  |  CC555  |  29  |  03  |  2014
John |  CC555  |  29  |  03  |  2014

[Query2]
Year  | Month | Employee | 1 | 2 | 3 | ... | 27 |  28   |      29       | 30 | 31 
2014  |  03   |   Bill   | - | - | - | ... | -  | CC555 |      -        | -  | -
2014  |  03   |   Jim    | - | - | - | ... | -  |   -   | CC555 + CC999 | -  | -
2014  |  03   |   John   | - | - | - | ... | -  |   -   |     CC555     | -  | -

Aside: [Query1] is dynamic and could have duplicate dates deleted or added to it, so [Query2] values must change accordingly.

Upvotes: 0

Views: 146

Answers (2)

Hughes
Hughes

Reputation: 347

By using the function given here allenbrowne.com/func-concat.html, and following the example given here http://www.access-programmers.co.uk/forums/showthread.php?t=234291, I was able to solve the problem.

Upvotes: 0

KumarHarsh
KumarHarsh

Reputation: 5094

one simple example,you have to make it dynamic,in real scenrio no need of table variable or CTE if using dynamic sql.i think no need of dynamic,just hard code from 1 to 31

;With CTE as
(
select   'Bill' Employee ,'CC555' codes,28 dd,03 mm ,2014 yrs union all
select  'Jim ','CC999',  29 ,  03 ,  2014  union all
select  'Jim ','CC555',  29 ,  03 ,  2014  union all
select  'John','CC555',  29 ,  03 , 2014
)
select yrs,mm,Employee,isnull([28],'-')[28],[29],[30] from
(select Employee,dd,mm,yrs 
,stuff((select ','+codes from cte b where b.Employee=a.Employee for xml path('')),1,1,'')codes
from cte a ) src
pivot (min(codes) for dd in([28],[29],[30])) pvt

Upvotes: 1

Related Questions