Normailising Tables

[Stephen]

Hello,

I am trying to find a way to normalise my database but am having
difficulty in ways to do this, as my table is over 100 fields.

A lot of the information is duplicated for previous years. This is
the bit I think I can put in to another table.

My table looks a bit like this:

Name Job First Year Second Year Third Year
Bob 1 01/02/04 01/02/03 01/02/02
Ian 2 02/02/04 02/02/03 03/02/02

I have a button on a form that moves all the data down one field each
financial year, creating a blank space for the new year i.e.

Name Job First Year Second Year Third Year
Bob 1 01/02/04 01/02/03
Ian 2 02/02/04 02/02/03

I have PK's in the table to make sure that the names will never be
duplicated so there is a referential integrity link.

Any ideas or any pointers will be really helpful.

Thanks

Stephen

[Rick Brandt]

"Stephen" <ba*********@hotmail.com> wrote in message
news:a4**************************@posting.google.c om...

Hello,

I am trying to find a way to normalise my database but am having
difficulty in ways to do this, as my table is over 100 fields.

A lot of the information is duplicated for previous years. This is
the bit I think I can put in to another table.

My table looks a bit like this:

Name Job First Year Second Year Third Year
Bob 1 01/02/04 01/02/03 01/02/02
Ian 2 02/02/04 02/02/03 03/02/02

The years should be separate rows not columns. Normalization is not always
about adding more tables.

Name Job Year
Bob 1 01/02/04
Bob 1 01/02/03
Bob 1 01/02/02
Ian 1 01/02/04
Ian 1 01/02/03
Ian 1 01/02/02

Change your PK to be a combination of Name and Year (both are bad names for
fields BTW since these are reserved words in Access).

This structure requires NO re-design when you add new years and can easily
be filtered to show any one or more years.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Stephen]

Rick,

Sorry in delay in getting back - but nice one.

Thanks