mapping dates into a weekly column table (1 Viewer)

[sha7jpm]

I reviously posted a query, but have changed tack ab it on how I am going to do this so have deleted that and started afresh..

I have a table which has project description as column1 and then many columns after it with the start date of each week between now and the end of 2005.

I then have a form where the user types in against a set timetable the dates they expect each task to be performed...

What I want is to place in the table the task description and the date in the correct weekly column...

i.e. column 26/10/2005
send out letter1, 28/10/2005

or something like this....
I know this seems clumsy but it is the only way I can see to solve this problem..

any pointers would be most useful

ta

John

 

[Pat Hartman]

You seem to have set up your table as if it were a spreadsheet. What are you planning on doing next year? Add more columns? Relational tables are very different from spreadsheets and we use different techniques for working with them. In general, repeating groups that continually require new columns to be added are pivoted so that instead of columns, you end up adding rows. Adding columns requires you to change your queries, forms, and reports. Adding rows causes no changes at all. So before going further, you need to rethink your table design. Here's an article to get you thinking along the correct lines.
Database Normalization Fundamentals

 

[sha7jpm]

dont worry Pat,

I have not gone completely mad...

it was a last resort action to try and solve this task which is almost like recreating a MS Project GANT chart from an access table..

the table is stored presently in NF, but it would not link itself to a report of this nature..

BUT my oclleague and I seem to have found a way, by looping through the table and we seem to be on the way to solving this..

will post up the code when it is finished.. but fingers crossed it si working well and should be a small nifty section of code that will grab a table which has projectdescription as first column with dates stored against key stages, and turns the table into a report which has weekly interval dates along the top with the key stages reported as the data... a bit like MS outlook meeting planner...

fingers crossed of course!

John

 

[Pat Hartman]

You may be able to use a Crosstab to pivot the data for the report. There is a sample report made from a variable crosstab in the solutions database or in the reports sample database available from the Microsoft Download site.