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

Charlotte Foust cfoust at infostatsystems.com
Thu Jul 28 10:34:36 CDT 2005


Connie,

I just have to ask:  how many FIELDS are in that single table??  I
suspect it can be broken into multiple tables in a relational scheme and
the users will never know the difference, but the data mining they want
to do and the archiving requires a slightly different design approach,
so you probably will still wind up with two databases, one for data
entry/capture and one for data warehousing/archiving.  

I highly recommend Ralph Kimball's book The Data Warehouse Toolkit
(watch out for a wrap)
http://www.amazon.com/exec/obidos/tg/detail/-/0471153370/002-6258971-950
0008?v=glance as a reference for building dimensional data warehouses.
Once you get your head around the concepts, including the star schema,
the design is simply a matter of how you want to use it.  Data retrieval
from dimensional data warehouses is very fast and requires minimal
querying because of the nature of the structure. 

Charlotte Foust


-----Original Message-----
From: connie.kamrowski at agric.nsw.gov.au
[mailto:connie.kamrowski at agric.nsw.gov.au] 
Sent: Wednesday, July 27, 2005 9:31 PM
To: accessd at databaseadvisors.com
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