Re: Conceptual design advice for relational database

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Getting rid of repetitive data is a good thing. It's what normalization is
all about. However, the solution is NOT to do as you have done. That
violates the first rule of normalization. You may well need other tables.
I can't say for sure and I don't like recommending table designs in a forum
like this because most business processes are far more complicated than
people communicate in posts.

Get a copy of "Database Design for Mere Mortals" by Michael Hernandez.
After reading that, go to my website and download the database design
tutorials. http://www.rogersaccesslibrary.com/TutorialsDesign.html. These
tutorials use Hernandez's process. That will give you a good idea of how
you should design your tables.

Access does not have a transpose feature like Excel. But one of the samples
on my website (NormalizeDenormalize2k.mdb) shows how to write denormalized
data into a normalized format.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



"laura reid" <laurareid@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:543E527A-7FD0-4028-9DB0-5E00E402633C@xxxxxxxxxxxxxxxx
Roger,

Thanks for your post.
When I set up the table, I thought about how to address the year problem
because I knew I'd have a problem with having to change each and every
query/report/form etc when I moved from one year to the next. So what I
did
in the main table was set up the design like this:

ID
ReqDesc
CurrYr
PgrmYr1
PgrmYr2
PgrmYr3 and so on....then in my reports or forms, I just renamed the
labels.

I thought by having the year in every record I would be committing the
error
of having repetitive data, (sorry, not up to speed on all my db lingo)
which
I thought was to be avoided.

I gotta think through what you say. You don't have to tell me how, but is
there a way to automatically fill in the date field to reflect the current
year, but still allow the user to manually change, if they need to enter a
different year. I wouldn't want a separate relational table to store only
the years, would I?

I certainly see how at the end of FY06, I could run a query that sums all
the FYo6 entries, and then append those sums back into the table and
change
the year to 07 to be the new years start position. hhhmmmmm.

Next question, excel let's you transpose records...does access have an
easy
way to fix this or must I go in and do a lot of handjamming?



"Roger Carlson" wrote:

Hi Laura,

I'm not going to try to answer your specific questions, because your
basic
problem lies in this sentence:

So my design consists of the main related table which holds the index
field,
detailed requirement description, and the 7 separate fields for 7
years (1
current year +6 programming years).

You should NOT have 7 separate fields, one for each year. This is what
we
call "committing spread***". It is a fine design for a spread***,
but
absolutely wrong for a relational database. Instead, you should have a
separate RECORD for each year's data. So instead of this:

ID......ReqDesc....2006.......2007......2008
============================
1.........Req1..........3.00........4.00.......2.00
2.........Req2..........6.00........1.00.......4.00

You'd have:

ID......ReqDesc......Year.......Amount
==========================
1........Req1............2006.......3.00
2........Req1............2007.......4.00
3........Req1............2008.......2.00
4........Req2............2006.......6.00
5........Req2............2007.......1.00
6........Req2............2008.......4.00

Now this is just an example. Please don't view it as definitive. But
the
point is that instead of storing information (year) in the column header
(field name) you store the value itself in a field. This will allow you
to
add and subtract amounts by adding additional positive or negative
values in
a new record.

7........Req2............2008.......-2.00

This will also make it possible to aggregate data from one year to
another.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L





"laura reid" <laurareid@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2EF65FFC-572B-42ED-835E-7C2A02030E99@xxxxxxxxxxxxxxxx
First, my apologies to this lengthy email, as it seems these news
groups
are
more focused on short-term problems and perhaps my email is
inappropriate
for
this newsgroup, but I'm going to try anyway ;) I think what I'm
mainly
looking for is either validation I'm on the right track or
recommendations
on
how to improve.

I have created a relational database for the purpose of formulating
our
military command's budget requirements over a course of 7 years and
also
to
track execution of funds during the current operating year.

So my design consists of the main related table which holds the index
field,
detailed requirement description, and the 7 separate fields for 7
years (1
current year +6 programming years).

Related tables include information for:
office/command responsible for the requirement
funding account information
mission function the requirement supports
requirement classification type (ie, travel, supplies, equipment,
contracts)
and audit trail information for funding changes during the year of
execution.

I have then created a BASEquery that merges all this information and I
use
this BASEquery as a source for all my other subqueryies/reports/ and
forms.

My endstate objective is to publish the DB for other users, so they
can
enter their requirements, but I'm not there yet, because, well, I just
don
't
know how to do it yet.

Problems: many, but my two biggest conceptual issues are as follows:

1) I exported data from the BASEquery into a spread*** in order to
send
out files to each command to make changes to their requirements. (I
know
this
isn't what should be done, but these people are non-access users and I
haven't taught myself the whole frontend/backend concept yet ---
getting
there though). I thought I could then take their changes and reimport
the
spread*** files. This didn't work out so easily because during the
export,
the information in the lookup fields converted to text. So then when
I
tried
to append, I had to change the text fields back to their numerical
code.
Now
I've been advised I should use combo boxes and list boxes on my forms
which I
haven't done..I just use the lookup field from the baseQry, but will
go
back
and study this.

2) My next problem is figuring out the best way to consider tracking
requirement changes during the execution year. We can't just go in
and
change the figure, but must show the +/- change so the database needs
to
support an audit tracking function. What I have set up is a form
where
the
analyst can go in and apply the decrements or increases through out
the
year
and then the database re-totals the individual requirement. This
works
fine
until the end of the year when I need to roll the end of year
execution
totals over to be the next years start point. To get around this,
what I
did
was created a FY06Backup to the main table, cleared all the records
from
the
main table, then appended the main table with records from the backup
using a
query that totaled the requirements. Can't I just create these steps
into
a
macro to run every year?

Thanks






.

Quantcast