Complex Report. How to procede...

[CMooreJr]

Hey all...need your opinions/help before I even start to tackle this report the "higher ups" want. The report will have the following columns:

Project Specialist (Captured in table "project_manager)

Project Number (Captured in table "project_manager)

Project name (Captured in table "project_manager)

Complexity (Captured in table "project_manager)

Hours (calc of complexity. Complesity 1 is 3 hours, 2 is 4 hours, 3 is 6 hours, 4 is 10 hours)

In-Store Date (Captured in table "project_manager)

Additional Hours

Week of X (i.e. 4/7/2003)

week of X (i.e. 4/14/2003)

week of X (i.e. 4/21/2003)

OK, so most of the info is captured, but

1) How would I calculate the "Hours" field, how do I make complexity 1 autopopulate 3 hours in the hours field?

2) They want weekly columns for 4 months. (20 columns in all) Now it depends on when the report is ran. The dates are Mon-Fri. I can prompt them to input the start date, but how do I add 7 days to calculate the next weekly field? For instance, if the report is ran on 4/7, how can I get the week2 column to be 4/14 automatically?

3) and only 1 more problem. The hours should appear in all weekly columns UNTIL it matches the "In-Store Date". For instance, if a project is due In-Store on 5/9/2003 and is complexity level 1 (3 hours), 3 should autopopulate the weekly fields 4/7, 4/14, 4/21, 4/28, and 5/5.

whew....that is it. From what I see, this is extremely complex. They are making this report in excel now, but want it automated. ANY help would be appreciated. Thanks all!

 

[coco86]

For question 1 you can use the choose() function:

hours:choose([complexity],3,4,5)

this will give

Complexity    Hours
1             3
2             4
3             5

 

[coco86]

For Question 2 use DateAdd()

week2: DateAdd("d",7,

![Date])

You need to do some checking though,when you prompt for start date to see if the date is a SAT or SUN

 

[coco86]

Oh, wait, are you looking for the Dates as column names? If you are, you're going to need to dynamically generate the SQL. You can still use Dateadd() to figure out what the column name is.

So, You're looking at something like this

sqlstring = "select '" & iif ([instoredate] > [startdate],choose([complexity],3,4,5),"&quot & "' as [" & [startdate] & "], '" iif ([instoredate] > datediff("d",7,[startdate]),choose([complexity],3,4,5),"&quot & "' as [" & datediff("d",7,[startdate]) & "] from table;"

you can then set this string to be the recordsource for the report.