Moving Data to "history" database

[Smieszek]

I am looking to move data as a whole, not sure if what I am looking to do is
possible or just wishful thinking.

Setup: I have a database with several tables, some are background type
information, using SSN number as the primary key (only one entry per table).
Other tables are transaction type, multiple entries for each SSN. Everything
is linked and controlled by the SSN’s. I also have a created a duplicate
database (blank of data copy) as a historical storage.

Task: Is there some way that I can, using a query preferably, to transfer
ALL data from ALL table that corresponds to a single SSN to the “history
file†copy of the data base. Is there a way to do this? Any and all help
would be great, Thanks.

 

[NoodNutt]

G'day Smieszek

Are you saying you have a table for every individual SSN, as you stated you
"only have one entry per table", if this is the case WHY. This is not
rational, you should house all SSN's in the one table using the SSN as your
primaryKey and store relavent information pertaining to each of the SSN's in
a seperate table with a ForeignKey.

eg

tblSSNprimary

PrimaryKey - SSNnumber (Set to Unique, No Duplicates)
Textfiled1 - <the name associate with the number>
Textfiled2 ........Textfiled50 - <any other additional information>

tblSSNforeignKey

ForeignKey - SSNnumber (Set to Number, Long Integer, Duplicates OK)

then you can add any type of fields you like for collecting whatever
information that is relavent for historical purposes.

you can add as many ForeignKey tables as you like containing whatever
background info you want also.

As far as your backup for historical purposes, you can save/backup the DB
into a different folder using the date to identify it

eg

C:\yourfolder\myDbBackup_20080430.mdb (or MDE if it is converted)

If you have concerns about your DB's size ei bloating, you should Compact it
on a regular basis. Depending on the AccVer you are using this can be done
via the toolbar or you could code it to compact everytime the DB is closed.

HTH
Mark.

 

[John W. Vinson]

Setup: I have a database with several tables, some are background type
information, using SSN number as the primary key (only one entry per table).
Other tables are transaction type, multiple entries for each SSN. Everything
is linked and controlled by the SSN’s. I also have a created a duplicate
database (blank of data copy) as a historical storage.

I suspect that what you have is one *record* per SSN, not one *table* per SSN
- I can see why NoodNut might have been confused though!

Task: Is there some way that I can, using a query preferably, to transfer
ALL data from ALL table that corresponds to a single SSN to the “history
file” copy of the data base. Is there a way to do this? Any and all help
would be great, Thanks.

You can't with a single query; you'll need multiple append queries to copy the
data, and (if desired) multiple Delete queries to remove the data from the
main table. The queries can be run from a Macro or (better) from VBA code,
using a Transaction to ensure that either everything gets done or nothing
changes. Unless you have a compelling reason to do this rather complex set of
operations, though, consider just adding an "Archived" yes/no field to each
table; set it to True to "archive" the data, and base your forms and combo
boxes and so on on Queries selecting only un-archived records.

A compelling reason would be a database size approaching 2 GByte (compacted),
demonstrably impaired performance, or the like.