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. :dunno Any thoughts?

[OBP]

Hello again, sorry from the first part I thought I new what you wanted to do, but from the TblProject Values I haven't got a clue.
Can you tell me what you want the final outcome to be?
Why do you want to reformat the data in this way?
Perhaps if I new what the final output (report, query) was going to be I might be able to help.

[brorick]

Hello OBP. The user would prefer a crosstab view. I know that a crosstab query can be manipulated to get close to this end result, but an array will serve many other purposes down the road. I have attached a copy of an excel workbook. The first tab includes all of the raw data and the second tab provides a layout of how the results table should look. You may notice that I only included a few records on the second tab to just show as an example. But the end result would parse out all of the data. Thanks for you help.

[OBP]

That definitely looks like a crosstab application to me.
Which date field generates the "Month" in the headings?
I am not sure why you think an array would be better as an array is great in memory but not easy to display to the user.

Have you asked the user if they would like a "Ghantt" chart to display the project progress as that is very good at showing "process activity" or are they only interested in the "value" being shown?

[brorick]

OBP, thank you for your help. I have just received word that this project has been placed on hold. Presently there is no need to pursue the option of an array.

However I do agree with your assessment. Before I was notified of the status on this project I began researching the option of utilizing a crosstab query with some If Then Else statements. Oh well, I will save it for a later date. Thanks again.

[stanl]

If the project ever goes live again, you might want to look into a 'TRANSFORM... PIVOT' Access query that inserts the results into an Excel Sheet as a crosstab.

.02 Stan

[brorick]

Stanl, thank you for the suggestion. I will look into it.