looking for easy/helpful way to add tables

[mpjm]

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

 

[Jamie Collins]

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.

--

 

[mpjm]

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)