RE: Archiving
- From: Jerry Whittle <JerryWhittle@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 17 Jan 2007 15:30:00 -0800
contains a lot of records for each YearMonth column
Do you have a lot of YearMonth columns across the table something like this?
200611 200612 200701 200702
If so your table is not normalized properly and you'll always have problems
finding the right data and updating it. Also if your tables aren't properly
normalized the database will be both larger and slower than need be. If I'm
correct about the columns going across, I suggest that you get some
relational database training or pick up something like "Database Design for
Mere Mortals".
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
"Ray S." wrote:
OK, this solution immediately raises another question...when I want so.
uncheck records, how do I do it? Let's say my DataTable is very large and
contains a lot of records for each YearMonth column, I tried to query the
table for a YearMonth that I want to uncheck. The query goes fine, but when I
try to use it to update the yes/no field...the taskbar says
...Calculating...then nothing happens.
"Jerry Whittle" wrote:
IMHO the best way to archive data is to put a Yes/No field in the original
table named something like Active. When you don't want to see that record
again, uncheck it. Run all of your queries where you only want to see active
records with the criteria of Yes (no quotation marks) against the Active
field.
Reasons?
(1) You don't have to worry about moving records between tables.
(2) You don't have to worry about duplicates either in the Archive table or
between the two tables.
(3) If you need to search for both active and inactive records, you don't
need an inefficient union query.
About the only reason to archive data is because the database file is
getting near the max size limitation. If that happens you'll need to move the
data to another .mdb file anyway. Might be time to look into another RDBMS
which can hold more data.
One more thing about archived data and I'll get off my soapbox: Archived
data is lost data. Somewhere along the line someone will make a change to the
database which will prevent moving the archived data back into the table
without a lot of work. Seen this way too often. My worse case was data saved
to a tape backup system. When needed we no longer had that kind of tape
backup machine! Once we found on, many of the tapes has unrecoverable errors.
So much for archiving data.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
"Ray S." wrote:
Is there some preferred clean way to archive records without duplicating
archive entries? That is, I want to have a sort of check that will determine
if the data I am appending to an archive table has already been archived to
prevent me from duplicating data in an archive table?
- Follow-Ups:
- RE: Archiving
- From: Ray S.
- RE: Archiving
- From: Ray S.
- RE: Archiving
- Prev by Date: Multiselect parameter query
- Next by Date: Re: SQl query in VBA - SELECT inside an INSERT INTO
- Previous by thread: Multiselect parameter query
- Next by thread: RE: Archiving
- Index(es):
Relevant Pages
|
Loading
