Re: Application to make tables consistent?

Tech-Archive recommends: Fix windows errors by optimizing your registry

> 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? :-)

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


.

Relevant Pages

  • Re: Application to make tables consistent?
    ... database admin for a group that deals with various contractors in Ontario, ... seperate and keep each contractor seperate; this means that I have 3 ... "Lynn Trapp" wrote: ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Basic newbie question about table layout.
    ... A Job may be performed by Employees. ... A Job may require materials. ... A job may be performed by many contractors. ... See "about designing a database" in Access help and feel free to ask ...
    (microsoft.public.access.gettingstarted)
  • Re: How can I import a contact list in outlook to access
    ... contractors in the "my documents". ... Is it possible to import that contact list in outlook to access, but also keep the list in outlook also. ... When the window opened I selected Export to a file and hit next. ... I then had an Access database on my desktop called test.mdb. ...
    (microsoft.public.access.gettingstarted)
  • Re: Search algorithm
    ... give me contractors whos the best matching to some conditions. ... Contractors are stored in DB and number of conditions is up to 20. ... At this moment I have two algorithm: ... If you show the schema to the database and the exact conditions you ...
    (comp.programming)
  • Implementing SQL on files
    ... This feature will add a "table" file type and SQL ... Both the two popular open source database systems, ... most databases implement user management seperate from ... Like to hear some comments and suggessions. ...
    (Linux-Kernel)