CODEWITHSUNDEEP
ms-accessms-access-2007
Hughes
Hughes

Reputation: 347

Creating a calender in Access

I have 5 fields in a query.

Employee, Project ID, Year, Month, and Day

I need to put all the query data into a Report where the data is sorted by Year, Month, and Employee, and each Project ID needs to be placed under its appropriate date. 

My Query:

Alice | P555 | 2014 | 11 November | 29
Darth | P999 | 2014 | 12 December | 27
Bob   | P555 | 2014 | 12 December | 28
Darth | P555 | 2014 | 12 December | 29
Alice | P999 | 2014 | 12 December | 29
Bob   | P999 | 2014 | 12 December | 29
Alice | P999 | 2014 | 12 December | 30
Alice | P555 | 2015 |  1 January  |  3
Alice | P999 | 2015 |  1 January  |  4
Darth | P555 | 2015 |  1 January  |  4

It needs to look something like this.

Calendar Report:

2014
   11 November   1 | 2 | ... | 28 |  29  | 30
         Alice     |   | ... |    | P555 | 

   12 December   1 | 2 | ... |  27  |  28  |  29  |  30  | 31
         Alice     |   | ... |      |      | P999 | P999 |   
         Bob       |   | ... |      | P555 | P999 |      | 
         Darth     |   | ... | P999 |      | P555 |      |   

2015
     1 January   1 | 2 |   3  |   4  | 5 | ... | 30
         Alice     |   | P555 | P999 |   | ... | 
         Darth     |   |      | P555 |   | ... |

I am able to create rows with the Year, Month, and Employee, but I do not know how to create the columns or how to add Project ID's once I do create them. Any push in the right direction would be greatly appreciated! :)

Upvotes: 1

Views: 263

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

A crosstab query should get you pretty close to what you want

TRANSFORM First(Calendar.[Project ID]) AS [FirstOfProject ID]
SELECT Calendar.Year, Calendar.Month, Calendar.Employee
FROM Calendar
GROUP BY Calendar.Year, Calendar.Month, Calendar.Employee
PIVOT Calendar.Day;

I do not quite see why you do not just use a datetime field rather than separating out year, month and day.

Upvotes: 1

Related Questions