Migrating to Access table/relationships question

[Parsnips]

Hi there,

We are considering migrating our budget over to Access, and I'm wondering what the best practise is for the following scenario:

At the highest level, we have projects. Each project has 8 tasks, and these all have budgets, forecasted amounts that change regularly, and actual dollars spent. Each budget, forecast, and actual dollars are represented monthly. I know I should separate out the projects and tasks, but I don't know how to separate out the task/budget etc/month relationship. Any tips on how to achieve this, as well as what a form for adding new projects would look like would be appreciated!

Current view in Excel

		Jan. Budget	Feb. Budget	...	Jan. Forecast	Feb. Forecast	...	Jan. Actuals	Feb. Actuals	...
Project 1	Task 1
Project 1	Task 2
Project 1	Task 3
Project 2	Task 1
Project 2	Task 2
Project 2	Task 3
Project 3	Task 1

<tbody>
</tbody>

Cheers!

 

[RickXL]

Hi,

If you want to stick to the usual relational model then you should really have the budgets, actuals etc on separate rows although I have to say that at least one data table in SAP is not like that. It has 16 columns for "period", which can be month, and then a column to specify the year. You use just as many columns as you need. Obviously, they did not expect us to be using weeks which caused a pile of bespoke stuff to be written!

The straightforward way would be to have tables for:

1. Project which contains at least a Project ID and a Project Name.
2. Each Task with a unique ID with Project ID as an attribute and Task Description etc.
3. Amounts will be in a third table keyed on Task and Amount Type, namely, Budget, Forecast and Actual and another part of the key for Year and Month.

A suggestion is shown below:

Excel 2013
	A	B
1	Project ID	Project Description
2	1	My First Project
3	2	Follow-Up Project
4	3	Third Project
5	4	Final Project for the time being
Project

Excel 2013
	A	B	C
1	Task ID	Project	Task Description
2	1	1	Initial Task
3	2	1	Final Task
4	3	2	Start up
5	4	2	Close down
6	5	3	Prepare
7	6	3	Execute
8	7	3	Comission
Task

Excel 2013
	A	B	C	D	E
1	Task ID	Type ID	Year	Month	Amount
2	1	1	2016	4	1000
3	1	1	2016	4	900
4	1	1	2016	4	1100
5	1	2	2016	5	1000
6	1	2	2016	5	1000
7	1	2	2016	5
8	1	3	2016	6	1500
9	1	3	2016	6	1200
10	1	3	2016	6
Amounts

Excel 2013
	A	B
1	Type ID	Types
2	1	Budget
3	2	Forecast
4	3	Actuals
Amount Types

That should be able to provide you with the dropdowns you need to fill in the values. You will probably need something for year and month as well. (In a system which uses general Posting Periods, you would have tables for those as well e.g. Posting Period name and start and end dates .)

If that looks OK, try out (mentally at least) all the scenarios you can think of (e.g. data entry, data editing, reporting) and see if the table layout works for each one and that all processes will be efficient.

I am not an Access expert but I would expect a form to enter data into each "master" table (Project and Amount ID) and then possibly "compound" forms to enter the other data. For instance, for Task entry you could include the Project ID and Project Description/Title.

It may be more understandable to have separate forms for Budget, Forecast ands Actuals because they may be entered by different people at different times. In fact, Actuals may be logged elsewhere and be accumultaed into that column. The precise layouts will depend on your requirements.

I hope this helps,

 

[Micron]

At the very least, leave the spaces out of the field names and don't use reserved words (like Year, Date, Month and Type) or special characters (possibly excepting the underscore, which I usually avoid). I would shorten names as well, as it can get ugly if they're long. I use a kind of Hungarian notation for everything as well as Title Case. E.g. TaskID, ProjID; tblProjects, tblTask NEVER something like Task #.
Edit:
What's missing from the suggestion is that there is no link between the project and the task budgeted amount because there is no field for the project id - only arbitrary task numbers. Also, I'm not a projects db expert, but I see no reason for storing year/month values. Any report or form can show values for quarters or any other time period with simple built in functions. In a very large db, it would be a lot of repeated information that really isn't needed. However, I would have TaskStartDte and TaskComplDte (or any other needed date columns) and avoid the use of time unless it's really needed.

Maybe check out these or similar re: the naming stuff
MS Access Naming Conventions - Access World Forums
Microsoft Access tips: Problem names and reserved words in Access
Built in function reference: http://www.techonthenet.com/access/functions/

 

[RickXL]

... I see no reason for storing year/month values.

So how would you check whether you had exceeded the budget or the forecast in April 2016, for instance, then?

I am not sure how much detail the OP wants to include but ERP systems can include lots of details. For instance you would typically have an overall budget for the project, possibly an overall budget for each work breakdown structure element. You will at least have that also broken down by year.

Cost plans (forecasts) can also be split along the above lines but also by posting period.

Individual activities might have planned and separate forecast estimates and if you are using "earned value" you will also need breakdowns to bottom level WBS and posting period - possibly even individual tasks. Earned value estimates are separate from budgets and from cost plans.

You then need an auditable system for budget release, moving budgets from place to place in the project and also in time as well as making changes to the budget. Budgetting and Cost Planning can give rise to quite complicated databases by themselves.

Then they have "versions" of cost plans and budgets so you can examine "what-if" scenarios.

 

[Micron]

So how would you check whether you had exceeded the budget or the forecast in April 2016, for instance, then?

Can you not perform aggregate functions (such as Sum or Dsum) on any currency field and compare the budget estimate to current cost? To get the sum for the current quarter would be WHERE DatePart("q",ProjectDte) = DatePart("q",Date()). For the last quarter WHERE DatePart("q",ProjectDte) = 4.

You will at least have that also broken down by year.

WHERE DatePart("yyyy",ProjectDate) = 2016
Seems to me that date fields for the project and/or task (e.g. ProjectDte, CreateDate; FiniDate, whatever) ought to work, but if you say it's necessary, who am I to argue? Like I said, I'm not an expert on the subject, but I know a bit about relational databases.

 

[RickXL]

We may be agreeing violently here. You need to record the date somehow.

However, for forecasts and budgets it is preferred to use financial posting periods which are often months. Actuals will be recorded as and when they occur (e.g. time bookings and invoice payments) using dates but will also need to be reported by month. The accountants like to "close" posting periods so they cannot be changed after that time. Even if you find an amount that was incurred in an earlier month and has a date to match it will not be posted to that month after the period has been closed. So in that case you would need the actual date and a separate posting date.

 

[Parsnips]

Thank you all for your replies! It's helpful, as moving from Excel to Access seems to require changing a lot of mindsets.

With the suggested format, what would be the best way to create a form that allows the user to view existing projects and their tasks, as well as add new tasks to existing projects?

 

[RickXL]

Thank you all for your replies! It's helpful, as moving from Excel to Access seems to require changing a lot of mindsets.

Yes, it does. Excel encourages you to roll the jobs of entering, storing, processing and presenting data into one. It is not uncommon for someone using Excel to put the data into the spreadsheet as they want to present it then have problems with carrying out the processing they need because the data is not in a suitable format.

A relational database like Access, on the other hand, wants you to store the data in a way that is easy to understand while permitting any type of data entry, calculation and presentation to be possible. So while the data is not presented well, by default, you can process it any way you want.

I can give you an example. I knew someone who had a spreadsheet with years down the side and months across the top. It looked great. Then he wanted to process the figures from July one year to June the next and he could not work out how to do it. If he had used the database approach he would have has years and months as row keys and the values in column C. His selection would then have been easy. The downside was that his data would not look good without any processing.

With the suggested format, what would be the best way to create a form that allows the user to view existing projects and their tasks, as well as add new tasks to existing projects?

As I mentioned previously, you will probably be needing more than one form. You will need to work out how you want to use the data and make the forms to suit. You might enter the Project data straight into the table or you might need to create a form to do that. It may depend on the expertise of the people who will be entering Projects. Similarly for the other tables.

Or, you might dream up a form with scrollable areas for the Tasks, for instance while still showing all the Project details. Tables can be joined to be presented on a form. In general, anything you need in a dropdown list or for verification purposes will need to be entered separately.

Assume everything is possible then see what you need.

 

[xenou]

We may be agreeing violently here. You need to record the date somehow.

However, for forecasts and budgets it is preferred to use financial posting periods which are often months. Actuals will be recorded as and when they occur (e.g. time bookings and invoice payments) using dates but will also need to be reported by month. The accountants like to "close" posting periods so they cannot be changed after that time. Even if you find an amount that was incurred in an earlier month and has a date to match it will not be posted to that month after the period has been closed. So in that case you would need the actual date and a separate posting date.

This is indeed completely standard for DB-driven financial applications.