[AccessD] Re: AccessD Digest, Vol 29, Issue 35

...
>They firstly store approximately 475000 records in one table in an
> Access Database, They then run a query on this database which
> creates 385000+ records and sticks them in a second database,
> they then run a query on this one generating 285000 records and
> store this in ... you guessed it Database number 3. To finish it off
> they query again, store the results in a 4th database and use this
> one for day to day business.

Connie, can it be assumed that data-entry is done in the first db?
Or, are you saying that records are transferred from the fourth db
to the first at the end of the day? Perhaps they are even pulling such
data from a different database system?

...
> they wish to archive off some of the records in the largest database.
> They would however need to be able to search and manipulate these
> records. What is the best way to manage this?

If I read into this correctly, there are several records per account and
your new relational system will keep the track of the account number.
As long as there is a Primary Key AutoNumber that can relate to a Long
in the archive table(s), you can just use SQL for searches and edits.
Perhaps it is not this simple, though ... ? What's missing?

----

Michael R. Mattys
Mattys MapLib for Microsoft MapPoint
http://www.mattysconsulting.com



----- Original Message -----
From: <connie.kamrowski at agric.nsw.gov.au>
To: <accessd at databaseadvisors.com>
Sent: Wednesday, July 27, 2005 9:50 PM
Subject: [AccessD] Re: AccessD Digest, Vol 29, Issue 35


>
> Hello List,
>
> Just when it seemed I had all the information I needed up pops another
good
> one.
>
> I have been meeting today with a group of people who have been
implementing
> a rather unique process for managing their data. They firstly store
> approximately 475000 records in one table in an Access Database, They then
> run a query on this database which creates 385000+ records and sticks them
> in a second database, they then run a query on this one generating 285000
> records and store this in ... you guessed it Database number 3. To finish
> it off they query again, store the results in a 4th database and use this
> one for day to day business. The databases range in size from
1.05gigabytes
> back to 370Megabytes. I was in awe.
>
> I am now designing a new relational database to store and manage this
data.
>
> My problem is this, the business drivers for this database have now got
> soem concerns for the integrity of the data (possibly based on the look on
> my face when they showed me the databases). And so while I am redesigning
> and they are finding the money for rebuilding they wish to archive off
some
> of the records in the largest database. They would however need to be able
> to search and manipulate these records. What is the best way to manage
> this?
>
> I am astounded by the way some people use Access,
>
> Connie Kamrowski
>
> Analyst/Programmer
> Information Technology
> NSW Department of Primary Industries
> Orange