I am designing a db in A2000 for my finance department, which has used Excel to maintain our data up to now. Bear with me, I'm a little overwhelmed because I've never worked on this large of scale. Here's what I'm working with:
Our data warehouse spits the data into Excel, and from there will have to be imported/linked to Access.
We need a main table (Projects) to hold all of the Project info (number, description, etc.)
Here's where I don't know which way to go with this: We need somehow to manage a labor $ plan, a non-labor $ plan, a headcount plan, and then actual $ for each as well, by month (for comparing plan to actual $ spent). Should I make a table for each? Ex:
It'll end up being a 1:1 relationship, because ea. project will only have one plan. But I don't know any other way to do it, except for putting ALL the data into the project table (Proj#,Desc,LaborJan, LaborFeb, NLJan, NLFeb, HCJan, HCFeb, etc.
Now here's the second part: I will also need to record any changes in the plan so that we can go back and say "In August, this was your plan for the year, and now it's this" without wasting a lot of space. I know I need to create a history table to do this, but I'm a little fuzzy on how to go about it.
One table should describe one object. If Labor is an object that is identical to a Non-Labor object then just add an extra field to the table that describes it as a Labor or Non-Labor object. i.e. IsLabor (Yes/No field)
Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!
Or try our Ask An Expert service to answer any of your questions!