Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

looking for easy/helpful way to add tables

Discussion in 'Information Technology' started by mpjm@mattmason.org, Jul 28, 2006.

  1. mpjm@mattmason.org

    mpjm@mattmason.org
    Expand Collapse
    Guest

    i just finished designing a database that will have one user. i had to
    migrate and convert all the tables from Lotus Approach to MS Access and
    recreate all the forms. for the past few months i have been working
    with obsolete data and now the time has come to implement the system,
    and i have to convert all of the new data over (which i can do
    easily)...what i'm looking for is any way to easily bring the data over
    without having to recreate the tables...im just a little nervous about
    the implementation of the system and would like some input

    all appreciated
     
  2. Loading...


  3. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    mpjm@mattmason.org wrote:
    > i had to
    > migrate and convert all the tables from Lotus Approach to MS Access
    >
    > now the time has come to implement the system,
    > and i have to convert all of the new data over (which i can do
    > easily)...what i'm looking for is any way to easily bring the data over
    > without having to recreate the tables...im just a little nervous about
    > the implementation of the system and would like some input


    I used to do this for a living a number of years ago when I was called
    a 'senior implementation consultant' and I actually used Access for a
    living.

    What you probably need to ease your mind is some 'reports' of control
    totals. If you can create linked tables in you Access database to the
    Approach tables (or use linked servers in SQL Server, etc) then this
    will be easy to achieve.

    At the most fundamental level you want to know that if you have, say,
    100 rows in an Approach table you want to have 100 rows in the
    respective Access table e.g. SELECT COUNT(*).

    At the next level you will want to know that each of the 100 rows in
    the Access table corresponds to those in the Approach table e.g. you
    don't want the same row duplicated 100 times. This could involve
    creating a FULL OUTER JOIN (in Access this would be the UNION of a LEFT
    OUTER JOIN and a RIGHT OUTER JOIN) on the natural key column(s) (if you
    are using 'surrogate' keys then there is no reason to assume they would
    be the same across paltforms) and test that you have no NULL values in
    the key column(s). A NULL value on the Access side would indicate a
    missing row in the Access table. A NULL on the Approach side would
    indicate a spurious row in the Access table.

    At the final level you will want to know the attribute data (i.e. those
    columns not used in the FULL OUTER JOIN above) is the same in both
    tables (subject to any transformations you have made in migrating the
    data). Joining the tables and comparing the values on a column by
    column basis is an obvious one, here.

    Experience tells me that the client would appreciate seeing such
    reports.

    HTH,
    Jamie.

    --
     
  4. mpjm@mattmason.org

    mpjm@mattmason.org
    Expand Collapse
    Guest

    thanks for your reply.
    i already know how to convert the data ( i had to learn that the hard
    part, as there weren't any tutorials for approach to access to be found
    )

    i suppose i just have a fear that my past four months of work will take
    another week or so just have the new data implemented...but i dont
    think that will truly be a problem....

    i am going to try and convert the new data, convert them into access,
    and rename them to match the existing tables, and see if that works.

    i'll let you know my progress..

    where did you do this access work? and for how long? (just curious)

    Jamie Collins wrote:
    > mpjm@mattmason.org wrote:
    > > i had to
    > > migrate and convert all the tables from Lotus Approach to MS Access
    > >
    > > now the time has come to implement the system,
    > > and i have to convert all of the new data over (which i can do
    > > easily)...what i'm looking for is any way to easily bring the data over
    > > without having to recreate the tables...im just a little nervous about
    > > the implementation of the system and would like some input

    >
    > I used to do this for a living a number of years ago when I was called
    > a 'senior implementation consultant' and I actually used Access for a
    > living.
    >
    > What you probably need to ease your mind is some 'reports' of control
    > totals. If you can create linked tables in you Access database to the
    > Approach tables (or use linked servers in SQL Server, etc) then this
    > will be easy to achieve.
    >
    > At the most fundamental level you want to know that if you have, say,
    > 100 rows in an Approach table you want to have 100 rows in the
    > respective Access table e.g. SELECT COUNT(*).
    >
    > At the next level you will want to know that each of the 100 rows in
    > the Access table corresponds to those in the Approach table e.g. you
    > don't want the same row duplicated 100 times. This could involve
    > creating a FULL OUTER JOIN (in Access this would be the UNION of a LEFT
    > OUTER JOIN and a RIGHT OUTER JOIN) on the natural key column(s) (if you
    > are using 'surrogate' keys then there is no reason to assume they would
    > be the same across paltforms) and test that you have no NULL values in
    > the key column(s). A NULL value on the Access side would indicate a
    > missing row in the Access table. A NULL on the Approach side would
    > indicate a spurious row in the Access table.
    >
    > At the final level you will want to know the attribute data (i.e. those
    > columns not used in the FULL OUTER JOIN above) is the same in both
    > tables (subject to any transformations you have made in migrating the
    > data). Joining the tables and comparing the values on a column by
    > column basis is an obvious one, here.
    >
    > Experience tells me that the client would appreciate seeing such
    > reports.
    >
    > HTH,
    > Jamie.
    >
    > --
     

Share This Page