Copying a whole table

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

Guest

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
 
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".
 
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
 
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
Click to expand...

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 spreadsheet. Access is a
wonderful tool. Learn to use it properly.
 
I am glad this question was asked because I am working on the same type of
problem. My specific problem is how to create the query to extract all of
the newly entered 2005 records, and all 2005 records that need to be viewed
through the year but have not been entered.

I want to sort by a specific field "effective date," and under that column
in query design I entered #2005#. That didn't work. I have also tried
'Effective Date=>Date (2005)' but that didn't work either. Can someone help
me? Thank you.
 
I am glad this question was asked because I am working on the same type of
problem. My specific problem is how to create the query to extract all of
the newly entered 2005 records, and all 2005 records that need to be viewed
through the year but have not been entered.

I want to sort by a specific field "effective date," and under that column
in query design I entered #2005#. That didn't work. I have also tried
'Effective Date=>Date (2005)' but that didn't work either. Can someone help
me? Thank you.

:
Click to expand...
** snipped **

If you have a date field in your table [Effective Date], you must use
a valid date to search it. A valid date includes the month, day and
year.
#2005# is not a valid date (#1/17/2005# is).

To search the field for all records within the current year (what ever
the current year is):
Where Year([Effective Date]) = Year(Date())

To search for records for any user entered year:
Where Year([Effective Date]) = [Enter Year]
Now you can enter 2003, 2004, or 2005 etc., (without the # symbols)
when prompted.
 
Back
Top