Gant Chart type display

[petro62]

Run Compact & Repair, zip with Windows Compression? Still too big? Some will not download from other sites. However, I had no problem.



I don't understand tblSchedulePlan and tblSchedulePlanPhase.
If the first is to replace fields in tblProjectDetails, why is there no EndDate field? If you want the start date of later phase to be end date of earlier phase (as I questioned before), it can be done but the SQL is tricky and calls for nested query.

Your form still has phase fields from tblProjectDetails. If you now want this info as records in a related dependent table (tblSchedulePlan), use a form/subform/subsubform arrangement.

And why not use tblPhase for combobox lookup instead of creating tblSchedulePlanPhase?
Most people would not assign an ID of 0.

All valid points. the tblSchedulePlanPhase was in the original so I used it trying to minimize how much I changed it knowing that would mean I would need to have even more changes in the SQL which I am even less familiar with. Honestly since I am not having the user select or type the Phase in the Milestones section then I think tblSchedulePlanPhase could be removed.

So on tblProjectDetails the third column for PhaseID is the current phase they are in. So they just manually change that throughout the project. Right or wrong that is how it is different than the milestone phases where they enter the dates. The alternative could be possibly having that just display the phase based on the dates they entered.

I have been working on too many version of this and I think I saved over the one where I used the UNION select code you showed me to calculate the end date. It seems that may be a bit more difficult with the way tblSchedulePlan is set up.

[June7]

Yes, calculating EndDate would be trickier, as I noted.

UNION query is in post #20.

It is a balancing act between normalization and ease of data entry/output - "Normalize until it hurts, denormalize until it works."

[petro62]

Yes, calculating EndDate would be trickier, as I noted.

UNION query is in post #20.

It is a balancing act between normalization and ease of data entry/output - "Normalize until it hurts, denormalize until it works."File Upload Manager

-Manage all files that you have uploaded

Yeah you are right. I will just add in entry fields for the end date. No reason to make this any harder than it needs to be. I just want to get it working. I added a new blank file where I created the EndDate in tblSchedPlan. I also use tblPhase now for both tblProjDetails and tblSchedPlan and got rid of the tblSchePlanPhase. So now I just need to know how to take a date entered into Feasbox from frmProjects and put it into tblSchedulePlan with dates entered into their columns and with the proper PhaseID.

[June7]

If you want to proceed with modifying data structure, consider: for data entry, I suggested a subform in place of the Milestones section on frmProjectDetailsSubform. This means a form/subform/subsubform arrangement. Use a combobox for selection of Phase and textboxes for input of dates. Lookup field in table still uses tblSchedulePlanPhase as source. I advise not to build lookups in tables.

Regardless of whether dataset is constructed with tblSchedulePlan or UNION query, code to manipulate for Gantt chart will be same, as modified for your weekly requirement. I see you have code so now test and debug.

[petro62]

If you want to proceed with modifying data structure, consider: for data entry, I suggested a subform in place of the Milestones section on frmProjectDetailsSubform. This means a form/subform/subsubform arrangement. Use a combobox for selection of Phase and textboxes for input of dates. Lookup field in table still uses tblSchedulePlanPhase as source. I advise not to build lookups in tables.

Regardless of whether dataset is constructed with tblSchedulePlan or UNION query, code to manipulate for Gantt chart will be same, as modified for your weekly requirement. I see you have code so now test and debug.

So either way they will have to actually pick the milestone from a drop down box? There isn’t a way to just have milestones listed there since each project requires the same? I guess that is what I mostly was trying to avoid but it seems that may not be a feasible goal.

[June7]

Your current structure does not require choosing milestone from dropdown - they enter date into appropriate milestone field of tblProjectDetails.

If you change data structure to use normalized milestone table, yes, select milestone from combobox. Or build code that creates a set of milestone records when a project detail record is created. Then user opens form filtered to that set of records and just enters date into appropriate record. Also, tblMilestones can probably be linked directly to tblProject instead of tblProjectDetails. Since tblProject and tblProjectDetails appear to have a one-to-one relationship, why not just have one table?

[petro62]

I was able to work on it this morning and I created the subform with the drop down menus for the phases. And all that works correctly to filling in the correct table. Now I just need to figure out the code to generate the report as I am currently getting an error each time I try to run it. Right now I know I get an error for milestones, but I don't believe that part is required for what I am doing. I could be wrong though and maybe I am missing a critical part of this. It looked like in the original report they were putting the milestone code into the report which I don't need.

After deleting the milestone section I get the error that it doesn't recognize Nr. I fixed this issue as the actual report still referred to Nr and Titel (now corrected to ProjecNumber & ProjName).

After that I ran it again and I get the following error that it does not recognize [202251] which I believe in the week date that is generated in tblScheduleMilestoneReportSetup. I will keep working on it...I think the issue may be on the report itself, but I am not sure yet.

So actually I am still get the Nr error. there is a Nr Header on the report and when I click properties it shows Group...for the name. I have not figure out how to change this header name.

Edit:
So I have gotten the report to work (partially). Right now I am trying to figure out why it wont show anything in the schedule beyond week 14 on a project. Everything is recording right to prepare the report so it seems to be with the report itself.

SchedProjecten new blank.zip

[petro62]

Your current structure does not require choosing milestone from dropdown - they enter date into appropriate milestone field of tblProjectDetails.

If you change data structure to use normalized milestone table, yes, select milestone from combobox. Or build code that creates a set of milestone records when a project detail record is created. Then user opens form filtered to that set of records and just enters date into appropriate record. Also, tblMilestones can probably be linked directly to tblProject instead of tblProjectDetails. Since tblProject and tblProjectDetails appear to have a one-to-one relationship, why not just have one table?

I am not sure what is going on exactly. Sometimes my report seems to work correctly other times it wont show the project name and just shows a question mark or symbols. Also it seems the weeks aren't getting correctly coded with the number or at least not after the number 3. So I am not exactly sure what is going on. Would you be able to see if anything appears to be obvious to you?

[June7]

For some reason, the query is showing project name as Asian style characters. I have no idea why.

Why is there no record in tblProjectDetails for project 626? Again, why have two tables?

tblSchedulePlan is not showing PhaseID for 2 records and 2 records do not have dates. So why is MilestoneReport table getting PhaseID of 0? It's because data in tblSchedulePlan is 0 but there is no longer a PhaseID of 0 in tblSchedulePlanPhase (or tblPhase). Table lookup and form are using tblSchedulePlanPhase but Relationships builder uses tblPhase. Advise not to build lookups in table. Remove either tblPhase or tblSchedulePlanPhase.

Fix data and structure then debug report.

[petro62]

Well I did some more work on it before your reply and I seem to have gotten it to work. The only issue I run into now I a warning when I run the report for the size not fitting. Other than that it works like intended.

I have a couple add ins I want to work on. First I want it to only show active projects so I am thinking I can do that within the query some how. The last thing I want to do is have the current week highlight itself. I will upload a new blank version after a bit more testing but so far so good. Thanks for all of the help n

[June7]

Move/resize controls until they fit within 16 inch width. The 'too wide' error should go away.

[petro62]

So on the report is there a way to get the unused space to not be white? As far I can tell I have changed all the sections to have a grey background, but as you see in the picture a large portion of unused space is left white.

Also while I try to get this to final form I was trying to copy one of my forms from another revision of this database and I get a message saying "The database has been placed in a state by user 'Admin'....". So it wont let me drag the forms over from one database to the other.

[isladogs]

Set all controls hidden and only make them visible when populated with data
For an example, see the crosstab report in my example app: Extended File Properties (isladogs.co.uk)

OR set the default colour to be same as the background (grey)

[June7]

Not that I know of. Reports are intended for printing. Coloring backgrounds would just use up printer ink/toner fast.

Print to colored paper instead of white.

Set default color where?

Question is not about white textboxes but white space below record.

[petro62]

Last few things then I am calling this done for awhile.

1. Is there a way on the report I can highlight the current week? I would assume I could put conditional formatting on the row that lists the week number, but I am not sure of how that formula would look.

2. I want the report to only show active projects. This is on tblProjects under ProjStatus. 1 is the value for Active. I assume in the qryScheduleMilestones I can have it only pull the active projects, but I am not exactly sure how I put that criteria into the query.


3. The last thing is that the database/file I have created for my final revision seems to prevent me from dragging some forms over from my previous revisions. These were ones I deleted while sharing the files. When I try dragging it over it gives me an error saying the admin/user for the computer has it in a mode preventing opening....something like that. I can get the exact message if that doesn't ring a bell. Is there a quick way to fix this or should I just import all of my new stuff into one of my older revisions?


I fixed the issue with the large white space by switching my footer. I had the stuff at the bottom in the page footer and now I put it in the report footer so it shows up at the bottom of the information.