implementing restructuring in a database (1 Viewer)

[alktrigger]

I recently completed my first database, which IMHO turned out well. My boss liked it so much he commissioned me to do another. Realizing that the 2 databases will carry similar parent tables and structure, I think combining the two tables is a "cleaner" solution. Incorporating the new dataset is not my major concern. My concern lies with the second dataset keeping track of certain information regarding the current status of fields within "tblCustomers" at the point in time that the record is created. This may not be hard to do from a fresh start, but since my original database did not require this, would adding this feature be worth the headache of restructuring my original database?
If so, how would I update the records within the first database which is currently in use (over 3600 records, servicing about 50 customers)?

 

[gemma-the-husky]

this is quite a regular thing to do

you decide to add extra functionality, which needs new fields being added to certian tables. If you can do this without changing the data structure its easier. if you new data means you need to change the table for normalization, then its a bit harder

but its much easier to refine an existing database, than to have two databases doing similar things.

In general add the extra fields

change whatever forms etc need changing

as you say initiallisng data in new columns may be tricky, but this will always be a job you have do, however you implement the new dbs - if your new data is in a spreadsheet already, you ought to be able to find a way to import the spreadsheet to a table, and then write some update queries to set the new columns.

 

[alktrigger]

I started trudging through the updates.

if your new data is in a spreadsheet already, you ought to be able to find a way to import the spreadsheet to a table, and then write some update queries to set the new columns.

I was wondering how I could accomplish this, thanks for the tip.

 

[gemma-the-husky]

how many tables do you have at the omment

all you need is a unique key/join that relates the imported table to the existing table (say the account number)

then you write an update query, that takes the values from one table (ie the new table) and updates the values in the existing table.

90%+ of everything in access is generally done by queries of various sorts

 

[The_Doc_Man]

When I do this, I always do it in a way that seems backwards - but it really isn't. I build an import table. Add a couple of foriegn key fields to the import tables that will point back to the primary keys of the tables to which they might apply. Until I can link every import record to where it is going to be applied, I'm not ready to import. Once I have those links established, a simple JOIN update is all you need.

That's a STRATEGY, not a specific method, because the specifics depend a LOT on the structure both of the real tables and the temporary import tables. The nature of the links between the temporary and permanent tables depends on your application and rules. But the general idea has worked for me many times.

 

[Steve R.]

Interesting post, as I am contemplating modifying (restructuring) my database.

To "test" my program, I have a "development" version separate from the production version in a totally different location. That way you separate the data you shouldn't be touching in the production version so that you can do destructive testing with the development version. My database is split, so there is no problem with periodically copying the production data over to the development database.

In fact, I have made a start at automating the copying of the data into the developmental version. See my post here.