Gant Chart type display

[petro62]

Here is a blank version of the database.Project Dashboard blank.zip

[June7]

Not seeing any attempt to adapt the Busdriver example database to your situation.

Need to:

1. create the 'temp' table tblReportSetup

2. create frmReportSetup and adapt its code to your database structure

3. build CROSSTAB query

4. build report

5. test and debug

[petro62]

Not seeing any attempt to adapt the Busdriver example database to your situation.

Need to:

1. create the 'temp' table tblReportSetup

2. create frmReportSetup and adapt its code to your database structure

3. build CROSSTAB query

4. build report

5. test and debug

Yeah I just sent the actual database and not all the "not so good attempts". Thanks for the advice at least now I know some of the terms to look up. I will give it another go.

[petro62]

Not seeing any attempt to adapt the Busdriver example database to your situation.

Need to:

1. create the 'temp' table tblReportSetup

2. create frmReportSetup and adapt its code to your database structure

3. build CROSSTAB query

4. build report

5. test and debug

Here is where I am stuck when trying to recreate the tblReportsetup. tblReportSetup has Date, PersonID, and OffTimeID. For me this would translate to Date,ProjectID,PhaseID. The part I am stuck on is the OfftimeID. Currently I have the project manager enter the date of their phases in the form and it saves in tblProjectDetails under the respective column for that phase. So how do I correlate the date from that column to a PhaseID? I hope I am explaining this right. I think I understand how everything works after that, but I am stuck on this early step.

[June7]

Why would tblProjectDetails have a PhaseID field then each phase has its own date field? Having multiple phase fields is not normalized structure. There are 8 phase types in tblPhase but only 7 phase fields in tblProjectDetails and only 6 match to types in tblPhase. Why does tblProjects even have PhaseID field?

As you noted, BusDriver tblSchedule has start and end date fields. My solution expects each period (whether it be an employee absence or a phase) to be a record. Code would have to be modified or use a UNION query to rearrange fields to records.

I suppose end date of a phase could be calculated from start date of next phase (minus one day).

Example of UNION. Didn't include Startup and [Turn Over] fields. I don't see a Completion field anywhere.

SELECT ProjectID, Feasability AS StartDate, Conceptual - 1 AS EndDate, 1 AS Phase FROM tblProjectDetails
UNION SELECT ProjectID, Conceptual, Definition - 1, 2 FROM tblProjectDetails
UNION SELECT ProjectID, Definition, Design - 1, 3 FROM tblProjectDetails
UNION SELECT ProjectID, Design, Construct - 1, 4 FROM tblProjectDetails
UNION SELECT ProjectID, Construct, Date(), 5 FROM tblProjectDetails;


NOTE: feasability is misspelling of feasibility

Advise to remove space from [Turn Over] field name.

[petro62]

Why would tblProjectDetails have a PhaseID field then each phase has its own date field? Having multiple phase fields is not normalized structure. There are 8 phase types in tblPhase but only 7 phase fields in tblProjectDetails and only 6 match to types in tblPhase. Why does tblProjects even have PhaseID field?

As you noted, BusDriver tblSchedule has start and end date fields. My solution expects each period (whether it be an employee absence or a phase) to be a record. Code would have to be modified or use a UNION query to rearrange fields to records.

I suppose end date of a phase could be calculated from start date of next phase (minus one day).

Example of UNION. Didn't include Startup and [Turn Over] fields. I don't see a Completion field anywhere.

SELECT ProjectID, Feasability AS StartDate, Conceptual - 1 AS EndDate, 1 AS Phase FROM tblProjectDetails
UNION SELECT ProjectID, Conceptual, Definition - 1, 2 FROM tblProjectDetails
UNION SELECT ProjectID, Definition, Design - 1, 3 FROM tblProjectDetails
UNION SELECT ProjectID, Design, Construct - 1, 4 FROM tblProjectDetails
UNION SELECT ProjectID, Construct, Date(), 5 FROM tblProjectDetails;


NOTE: feasability is misspelling of feasibility

Advise to remove space from [Turn Over] field name.

I think I understand what you are saying and originally this did have a table dedicated to these Phases. So based on the busdriver I know I need to give each phase its own unique ID. So is there a way that I could create tblMilestones and when they enter a date into the Feasibility text box it saves a MilestoneID(autonumber), ProjectID, MilestoneNumber (this would be the unique ID for one of the 8 phases), and date. So when I enter a date into the feasibility textbox and saves it with a MilestoneNumber of 1. If I type a date into the Definition text box it saves a MilestoneNumber of 3. If I do that I can eliminate the data that currently sits in tblProjectDetails and have it in its' own dedicated table. I hope that makes sense.

[June7]

The Busdriver solution does not require each project's phases to have unique IDs. The Busdriver procedure does not even use the tblSchedule unique ScheduleID field for anything.

You can redesign your db if you want but the UNION query approach is a workaround so redesign is not required. If tblMilestones still has a field for each phase instead of a record, you really have changed anything.

[petro62]

The Busdriver solution does not require each project's phases to have unique IDs. The Busdriver procedure does not even use the tblSchedule unique ScheduleID field for anything.

You can redesign your db if you want but the UNION query approach is a workaround so redesign is not required. If tblMilestones still has a field for each phase instead of a record, you really have changed anything.

Thanks for all of the information. With that I was able to create the following table. What is the syntax to pull the ProjName from tblProjects into the union query? In the busdriver report it just shows a 31 day section. Since mine will span greater period of time is there a way to create a scroll bar so I can scroll side to side through dates? I originally didn't plan to make the user select of period of dates and instead it would just load (maybe with this Month centered). If not I could just show a 1 yr section instead. Just curious to know what is possible. I think with what I have now I should be able to reapply the rest of the busdriver example.

[petro62]

Actually ignore my above post. Looking back at the original thread discussing this and bus driver I see someone reapplied this to projects. The main difference is they break it down to weeks of the year. I am playing around with it and trying to see how it works and I think I can reapply it using the union select you suggested. The one part I am confused on is how they color coded the blocks. I don't see anything in the conditional formatting.

Edit: I now see that it seems it is set in the code when they click Process Report button

[petro62]

So I am working with another database that I believe you may have helped with, but I keep getting an issue when the SQL tries to execute. When I open debug it shows it highlighting the line db.Execute sSQLp. Being very new to SQL I assume this means there is an issue with the chunk of code above that statement, but I am not sure exactly what.

https://www.accessforums.net/showthr...t=18459&page=2

Private Sub cmdProcess_Click()

Dim db As Database
Dim sSQLp As String
Dim sSQLm As String
Dim dStartDate As Date
Dim dEndDate As Date
Dim dCurrDate As Date
Dim rst As Recordset
Dim iWeek As Long


Set db = CurrentDb
sSQLp = "DELETE * FROM tblScheduleMilestonesReportSetup"
db.Execute sSQLp


dStartDate = Forms!frmScheduleMilestonesReportSetup!StartDate
dEndDate = Forms!frmScheduleMilestonesReportSetup!EndDate
dCurrDate = dStartDate


Do While dCurrDate <= dEndDate
    iWeek = iWeek + 1
    
    sSQLp = "INSERT INTO tblScheduleMilestonesReportSetup (ProjectID, SchedPlanPhaseID, SchedWeek, SchedWeekSeq) "
    sSQLp = sSQLp & "SELECT "
    sSQLp = sSQLp & "[ProjectID], "
    'Select SchedPlanFaseID if SchedPlanFaseID is "active" in dCurrDate Week (Monday - Sunday)
    sSQLp = sSQLp & "Iif(#" & dCurrDate & "# between [startdate]-Weekday([startdate],2) +1 and [enddate]-Weekday([enddate],2) +7, [SchedPlanPhaseID],null) as Expr2, "
    'Select SchedWeek (subtract one year if Week is Week 52 ???)
    sSQLp = sSQLp & "'" & IIf(Right("0" & DatePart("ww", dCurrDate, vbMonday, vbFirstFourDays), 2) = 52, -1, 0) + DatePart("yyyy", dCurrDate, vbMonday, vbFirstFourDays) & Right("0" & DatePart("ww", dCurrDate, vbMonday, vbFirstFourDays), 2) & "' AS SchedWeek ,"
    'Select Week sequence
    sSQLp = sSQLp & iWeek & " "
    sSQLp = sSQLp & "FROM tblSchedulePlan "
    'Group the records by Project and - if SchedPlanFaseID is active in dCurrDate Week - by SchedPlanFaseID
    'sSQLp = sSQLp & "GROUP BY projectid, Iif(#" & dCurrDate & "# between [startdate] and [enddate], [SchedPlanFaseID],null)"


    Debug.Print sSQLp
    db.Execute sSQLp
        
    sSQLm = "INSERT INTO tblScheduleMilestonesReportSetup (ProjectID, SchedPlanPhaseID, SchedWeek, SchedWeekSeq) "
    sSQLm = sSQLm & "SELECT "
    sSQLm = sSQLm & "[ProjectID], "
    'Select SchedPlanFaseID if SchedPlanFaseID is "active" in dCurrDate Week (Monday - Sunday)
    sSQLm = sSQLm & "Iif(#" & dCurrDate & "# between [MilestoneDate]-Weekday([MilestoneDate],2) +1 and [MilestoneDate]-Weekday([MilestoneDate],2) +7, [MilestoneTypeID],null) as Expr2, "
    'Select SchedWeek (subtract one year if Week is Week 52 ???)
    sSQLm = sSQLm & "'" & IIf(Right("0" & DatePart("ww", dCurrDate, vbMonday, vbFirstFourDays), 2) = 52, -1, 0) + DatePart("yyyy", dCurrDate, vbMonday, vbFirstFourDays) & Right("0" & DatePart("ww", dCurrDate, vbMonday, vbFirstFourDays), 2) & "' AS SchedWeek ,"
    'Select Week sequence
    sSQLm = sSQLm & iWeek & " "
    sSQLm = sSQLm & "FROM tblMilestones "
    'Group the records by Project and - if SchedPlanFaseID is active in dCurrDate Week - by SchedPlanFaseID
    'sSQLm = sSQLm & "GROUP BY projectid, Iif(#" & dCurrDate & "# between [startdate] and [enddate], [SchedPlanFaseID],null)"


    Debug.Print sSQLm
    db.Execute sSQLm
    
        
    dCurrDate = DateAdd("ww", 1, dCurrDate)
Loop
Set db = Nothing


DoCmd.OpenReport "rptScheduleMilestones", acViewPreview
End Sub

[June7]

Post the output of the Debug.Print.

If SchedWeek is a number field, don't use apostrophe delimiters

[petro62]

Post the output of the Debug.Print.

If SchedWeek is a number field, don't use apostrophe delimiters

Thanks for the help I think I am really close to getting this.

I have a question about a textbox writing to two columns
So I have a textbox next to each milestone for the PM to enter their dates. Is there a way that when they add a date to that box it writes the date, but based on which box is also writes the MilestoneID.

So for instance Feasibility has an ID number of 1
I type 01/12/2022 into the textbox next to the Feasibility label.
It copies the date under the proper column, but how do I get it to also write the ID?

If I can do that then I can have one table that just stores all of the milestone dates. At least that was my thinking.

[June7]

Certainly, just need code.

What is "which box" and "proper column"? Still have separate fields for phases instead of a record for each phase? Again, this really doesn't change data structure. Might as well leave as is.

[petro62]

Certainly, just need code.

What is "which box" and "proper column"? Still have separate fields for phases instead of a record for each phase? Again, this really doesn't change data structure. Might as well leave as is.

Seems the file is too big too upload here now. I added a form with a bit more description to hopefully help. There are several issues at the moment as I was merging this database with my old one. There are some issues I still need address, but haven't had time. Just a heads up.


https://drive.google.com/file/d/1qEy...ew?usp=sharing

[June7]

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.