Database Structure for Opportunity Tracker with History

[jmchristian]

Thanks in advance.  And sorry if this has already been answered.  I couldn't find a good post about this.

Developing a base to track opportunities based on a weekly call and ~20 columns of relevant data in many different data formats.  I'd like to use one table to use as the working table and another that shows the opportunity and its revision history as the working table gets populated.  What is the best way to go about this?  

For example, each week we may populate a "Progress" field that is a few bullet points on progress made and obstacles.  I want to somehow populate another table that allows selection of a particular Opportunity shows the history of the each of the 20 (now 19 since Opportunity is not longer a column) columns in an easily digestible format.  

I need a way to very quickly populate the working table and a method to quickly review the resulting history of the data.  Thanks a lot!

The way we are handling it now is in OneNote and copy/pasting the previous revision to a new tab and dating it.  This makes it difficult to see revisions over time as our opportunities can take years to fully develop.

I don't think the revision history capability is what I'm looking for.  

[TheTimeSavingCo]

Hmm, a lot depends on what you'd want the output to kind of look like, really.  If you could create a new table with like 3 fields in it and provide an example of what you'd want the revision history to track and look like that'd be very helpful!

[jmchristian]

Hi thanks for the quick response.  The input table looks like the following with the revision with the Buyer / Opportunity being the the pivot to another table (essentially the revision history).  The revision history should include the blue highlighted items.  What I could do is copy / paste the existing rows each week and add them into the database and update the date to the next week so I can track the revision history.  But I wish there was a cleaner way to do it.

Using a table as an "Input Form" into a data table would be a solution, but I'm not sure how to implement it.  Essentially the input table would have all the existing unique opportunities, I could add rows to it and edit cells of each row, and finally submit the form to populate the datatables with the submission date and annotate (either automatically or manually via the form) any items that have been updated.