Columns -Vs- Rows

[cranebill]

I am having a little trouble here, so please bear with me as i try to give a good explanation to tell what i am trying to do.

What i am basically trying to do is build a time line which when certain dates are entered it autofills other dates. What i have done is created a form that is based on a query. The query is based on a project table. The table is set up by having several columns such as task1, task1 complete, task2, task2 complete, ...etc. There are about 50 of these items. Through the form i can update each tasks dates very easily. That is not the problem, the problem lies in the fact i am wanting to generate a daily report based on the dates that i have entered into the form and whether or not the item was completed, like a daily to do list. I can then set the criteria of the query the "daily report" is generated from on 100+ items....(which wont work) some based on comparing date due with todays date, and others whether or not the task was completed. I have thought that if i can make the columns into rows, it would be alot easier on the report issue for in a subform i can list only teh records that are due and not completed and i can also add items that arent in the normal spectrum of the project. Now the question would be how to update the dates on the items if they are rows from my form. I can bind textboxes to columns, but not rows(not that i know of anyway). Another thought i had was to have items added to the "toDo" list automatically everytime i modify a date on my form. Another thing that im not sure how to accomplish. Any ideas, or suggestions are welcome and appreciated. Hopefully this was enough explanation.

Thanx,
Bill

 

[beetee]

I think what you really want to do is normalize your database a bit.

One table might be the project table, which will have a unique id key (say an autonumber).

A second table would be a task table. A task would consist of a project ID, task information (e.g. name, duration, date started, expected completion date, actual completion date).

You would then set up a TasksInProjects table that would have a TaskID and a ProjectID as the primary key.

Additionally, you may want to set up something like a resource table (ResourceName, ResourceID) and a ResourceToTaskTable so that you can associate multiple resources to a task.

At that point, it should be a simple matter to set up any queries you need.

Use the relationships tool to define the connections between tables to guarantee that an record that is being referred to elsewhere may not be deleted or have it's key changed (or cascade changes/deletes to the referring tables).


Back to your original question, it will now be a simple matter to query the tasks table to note which items are complete or not.

HTH

 

[cranebill]

Well in essence that is what i have done. I have a customer table, linked to a project information table, linked to a task list table, linked to a notes table. I can run queries to enter different things yes. There are set tasks in the task list table which have dates generated from the form. Now when i go to make a daily report is where i am having problems. To many criteria items. For Example i want to generate a report of things that are due form today back and have not been completed so i would have to do as follows as it is now:

task1 | task1complete | task2 | task2complete | etc...

Then comes the criteria for each of these items which task1 would be from today back and task1complete would be yes/no... now do this 50 times and access throws a fit. Also i got to thinking about it and it would be difficult to per say organise on the report.

So i was thinking of this:

Task | Complete
task1 Yes
Task2 No

You get the picture im sure but how do you have a textbox reference a specific row to generate a date for the items like i have it doing with columns(fields) or how based off the form can i add records to a to do list table?

Bill (sounding retarded at this point)

 

[beetee]

No, not retarded at all! I'm sorry I misunderstood your fine design.

I agree that you will probably run into trouble trying to make rows to columns.

A suggestion:

Make a report with two subreports: complete tasks and incomplete tasks. Create temp tables to hold 'n' columns of task names, one for complete, one for incomplete, e.g.

CompleteTasks
Task1
Task2
Task3
...
Taskn

Base the subreport on those two temp tables. Populate the temp tables with VB code.. roughly

Dim DestRst as recordset
set DestRst = currentdb.openrecordset("CompleteTasks&quot

Dim MyRst As Recordset
Set MyRst = CurrentDb.OpenRecordset(&quot;Select * from <TheTable> where <Task Completion Criteria>&quot
MyRst.MoveFirst
dim ColumnNumber as integer
ColumnNumber = 1
Do While Not MyRst.EOF
if ColumnNumber = 1 then
DestRst.AddNew
endif
DestRst(&quot;Task&quot; & ColumnNumber) = MyRst(&quot;TaskName&quot
if ColumnNumber = n then ' n is the number of columns
DestRst.update
ColumnNumber = 1
endif
ColumnNumber = ColumnNumber + 1
MyRst.MoveNext
Loop
if ColumnNumber <> 2 then DestRst.Update
DestRst.close
MyRst.close


Pardon the sketchiness of the answer. Perhaps it will lead you in a good direction, perhaps it is inappropriate.
HTH

 

[cranebill]

Well this is what i came up with... posting it so it may help others.

Dim PID As String
Dim CD As String
Dim CDDD As String
Dim AD As String
Dim ADDD As String

PID = Me.ProjectID
CD = &quot;Clearance Drawings Due&quot;
CDDD = Me.ClearanceDrawings
AD = &quot;Approval Drawings Due&quot;
ADDD = Me.ApprovalDwgs

DoCmd.Close acForm, &quot;New Project&quot;, acSaveNo

Set Db = CurrentDb()
Set rs = Db.OpenRecordset(&quot;SELECT ProjectID, ToDo, DueDate FROM ToDo;&quot
rs.AddNew
rs.ProjectID = PID
rs.ToDo = CD
rs.DueDate = CDDD
rs.Update
rs.AddNew
rs.ProjectID = PID
rs.ToDo = AD
rs.DueDate = ADDD
rs.Update
rs.Close
Set rs = Nothing
Set Db = Nothing

DoCmd.OpenForm &quot;Switchboard&quot;, acNormal, , , acFormReadOnly, acWindowNormal
DoCmd.SetWarnings WarningsOn
End Sub

Bill