Adding two new columns every year

S

Sarah Stockton

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
 
T

Tom Lake

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
 
T

Tom Lake

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
 
J

John Vinson

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
 
Top