Update/Copy-Delete columns

[Fastone]

I have a database which contains information on last year and this year (number of employees and premium charged). Would it be possible to have a query or module or anything else automatically copy the data that is in the present column to the prior column on a set date?

Thanks

 

[Rich]

Your question suggests that you're creating a new column each year, is that correct?

 

[Fastone]

No, sorry if that was the impression I gave. I have only two columns: Prior, Present. The Present values need to get into the Prior column at a certain date. The Present values than need to be deleted (but the column obtained)

 

[Rich]

Can't you retrieve data from a previous date via a query on a single column without the need to store it again in another column?

 

[Fastone]

Ok, let me try and clarify the situation a bit.
I have date which is old (last year) en new (this year). Now, on the 1st of January (or whatever date) the new data becomes old. This old data is used for comparisons and stuff like that. This is a very easy cut and paste senario but it should be automated.
BTW: Both columns are in the same table.

Thanks

 

[Rich]

I understand what you are doing, I just don't understand the reasoning behind it, there are valid reasons for archiving old data to another table for historical purposes, I just can't see any advantage of moving the data to another column in the same table.
Hopefully, somebody will prove me wrong and help us both

 

[Fastone]

The reason is for comparison purpose and because this is the only 'old' data I have. It should also only go back 1 year and not the rest of this DB's miserable life.

 

[Rich]

Then why not just delete everything older than one year, just keep a back up in case

 

[Fastone]

Because this old columns don't have a date connected to them. But if yoo can help me that way, I'm happy to make sure a date will be present.

 

[Pat Hartman]

You are approaching the problem with a spreadsheet mentality. You need to change your thinking process to use a relational database.

A very simplistic way of looking at it is that things that you need to add columns for when working with a spreadsheet, you add rows for when working with relational tables. Therefore rather than having a column for LastYear and a column for ThisYear, you have two rows. One with last year (2002) in the Year field and a second one with this year (2003) in the year field. You use crosstab queries that take date parameters to pivot the data so that it "looks" like a spreadsheet. Properly done, you will NEVER need to write code that gets modified every year because you need to add new columns or new tables.

 

[Fastone]

Thanks Pat, just one question.
Are you suggesting I should add a new table every year for the data?