Re: Copying a whole table

From: fredg (fgutkind_at_example.invalid)
Date: 01/17/05 

Date: Mon, 17 Jan 2005 17:07:54 GMT

On Mon, 17 Jan 2005 07:31:51 -0800, gmax1988 wrote:

> So this is only the second year (keeping the original table) the info in
> certain colulmns needs to be deleted each year because it is a yearly
> membership so how do you delete all the info in a column eg a tick or a date
> processed but not the column itself?
>
> Thanks
>
> "Jeff Boyce" wrote:
>
>> To make the most of Access' capabilities, reconsider your data structure.
>> From your description, you use different tables for different year's data.
>> This approach is very common ... in spreadsheets!
>>
>> You don't want to do this in tables in a relational database like Access.
>> Here's one reason why -- all your queries, forms, reports and code that
>> refer to the table named 2004xxxxxx will have to be modified to refer to
>> 2005xxxxxx. Here's another -- every year you add a new table, you have to
>> figure a way to look "across" another table for all historical records.
>>
>> Instead, consider the impact of simply adding a date/time field to your
>> underlying basic table (forget using the year in the title, the table
>> records something common from year to year). If you were recording
>> Contracts, your new table would be tblContracts, not tbl2005Contracts.
>>
>> Note that this satisfies your other requirement -- i.e., how to relate your
>> "links to reports" (and queries, and ...).
>>
>> With the new design, you can easily get only those rows with a date in 2005.
>> Create a query that returns the fields you want, and add a criterion to the
>> newly-added date/time field. You could even get more creative and make the
>> criterion "dynamic", changing each year, rather than "static", hard-coded to
>> "2005".
>>
>> --
>> Good luck
>>
>> Jeff Boyce
>> <Access MVP>
>>
>> "gmax1988" <gmax1988@discussions.microsoft.com> wrote in message
>> news:71F28E90-28DE-403B-9371-5426218FDEEC@microsoft.com...
>>> I have a database set up and I wish to copy a table named 2004 and start a
>>> new one for 2005 with the same info from 2004 that I can add to throughout
>>> 2005. It needs to keep all the links to other reports etc.
>>> I am very new to access is there any easy way to do this?
>>>
>>> Thanks
>>
>>

Actually, you don't have to delete the previous year's records.
By including the membership date, you can return records for any one
year, or all the years. Stop thinking spread***. Access is a
wonderful tool. Learn to use it properly. 

-- 
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Loading