append queries to transfer flat data file into relational tables

[Guest]

Hi,

I am trying to transfer data from one large flat dataset (an excel file)
into normalized tables in access.
I have imported the excel file into a temporary table called tempdata

What is the best of way of doing this transfer, while holding the
relationships? I can get the data into the first relational table easy enough.

This is what I've done so far,

strsql = "INSERT INTO tblsurvey (year_txt, formcode_nm,survey_type_txt,
grade_txt, survey_name_txt ) SELECT DISTINCT year, form, type, grade,
surveyname FROM tempdata"

How do I link the first record I put in the tblsurvey to the other INSERT
INTO statements for the other two tables?

 

[Guest]

You can set up relationships in Tools->Relationships. What you have to do is
make sure the data matches those relationships. That there are no double
records or null records or illegal conversions and so on. I usaully import
the data in to a temp table like u did to take care of these issues before
actually inserting it into table that have relationships, indexes and keys.
This intermediate step might be a pain in the neck but I find usaully
necessary. In order to have a clean transfer in to your actual tables with
relationships and keys.

HTH
Martin

 

[John Vinson]

Hi,

I am trying to transfer data from one large flat dataset (an excel file)
into normalized tables in access.
I have imported the excel file into a temporary table called tempdata

What is the best of way of doing this transfer, while holding the
relationships? I can get the data into the first relational table easy enough.

This is what I've done so far,

strsql = "INSERT INTO tblsurvey (year_txt, formcode_nm,survey_type_txt,
grade_txt, survey_name_txt ) SELECT DISTINCT year, form, type, grade,
surveyname FROM tempdata"

How do I link the first record I put in the tblsurvey to the other INSERT
INTO statements for the other two tables?

You can base additional Append queries on a Join of tblSurvey and
tempdata; pick up the primary key from tblSurvey, and join the two
tables on whichever set of fields identify a given survey.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps