0

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.

Hughes
  • 339
  • 3
  • 15

2 Answers2

1

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
KumarHarsh
  • 5,046
  • 1
  • 18
  • 22
  • Does MS Access supports CTE? – Crono Feb 25 '14 at 17:47
  • no sorry,MS Access do not support any othe feature above.dd you want them in MS Access ? – KumarHarsh Feb 25 '14 at 17:53
  • To my understanding Access does not support CTE for queries. I am not very familiar with VBA, but I stumbled upon this link http://stackoverflow.com/questions/9217234/concatenating-multiple-rows-into-single-line-in-ms-access would this work? – Hughes Feb 27 '14 at 14:05
0

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.

Hughes
  • 339
  • 3
  • 15