Select last input from several date columns

[Frankie_The_Flyer]

Not sure if this can be done, but here goes...

Having built a database that tracks projects across their lives, I've now been asked it I can identify the latest status of the project in a "quick view"

The table "All Projects" has columns that list the Project Number; Project Title; Project Manager and further colunms that list dates that the various stages of the project were achieved. i.e. Start Date; Date Milestone 1 Completed; Date Milestone 2 completed etc.

The projects are such that Milestone 5 may be completed before Milestone 2 and so on, so I need to be able to identify which Milestone was the last to be completed.

I thought of trying an IIF but the progress across the Milestones isn't logical so it won't work. I found something on the Internet about "Greatest" being used on some other program (not Access), which Access didn't like at all!

If the requirement had been given before starting building the db, I would have done everything differently, but I have no real desire to go back to stage 1 and start again, so any ideas on how or if it's possible to find the highest / latest date relevant to the Project Number across the row of data please??

 

[JHB]

Can't you use a Max on the date for the milestone completed date?

 

[namliam]

Having columns anme Milestone 1, Milestone 2, 3,4,5,6, etc is a definate sign of flawed design...

Depending on how many milestones there are your best recourse is either use a user created function to sort out the fields / milestones or use a set of queries to "fix" the design flaw with a set of Union queries... Something along the lines of:

Select ProjectID, 1 as MilestoneNumber, Milestone1 from YourTable
union 
select ProjectID, 2 as MilestoneNumber, Milestone2 from YourTable
union
etc.

Keep in mind there is a limit on the number of unions you can do... so you can split it into seperate queries and union the queries again if you need to...

The function shouldnt be too dificult to make depending on how many fields there are, but can go out of control

 

[madefemere]

Hello,

The difficulty is to fix the number of milestones wich has an effect on the number of your fields.

Here a simple user function that give the result with 5 milestones. For you to adapt if there is more milestones

Function StatusProject(ParDateStart As Variant, _
                                                              ParDate1 As Variant, _
                                      ParDate2 As Variant, _
                                                              ParDate3 As Variant, _
                                                              ParDate4 As Variant, _
                                                              ParDate5 As Variant) As String
Dim DateMax As Date
ParDateStart = Nz(ParDateStart, #1/1/1910#)
ParDate1 = Nz(ParDate1, ParDateStart)
ParDate2 = Nz(ParDate2, ParDateStart)
ParDate3 = Nz(ParDate3, ParDateStart)
ParDate4 = Nz(ParDate4, ParDateStart)
ParDate5 = Nz(ParDate5, ParDateStart)
DateMax = ParDateStart

If DateMax <= ParDate1 Then DateMax = ParDate1
If DateMax <= ParDate2 Then DateMax = ParDate2
If DateMax <= ParDate3 Then DateMax = ParDate3
If DateMax <= ParDate4 Then DateMax = ParDate4
If DateMax <= ParDate5 Then DateMax = ParDate5

Select Case DateMax
      Case ParDateStart
               StatusProject = "BEGIN"
      Case ParDate1
               StatusProject = "MILESTONE 1"
      Case ParDate2
               StatusProject = "MILESTONE 2"
      Case ParDate3
               StatusProject = "MILESTONE 3"
      Case ParDate4
               StatusProject = "MILESTONE 4"
      Case ParDate5
               StatusProject = "MILESTONE 5"
End Select
End Function

 

[Frankie_The_Flyer]

Thanks for all the inputs, but it all proved too complex and time consuming, so I introduced an IIF that looked at the various dates and depending on a cell having a date in it, produced a Status.

Project_Status:IIF([M2Achieved]>"","Milestone 2 Complete",IIF(M1Achieved]>"","Milestone 1 Complete") etc etc

I did find I had to put the latest date first which was a bit of a puzzle initially!

 

[The_Doc_Man]

The reason it would be too complex is probably because it wasn't properly normalized. In a normalized list of milestones, a straight-up sort ("ORDER BY") would have been all that was needed.

What you did will work, but to save yourself trouble in the future, study normalization techniques and realize that a simple list of milestones with proper normalization would give you what you wanted immediately without nearly so much fuss.

 

[Frankie_The_Flyer]

I guess that what comes from not starting from scratch. The basis for the db was information on a 2000 line multi column excel spreadsheet!