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

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.

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.

LVL 86

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented: 2018-02-18

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: 2018-02-18

Thanks

A great help in getting me started

Migrate a "3-D" Spread sheet to an Access Database

Who is Participating?