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)