Full Version: Return Results Where Date Changed
UtterAccess Forums > Microsoft® Access > Access Date + Time
I have a table that contains the forecast billing dates for milestones for each period in the fiscal year. I need to return results for each milestone where the billing dates changed.
For example:
Milestone 11388 had a forecast date of 9/6/2011 for 2012-01 but that date was changed to 1/2/2012 for 2012-02. The date has remained unchanged since.
Milestone 14339 had a forecast date of 5/1/2011 for 2012-01 thru 2012-02 then the date was changed to 12/30/11 in 2012-03 until 2012-08 when it was changed again to 4/3/2012.
I'll need to pull back the customer and each period when the date was changed.
Milestone 2012-01 2012-02 2012-03 2012-08
11388 9/6/2011 1/2/2012
14339 5/1/2011 12/30/2011 4/3/2012
Do you have any suggestions on how I can do this? This table will continue to grow with each month passing. It will not be limited to just one fiscal year.
I've attached a database with the detail.
You would need another table, which would record the history of changes. In your "Milestone" table, you would have your milestone field and any other relevant information. In your "Milestone Change History" table, you would have a field to tie this to the "Milestone" table e.g. MilestoneID and this is where you could have your "Forecast Dates". By doing this a single milestone can have multiple forecast dates, so you can track all the changes. If theres just a single one for a milestone then you know there were no changes.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.