[AccessD] Re: Huge Access Database was wrongly named Re: AccessD Digest, Vol 29, Issue 35

Michael R Mattys mmattys at rochester.rr.com
Thu Jul 28 08:40:34 CDT 2005


Connie,

475,000 is not that many records that they needed to
go to such extremes as to make these separate tables
in separate databases.

You should be able to achieve the same result they got
in the subsequent (4th) databases by using simple queries and
manage the searches/edits through there. They may have
somehow changed the data/structure of those tables, though,
and that could be why they're worried.

You'll need a few queries - one based upon the next:

The first query will simply be a date range - fast and simple.
This can only produce a number of records that could possibly
have been entered (from paper records) within that date range.
Results may need to be appended to a temp table to increase
performance.

The second query will be based upon the first (or temp table) and
have another general criteria that the desired records have in common

Edits can take place upon the temp table and then an update query
can edit the original table.

There are also data-mining methods where data is denormalized
and translation tables used to get at data quickly, but I think Charlotte
would be better at explaining this ...
----

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: Thursday, July 28, 2005 12:30 AM
Subject: [AccessD] Re: Huge Access Database was wrongly named Re: AccessD
Digest, Vol 29, Issue 35


>
> Matty,
>
> Yes the data is all entered into the original Database. The 4th DB is
> actrually the data they need to export as a commercial venture (my
headache
> is getting worse LOL). The original data is entered from old records and
> until I create the new relational model with a shiny new SQL backend this
> is the only electronic record of the data. The need is there to be able to
> seperate this based on the date it was entered and store as a historical
> record, Problem is they may need to search the historical data for a
> specific record. I am unsure of the best way to manage this volume of
> records I guess. There is a unique number per record, it is the serial
> number given by the database. The criteria they will search on is a 16
> digit alphanumeric field. It is probably simple, but as I have not dealt
> with the whole historical record managemnt thing I am looking for advice
on
> how to manage this in the interim. That and I wanted to share this as a
> hall of fame use of Access, LOL.
>
> John,
>
> What was the amusing bit??? The use of Access or my amazement.... *grin*
> .... they obviously didn't have access to this group or they would have
> known better huh?
>
> Connie Kamrowski
>
> Analyst/Programmer
> Information Technology
> NSW Department of Primary Industries
> Orange
>
> >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?
>
>
>
> This message is intended for the addressee named and may contain
> confidential information. If you are not the intended recipient or
received
> it in error, please delete the message and notify sender. Views expressed
> are those of the individual sender and are not necessarily the views of
> their organisation.
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com




More information about the AccessD mailing list