Migrate a "3-D" Spread sheet to an Access Database
I have a working project management Microsoft Access database that I continue to develop, adding new processes that help consolidate all projec t functions into one tool where I can combine data to generate composite reports
With each new process I learn new techniques
The latest element that I am trying to add is mimicked in the attached spread sheet - the challenge I am faced with is that each contract has a different menu of "Billing Code / Activities" (Column b-f) and then there are monthly billings that occur for the life of the project (col g-o, col p-x, ...)
The typical working spreadsheet has a number of other tabs - but this is the "core data" for each contract.
Currently I manually build the spreadsheet for each project and then manually migrate data from the spreadsheet to my database each month.
Does any body have an example of a "3-d" table structure I could use as a starting point?
PAY-Application-Review-Sample.xlsm
With each new process I learn new techniques
The latest element that I am trying to add is mimicked in the attached spread sheet - the challenge I am faced with is that each contract has a different menu of "Billing Code / Activities" (Column b-f) and then there are monthly billings that occur for the life of the project (col g-o, col p-x, ...)
The typical working spreadsheet has a number of other tabs - but this is the "core data" for each contract.
Currently I manually build the spreadsheet for each project and then manually migrate data from the spreadsheet to my database each month.
Does any body have an example of a "3-d" table structure I could use as a starting point?
PAY-Application-Review-Sample.xlsm
Who is Participating?
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
If so, it's sort of like a Budget, but in reverse. You'd need 4 tables to represent those three entities and relationships:
tContract
------------------
ContractID
ContractNumber
ContractStart
etc etc
tEvents
----------------
EventID
EventName
EventCode
etc etc
tContract_Events
--------------------------
EventID
ContractID
EventValue
etc etc
tContractEvent_Periods
--------------------------
ContractEventPeriodID
EventID
ItemName
ItemAmount
ItemDate
etc etc
So you'd add Contracts and Events (the items in ColumnB), and you'd then add the relevant Events to a Contract. For each ContractEvent, you'd add the number of "periods" over which you'd spread the cost, and the amounts of each.
I'm not sure what the "trigger" is for each ContractEvent (i.e. a Date, a level of completeness, etc) so you'd obviously need to flesh that out a bit more.