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
rogerdjrAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So you have a Contract, and that Contract would have different "events" (i.e. the Earthwork-AC), and you'd bill for those events on a structured basis?

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.
0
 
rogerdjrAuthor Commented:
Thanks

A great help in getting me started
0
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.