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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rogerdjrAuthor Commented:
Thanks

A great help in getting me started
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.