Table Structure Help & Access 2010!!

[andy_dyer]

Ok...

It's been several years since I last touched Access and it was Access 2000 I was using - I've now been asked to build a database in Access 2010!!

I'm struggling to get my head around the basics of how best to build my tables...

basic structure of my data is

1 x Owner
Many x Budget Owner
Many x Sites
Many x Cost Centres
Many x Cost Headings

Then I have 12 months which have budgets (which I have) and also need actuals to be added on a monthly basis for each of the cost headings...

currently i have one large table which has all these fields in this very order from owner to month 12 actual

I'm struggling to remember how best to build the other tables and link them together - I'll attach what I have so far...

Access 2010 is so different is has completely thrown me although I keep telling myself it's fundamentally the same underneath!

Can anyone please check what i have done and see if it makes sense? Or make suggestions for how to improve it before I start getting real data in?

Thanks

 

[Banana]

Caveat: I tend don't download files so my answer is based on the post and apologies if this is off...

currently i have one large table which has all these fields in this very order from owner to month 12 actual

If you mean that your table has this

... Jan | Feb | Mar | Apr | .... | Dec

then this isn't the best way to design the table. This is definitely useful format as a *report* but not for the table design itself.

Basically, you'd have two tables:

tblBudget <-- store budgeting info
-BudgetID
-BudgetMonth
-BudgetAmount
-<other columns as needed>

tblActuals <-- store the actual *totals*
-ActualID
-ActualMonth
-ActualAmount
...

IMPORTANT: the tblActuals is based on the assumption that you are only entering the gross amount of actual and not necessarily the line items. That may be incorrect and if your database does track each single expenses that would add up to the "Actual", then you don't want tblActuals. You want this instead:

tblExpenses
-ExpenseID
-ExpenseDesc
-ExpenseDate
-ExpenseAmount

Enter the individual expenses, then you would calculate the Actuals by summing them for each month (in query parlance that'd be "grouped by Month).

Does that helps?

 

[andy_dyer]

Thanks - no not month names just month numbers month 1 budget, month 1 actual etc

But your advice probably holds true still...

I will have actuals for each type of cost I.e. Salaries, travelling etc under each cost center...

I also have the problem that I may have actuals against some cost headings with no budget...

Does that help you help me?

Thanks

 

[Banana]

Right - whether it's month names or month numbers, you don't want to "repeat" the column that would contain same kind of data but differs only in attributes (e.g. the motnh this data is for). That's why you want a column "BudgetMonth" to identify which month this budget is for.

Same thing with cost centers - you'd add something like CostCenterID to the Budget and Expense tables so you know which expense/budget belong to what cost center. In your reporting, you can then use a crosttab query to group by cost center and tabulate the results by months.

In case of where you do not have budget data, you would make sure your query uses outer joins to guarantee that it contains all rows whether there is a match or not so outer join on cost center should ensure they'll still appear on your reporting and you can choose to add some data such as "No Budget Given for this X month" on your report.


So in short, the only thing you actually enter would be basically budgets and the expenses (or actuals if you are not tracking the individual expenses but only the final balance at end of month). Querying does the rest.

Helps?

 

[jdraw]

Andy,

Many who participate in the forum do not have acc2007 or above, and therefore can not open an accdb (even if they do download files).
So you may get more responses by attaching in mdb format.

There are a number of free data models here that may be of value to you.
http://www.databaseanswers.org/data_models/index.htm

 

[andy_dyer]

Andy,

Many who participate in the forum do not have acc2007 or above, and therefore can not open an accdb (even if they do download files).
So you may get more responses by attaching in mdb format.

There are a number of free data models here that may be of value to you.
http://www.databaseanswers.org/data_models/index.htm

Thanks - I've got no idea how to convert back to a .mdb

I'll have a look!

 

[andy_dyer]

I may be having a real mental block but I think this is why I have built previous databases 'wrong'...

I cannot figure out how I get a query to churn out a nice report

with

budget owner - site - cost centre - cost heading - actual month 1 - budget month 1 - etc

I have previously created monsters of tables with many repeating fields and been told off by you guys but I simply don't know how to do it 'properly'...

I would love to get this one right... think i've also worked out how to save as an earlier version... so anyone that has an earlier of version... here you go

 

[jdraw]

Andy,

I've looked at your database.
Your tblMain should be broken down as Banana has suggested.
tblBudget and tblExpenses ( or tblActuals depending on how you're tracking expenses)

Banana has laid it out for you. I think you are making things difficult for yourself.

My suggestion would be to do a little reading/review of Normalization.

For your own benefit, I'd recommend your writing a 1 liner describing each of the Tables and each of the fields in the Tables. You can do this right in the Table design . It will help clarify what you are dealing with and will help in documentation and any associated maintenance; including describing the database and its use to others.

Using a naming convention that excludes the use of spaces in Table and object names will save you work in the longer term.

I think you would find this next site (especially the first 3 Topics - Normalization, Normal Forms and the Entity Relationship Diagramming) very informative. And I think it will help with building tables and relationships (links) between tables.

http://www.rogersaccesslibrary.com/forum/topic238.html

Good luck and congrats on sorting thru the creating and mdb. Oh yes, thanks for the mdb version.