Table Design Issue/Question

[clove04]

Hi,

I'm trying to set up a good table structure and am not sure what will
be best. I have on an annual basis maybe 200 records that track budget
planning expenses as planned, expenses as disbursed and historical
expenses. At any one time, one record can only be Official, Proposed,
or Historic.

The goal is to compare the most recent approved (Official) quarterly
budgeted amount to the amount proposed (Proposed) - once all parties
agree to the proposed amount. Official is "O" and Proposed is "P"

Project Official_Q1, O_Q2, O_Q3, O_Q4, O_Tot, ProposedQ1, P_Q2,
P_Q3, P_Q4, P_Tot

Once the proposed budget is agreed upon, the Official status will be
changed to Historic, the Proposed will change to Official and the next
round of changes will use the same data as the Official data with a
revised status of Proposed.

My first thought was to have a table with Official, Proposed and
Historic data so that a query could pull official and proposed data
into one query result that could be used for the main form. Over time,
the Historic table would grow yet the Official and Proposed tables
would seldom exceed about 200 records.

I'm new to the design side - does this make sense or would I be better
of with one larger table and one filed used to distinguish the Status
of Official, Proposed or Historic?

Any insights are appreciated -
Thanks!
Chip

 

[mscertified]

Its a trade-off. In my opinion either solution would be acceptable.
It would be nice to be able to change the status of records by just flipping
the status instead of moving the records from one table to another. On the
other hand, you would have to weed through the historical records whenever
you needed to get proposed or official records.
How about a compromise solution, where you keep proposed and official in one
table and historical in a separate table?

-Dorian

 

[Jeff Boyce]

From the description you offered, it looks like you are building a ...
spreadsheet! To get the best of Access' features/functions, consider
spending some time normalizing your data before you ever create a table.

It sounds like you are saying that you have Dollars that fit in a Quarter of
a Fiscal Year, and are either Projected or Actual. The "Historical" numbers
could mean Dollars from Quarters in past Fiscal Years (or could mean
something else -- which is it?).

Notice that I didn't include a "Total" -- that's something that Access can
easily do with a query.

You didn't mention the categories, but I'm assuming that your "maybe 200
records" related to line items, so each of the Dollars ... above would also
have a ChartOfAcctsNumber or some such.

Take a look at this (decidedly incompletely informed) structure:

tblBudget
BudgetID (unique identifier)
BudgetDate (actual date -- you can use Access date functions to get
"quarter")
Amount
BudgetType (Projected or Expense)
ChartOfAcctNumber (which line item)

Does that capture all of the basic data?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[clove04]

Thanks Dorian - will keep plugging along and see how it works
Chip

 

[clove04]

Thanks Dorian - will keep plugging along and see how it works
Chip

 

[clove04]

Yes -I'm caught again! Actually, I'm trying to take information from
various spreadsheets and put them into a manageable database. The core
data are -
Project (represents a single project)
P_ID (unique ID for a project)
Event (type of event at a project)
E_Seq (sequential event #)
Status (as Official, Proposed, or Historic)
Stat_Date (date of status change)
Q1 Planned amounts
Q2
Q3
Q4
Comment
Mod_Date (captures date when a record was changed - used on Proposed
sites only since Historic and Official will not be editable)


Your idea does make sense since another piece of data to be captured
will be a specific budget object class (BOC) - any single quarter
planned amount could have 1, 2 or 3 BOCs. I am still working on
understanding how the full cylce takes place - the major request is to
compare the Official plan (approved by Mgmnt) to the Proposed plan,
allow users to edit the Proposed plan and check to make sure the
Proposed plan as changed is still in line with the Official plan.
There will be many changes to the proposed plan between the quartely
Official plans.

Due to my lack of knowledge, I am considering using the multiple
similar tables to try and ease the comparison between Official and
Proposed. With separate tables, I can use a query to create all the
info in a subform and place it on a form.

In an ideal world, what's the best way to store and display this type
of data? If a single database with the Status field, can a well
designed query pull the data from fewer tables or a single table?

Thanks Jeff

Chip

 

[Jeff Boyce]

The number of tables you need in a well-normalized relational database is
.... <insert any answer you want here!> There's no right answer, because
each "domain" (your real world situation) is different.

Normalization helps you group like information together, and, when using a
tool like Access, simplifies your querying.

Whether you have one table or many, you can use queries to assemble an
answer. You can create queries against tables that have not been well
normalized, but both you and Access will have to work much harder.

I'll point out that, even though "caught", your description is still quite
"spreadsheetly". As soon as you describe repeating fields (Q1, Q2, Q3, Q4)
you are in spreadsheet mode. A more normalized structure is like the one I
described in my previous post.

You do not need to store separate quarters' amounts in separate fields. You
can store an amount and "tag" it with the date and/or quarter to which it
applies. That only takes two fields (Amount, Quarter) and you can keep
going "deep" without ever having to expand the table wider to handle NEXT
year's quarters, and the FOLLOWING year's quarters and ...

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

 

[clove04]

Thanks for your time! I have lots to learn and so little time...
(I'm sure that's not familiar to anyone else)

Chip