DateDiff Array

[brorick]

I have a table that contains the following fields in an Access table.

projectID (AutoNumber)
projectname (text)
value (number) (A value is assigned that gives weight to a project. This could be 1-100)
employeename (text)
dateassigned (date)
datecompleted(date)

I would like to create an array pulling the projectid, value of each project and applying that to the corresponding months from the dateassigned to the datecompleted. If there is no complete date then the value would be assigned beginning with month(dateassigned)up to month(now()). Of course the results would be inserted into
a table.

Example:
TblProjects
projectid, projectname, value, employeename, dateassigned, datecompleted
1001, Create ADMIN DB, 15, John Smith, 1/1/2006, 5/1/2006
1002, Perform Security Audit, 25, John Smith, 3/5/2006, ,
1003, Test ISD Website, 17, Jane Doe, 2/1/2006, 2/15/2006
1004, Create Crystal Reports, 50, Jane Doe, 1/15/2006, 11/15/2006

End Results
TblProjectValues
projectid, jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec
1001, 15, 15, 15, 15, 15, 0, 0, 0, 0, 0, 0, 0
1002, 0, 0, 25, 25, 25, 25, 25, 25, 25, 25, 25, 25
1003, 0, 17, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
1004, 50, 50, 50, 50 ,50 ,50 ,50 ,50 ,50 ,50 ,50, 0

I thought i could use a datediff in an array to get this to work, but I have not worked with many arrays and i am not sure how to get this to work. Any thoughts.

 

[SkipVought]

Hi,

Have you checked out the CrossSum query or PivotTable?

Skip,

_[red][/red]