Application to make tables consistent?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good morning,

Does anyone know of an app that I can use to go through my tables and make
the Field Names, Formats, Data Types, etc. all the same? I have a huge
number of tables that I need to bring up to the same structure, and don't
really want to sit here and go through all of them manually. Any ideas would
be appreciated....
Thanks,
Chris
 
Hi Ms. Trapp,

I should have explained that a little better in my first post. I am the
database admin for a group that deals with various contractors in Ontario,
and I need to build a database that will allow for trend analysis,
forecasting, optimisation, etc. Because of the nature of my data (the Order
Numbers for each contractor are not unique, an error on the part of the
programmer that wrote our dbase application), I need to (a) Keep each year
seperate and (b) keep each contractor seperate; this means that I have 3
years X 27 contractors X 7 tables worth of information, and I really don't
want to go through each and every single table verifying the information. I
could probably go through the Documentor, but given the size of my database,
I shudder to think how big the Document would be. The 7 tables are linked by
the Order Number, but not all tables have the same number of Orders - a
result of not all fields being filled in on every order, because if they were
I would use Autonumber for each record, sorting by Order number.
As you can tell, having a quick and dirty way to go through each table to
ensure "integrity" (in quotes because I realise this database is not very
well integrated) is going to make my life a lot easier. And no, I can't have
seperate databases for each contractor because I have people that need to
access all the data to look for trends in specific areas that are looked
after by multiple contractors. How's that for complicated? :-)
Thanks,
Chris
 
Hi Ms. Trapp,

Ah, my wife just loves it when people address me as "Ms." ;-)
I should have explained that a little better in my first post. I am the
database admin for a group that deals with various contractors in Ontario,
and I need to build a database that will allow for trend analysis,
forecasting, optimisation, etc. Because of the nature of my data (the
Order
Numbers for each contractor are not unique, an error on the part of the
programmer that wrote our dbase application), I need to (a) Keep each year
seperate and (b) keep each contractor seperate; this means that I have 3
years X 27 contractors X 7 tables worth of information, and I really don't
want to go through each and every single table verifying the information.
I
could probably go through the Documentor, but given the size of my
database,
I shudder to think how big the Document would be. The 7 tables are linked
by
the Order Number, but not all tables have the same number of Orders - a
result of not all fields being filled in on every order, because if they
were
I would use Autonumber for each record, sorting by Order number.
As you can tell, having a quick and dirty way to go through each table to
ensure "integrity" (in quotes because I realise this database is not very
well integrated) is going to make my life a lot easier. And no, I can't
have
seperate databases for each contractor because I have people that need to
access all the data to look for trends in specific areas that are looked
after by multiple contractors. How's that for complicated? :-)
Click to expand...

Well, it's more complicated that you really want to have to deal with. I
don't know of any "quick and dirty" way to do what you want. There might be
some third party tools available to do that, but I'm not aware of any off
the top of my head. If I were in your shoes. I would take the time to
correct the error made by the programmer and normalize your database. In the
long run you will be glad that you did. It will take quite a bit longer than
just changing the field names and datatypes (beware that changing datatypes
can cause data loss and data corruption), but it will save you tons of
headaches in the future. Even having all the tables with identical structure
will be very difficult to maintain and create queries for.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html
 
Um....Apologies for the Gender Gaffe.......
The problem with the database I'm getting the info from is that it's written
in Clipper52, which I have no idea how to write in. The programmer (who is a
contractor, not an employee, of the company that I work for) is very
protective of "his" database, and as a result sees me as a threat (which I
am, I'm working to get rid of his app and use a SQL-Server based database).
As a result, getting him to supply me with information is worse than pulling
teeth. Getting him to normalise the database would be pretty much
impossible.
What I've decided to do is pull ALL my data from each table type into a
single table, so that I would end up with 7 (large) tables, with my own
Unique ID (a combination of telephone number, date/time rec'd, as these are
three fields that are common to each table) and will link the 7 (large)
tables to each other by my UID. I've tried it with a sample of 1500 tickets,
and it does work, so I'm keeping my fingers crossed that my luck will
continue.
Thank you very much for your help, MR Trapp...... :-)
Chris
 
Um....Apologies for the Gender Gaffe.......


No apologies necessary. It happens all the time and it doesn't bother me.
The problem with the database I'm getting the info from is that it's
written
in Clipper52, which I have no idea how to write in. The programmer (who
is a
contractor, not an employee, of the company that I work for) is very
protective of "his" database, and as a result sees me as a threat (which I
am, I'm working to get rid of his app and use a SQL-Server based
database).
As a result, getting him to supply me with information is worse than
pulling
teeth. Getting him to normalise the database would be pretty much
impossible.
Click to expand...

Well, depending on how your company's contract is written with this
consultant, I would suspect that your company may well own the code and not
him.

Good luck with everything.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html
 
Back
Top