Advice on table design/structure

[WembleyBear]

I have read some advice already about normalisation but still need a little
help with ensuring I set up my tables correctly please. The database is
initially to hold details of budgets for our branches, 14 branches in all.
Each branch will then have it's budget input just the once and this will then
remain the same for the entire year, with no additions. And each line of the
budget for each branch will be split out into the individual months. So for
example, in our Northampton branch, we might budget for 19 technicians in
Jan, 20 in Feb etc throughout the year.

To me it seems like there are 3 dimensions: Branch + Budget line + Month.
What would be the most appropriate table structure to take account of: a)
each individual branch, b) each Budget item c) and the amount for each month?
And how should I link the tables so that when I later build the forms the
user can select one branch and enter all the years figures for each budget
line?

Thanks
Martyn

 

[Jeff Boyce]

So, if this is a "budget"-related exercise, and if you have experience using
a tool that helps manage budget and accounting (e.g., Excel), what do you
hope to gain by figuring out how to make Access do this?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[WembleyBear]

Hi Jeff

The data was previously held in two spreadsheets (not by my design!) each
over 54mb in size. These, unsurprisingly, repeatedly crash when the nominal
download data is added to them each month. The budget data is simply a part
of these spreadsheets. I've split the two spreadsheets down into the various
branches as seperate workbooks, but each is still large enough and contain 12
worksheets each. The data still has then to be pulled back together again in
order to summarize the monthly accounts - the spreadsheets are quite a
sprawl. My preference would be to hold the budget data in tables in Access,
and hopefully make it easier to navigate, print and import the monthly
nominal data for the management accounts. What I'm trying to say is that the
budget data is really part of a larger project and I'm seeking the best way
to hold this data without resorting back to "spreadsheet thinking".

Thanks
Martyn

 

[Jeff Boyce]

Martyn

You are quite right about needing to step away from "spreadsheet thinking"
if you want this to work well in Access!

I'm having trouble visualizing the domain you are working in ... could you
provide a description of the tables you have come up with so far? As an
example, a simplified class registration data structure might look like:

tblPerson
PersonID
FirstName
...

tblClass
ClassID
ClassTitle
...

trelRegistration
RegistrationID
PersonID
ClassID
RegistrationDate
...

If you'll provide a description of how you are structuring your data, it may
give folks here a way to offer more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP