Adding two new columns every year

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Any Idiea on this would be greatly appreciated

I have one table called "Master" each year I need to add two coulmns to it
such as "05Hi" and "05Lo" ect...

This is where the proble comes in:it has to be user stupid. My user will
know nothing about Access,except how to enter data, so it has to be all
automated. I thought about putting a SQL statement into a query, except that
the add column statement askes for the colunm name, and I can't have the same
field name entered twice.

Any advice would be great

thank you ahead of time

Sarah Kathleen Stockton
 
Sarah Stockton said:
Any Idiea on this would be greatly appreciated

I have one table called "Master" each year I need to add two coulmns to it
such as "05Hi" and "05Lo" ect...

This is where the proble comes in:it has to be user stupid. My user will
know nothing about Access,except how to enter data, so it has to be all
automated. I thought about putting a SQL statement into a query, except
that
the add column statement askes for the colunm name, and I can't have the
same
field name entered twice.

Any advice would be great
Click to expand...

I think you're going about it the wrong way. Don't add columns, create a
one to many relationship and create two new records in the many side for
each year.

Tom Lake
 
I think you're going about it the wrong way. Don't add columns, create a
one to many relationship and create two new records in the many side for
each year.

Tom Lake
Click to expand...

The OP is presumably using the new Columns for data relating to
existing rows in the Table for the new year. I agree that her basic
approach is wrong, but I am not sure that I understand your proposed
solution.

I would get rid of _all_ the nnHi and nnLo columns from Master and
move the data from them into a second, linked Table looking like:

PriKey Primary Key
MasterPt Link to Primary Key of Record in Master Table
Year (nn from the old system)
Hi
Lo

This assumes, of course, that there is no other data which changes
from year to year and for which the history has to be retained. If
there is such data, it may also be able to be moved into the new
Table.

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Sarah Stockton said:
The OP is presumably using the new Columns for data relating to
existing rows in the Table for the new year. I agree that her basic
approach is wrong, but I am not sure that I understand your proposed
solution.

I would get rid of _all_ the nnHi and nnLo columns from Master and
move the data from them into a second, linked Table looking like:

PriKey Primary Key
MasterPt Link to Primary Key of Record in Master Table
Year (nn from the old system)
Hi
Lo
Click to expand...

Yes, that's pretty much what I said except I used two records per year in
the second table instead of one.

Tom Lake
 
Yes, that's pretty much what I said except I used two records per year in
the second table instead of one.

Tom Lake
Click to expand...

I'm sure it's what you meant; I'm not so sure that it's what you said!

I don't think that you gain anything by using two records per year
instead of one, since you would have to add back a field to the Table
to indicate whether you are looking at a Hi or a Lo, and getting both
values in the same query would be slightly more complex and (for large
datasets) probably slower.

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Any Idiea on this would be greatly appreciated

I have one table called "Master" each year I need to add two coulmns to it
such as "05Hi" and "05Lo" ect...
Click to expand...
As Tom and Peter say - DON'T DO IT THIS WAY.

You're "committing spreadsheet upon a database". That's a misdemeanor
punishable by being required to read Codd and Date, or (if the judge
is feeling lenient) John Viescas' book on SQL.

Storing data in fieldnames *IS BAD DESIGN*, and has already gotten you
in trouble! If you have a one (item) to many (years) relationship, use
*two tables* in a one to many relationship; the second table would
have a Year field. Rather than adding two new fields every year,
you'ld add new *records* in the related table.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top