redoing a bad data base design

[Stephen]

Good day

I have inherited several badly designed databases is there a simple way
to edit them rather than going through and doing several hundred changes

the original designer used bad names and was kept on as a consultant to
correct them now after 5 years he is gone

have to change field name "date" to work_date
"time" to time_of_day

to start with these will do about 4 other field names need to be changed

thanks
Smiles

 

[John Spencer]

Products to document the database and do a global find and replace. All
have Access 97 and later versions. Some don't have an Access 2003 version,
but may still work with Access 2003.

Shareware (Try and Buy):
Find and Replace http://www.rickworld.com

Commercial (Try and Buy)
Speed Ferret http://www.moshannon.com

Commercial
Total Access Analyzer http://www.fmsinc.com

Free (For Access XP Only??):
http://www3.bc.sympatico.ca/starthere/findandreplace

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Stephen]

Thank you John for a quick response
Stephen

 

[Stephen]

John I am using Access XP and will go with the free version

one quick question I have tables, queries and forms which have the year
such as 2005 2007 in their names and related items will it remove the
year from the name so it becomes a more universal database?

I do not like cross posting do you look in the access report posting on
this news server I have a question directed to Ken with a subject line
"attention Ken calculated values" which shows this problem. year was
hard wired into data as a mask so all years to this year show as 2005
but query and forms are set to 2007 in there name.

thanks John
Stephen

 

[Jerry Whittle]

If the tables, queries, ect., have 'data' in their titles, there's a very,
very good chance that the databases are fundamentally flawed. If you open up
the Relationship Window are most of the tables joined in some what hopefully
with Referiential Integrity enabled? If not, combined with the iffy naming
conventions, I bet that the databases are a mess.

Therefore you may consider completely redoing them. Gather up requirements
and start from scratch.

 

[Stephen]

They are a mess fortunately now each one is a flat data base
two table at most no relationship between them
The largest has 300 field names one entry per day of the year and a form
is used to fill each record based on what function you are doing at that
time

by the way is there a web site that shows what names should be avoided?
a lot of copying has gone on the main table is common but not shared
between as many as six people and is a check program so it tracks errors
between scheduling, warehouse receiving & shipping, truck loading and
invoicing

It has cost us a lot of extra work but because it is not shared we have
caught a few good thieves and cheaters.

our pointer is a single page report which is compared by sales manager.
then he has to look at the database of the offending user to find the
problem

will look at redoing but it is a lot of work

thanks
Stephen

 

[Stephen]

Oops Access 2000 not so nice thanks John

John I am using Access XP and will go with the free version

one quick question I have tables, queries and forms which have the year
such as 2005 2007 in their names and related items will it remove the
year from the name so it becomes a more universal database?

I do not like cross posting do you look in the access report posting on
this news server I have a question directed to Ken with a subject line
"attention Ken calculated values" which shows this problem. year was
hard wired into data as a mask so all years to this year show as 2005
but query and forms are set to 2007 in there name.

thanks John
Stephen

 

[Stephen]

OK I am going to take the plunge starting this weekend, this is my plan

1) document the old system and its bad points
2) come up with a new document for the database
3) set up new database
4) hope to export old database to excel
5) rename column headers and any other changes
6) import from excel to new database

question does the order of the columns have to match the database or
will access figure that part out what goes where

Thank You
Stephen

 

[Stephen]

OK I am going to take the plunge starting this weekend, this is my plan

1) document the old system and its bad points
2) come up with a new document for the database
3) set up new database
4) hope to export old database to excel
5) rename column headers and any other changes
6) import from excel to new database

question does the order of the columns have to match the database or
will access figure that part out what goes where

Thank You
Stephen

 

[John Spencer]

Instead of exporting to Excel and then importing from Excel you should be
able to import directly from the old database to the new database.

If you are mainly concerned with changing field and table names I would make
a copy of the current database and then use Find and Replace to change the
field and table names on the copy. You would have to do one table name at a
time and one field at a time but it is doable.

Find and Replace is shareware and I found it well worth the cost. If you
don't want to pay, you can download it and try it for a short period. But
if you really use it for this project and it works for you, I would suggest
that the savings in time and effort would require you to compensate the
author.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Stephen]

John I will look at the cost but I am not getting paid or have any funds
to cover this project already I am into about 60hrs of work and have not
started improvements.

each data base is one table with 300 field names about 6 have to be
changed at most date being the most corrected in past

Thank You
Stephen

 

[John Spencer]

You have a problem if you are using Access 2003 or earlier (and I believe
Access 2007 has the same limits).

A table can have a maximum of 255 fields. 300 field names and one table
implies that there are 300 fields (45 more than Access can handle).

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jerry Whittle]

Below is a good source of words that should be avoided. "Date" is bad;
however, "DateOfBirth" is fine.

http://support.microsoft.com/kb/286335/

Also I highly recommend avoiding any special characters including spaces in
field, table or query names. The one exception is the underscore. _

"Complete %" is bad.
"Complete Percent" is almost as bad.
"Complete_Percent" or "CompletePercent" are much better.

 

[Stephen]

thanks that must be why for some users he dropped fields and I have a
split in the master database into two last year causing his main departure

thanks on the past I just have to correct one query before I proceed
posted elsewhere with no response

I have decided to go with 5 smaller tables

 

[Tony Toews [MVP]]

thanks that must be why for some users he dropped fields and I have a
split in the master database into two last year causing his main departure

And he called himself a consultant?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Stephen]

Yes Tony @ $175.00 per day the boss said he was cheap to maintain a
program we paid $40000 for a months work on the original setup

can you help me with my last issue
this is a working query but the vehicle was changed March 16 date is set
for 2005 by default

SELECT MIN([start mileage]) AS [Start of year mileage],
MAX([home mileage]) AS [End of year mileage],
MAX([home mileage])-MIN([start mileage]) AS [Total mileage],
SUM([home mileage]-[start mileage]) AS [Total business mileage],
SUM([gas]) AS [Sum Of gas], SUM([US gas]) AS [Sum Of US gas],
SUM([US gas2]) AS [Sum Of US gas2],
SUM([servise $]) AS [Sum Of servise $],
SUM([us cost]) AS [Sum Of us cost],
SUM([gas pur]) AS [Sum Of gas pur],
SUM([insureance]) AS [Sum Of insureance],
SUM([plate]) AS [Sum Of plate]
FROM [travel 2007];

what I need is total mileage which is as posted but Jan1 to March16 to
be added to March16 to end of year

due to vehicle change I a negative mileage

also is it advisable not to have spaces in any names such as field names