Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2006
    Posts
    2

    Unanswered: Automating relationships/primary keys

    Ok, I'm coming here for help...

    I'm trying to create an automated way to import 3 CSV files and then create a relationship between the three on an ID_NO field, which is consistant in all three tables. I've gotten to the point where I have the files imported into tables (automated via a form and VBA code), but I'm stumped on how to relate them.

    The ID_NO field unfortunately can't be the primary key, because on 2 of the 3 tables there are multiple records with the same ID_NO, so I'm guessing I should build in an iterative number assigned as files are imported, then just do a join on the ID_NO fields with it not set as the primary key.

    However, I can easily do this manually, but I need to automate this so someone who has no Access skills can do it all with a few clicks of the mouse, and once I get into VBA code and macros, I start to get a bit lost. If anyone can help or point me in the right direction, I'd really appreciate it. Thanks!

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Relationships on import files

    Just an option (you'll probably get other options)...

    For example on 2 csv files and establishing a relationship on the 1st csv with the 2nd csv.

    1. Import the 1st primary csv file (you might be able to do an import specification on the 1st csv file which would import it into a table with a primary key field or import the csv file into a temp table (i.e. "TmpCSV1") and then run an append query based on "TmpCSV1" to append it to a "CSV1" table which has the primary key field. You'll want to also have some field to distinguish this csv imported file from future csv files to be imported (i.e. a "DateImported" field which stamps it with today's date or a "FileNameCSV" field inputed by the user, etc.) You can populate this field in the CSV1 table during the append query. You want to do this especially if you're going to be importing more of these csv files and continuosly adding them to the same CSV1 table. DateImported is a good logical choice and you can easily put DE: date() in the header row of the append query to append it the DateImported field in CSV1. If you're importing more than 1 set of csv files in a single day, you may also want to add in a FileNameCSV field to further distinguish this csv dataset. You can put something like FN: [Enter File Name:] in the header row of the append query and append that field to a field name called: FileNameCSV in the CSV1 table.

    2. For the 2nd csv file, import that to a temp table (i.e. "TmpCSV2") and run an append query to append the records to a table which has the Foreign Key in another table (i.e. "CSV2") and also has the same distinguishing field (i.e. "DateImported" or "FileNameCSV") in "CSV2". Populate the distinguishing (DateImported or FileNameCSV) field in the append query. So CSV2 now has an unpopulated Foreign Key field (which you're going to update with the Primary Key from CSV1) and a field to distinguish the dataset your importing.

    3. You'll need to do some sort of field matching between CSV1 and CSV2 to distinguish which records in CSV2 relate to CSV1 (i.e. DateImported/FileNameCSV, and AcctNumber) (or DateImported/FileNameCSV and CustomerName), etc... CSV2 should have some field(s) which tell you to which record in CSV1 they match to. It could be 1 field such as AcctNumber or a combination of 2-4 fields (besides using the DateImported or FileNameCSV field). Design an update query which links CSV1 and CSV2 together (i.e. linked by DateImported/FileNameCSV and AcctNumber) or (DateImported/FileNameCSV and CustomerName) or (DateImported/FileNameCSV and CustomerName and Address) and run the update query which updates the Foreign Key field in CSV2 with the Primary Key field in CSV1.
    4. Do the same type of thing with the 3rd csv file.

    The key thing is that your first csv file needs to have some sort of field which uniquely relates it to 1 or more records in the 2nd and 3rd csv fields. If not, then you'll have to use another type of strategy. A field such as AcctNumber might be a good enough unique field to do it (combined with the DateImported and/or a FileNameCSV field to distinguish that dataset being imported). CustomerName might not be a good choice because there could be 2 John Smiths in your first csv file which are different which would end up populating the Foreign key in the 2nd csv (in the update query) with the wrong John Smith Primary Key. But a CustomerName and Address field name together could be uniquely enough to match the 1st csv with the 2nd and 3rd csv files.

    Hope that helps/makes sense.
    Last edited by pkstormy; 07-18-06 at 16:07.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •