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