Forum Discussion
Copying Data From Excel file to Access Table
Hello All,
I have created one Shared Excel File in Access using below option:
Now I want to copy data from this excel file to two different Access Tables one row at a time. How can I achieve this?
Excellent.
There's a fairly straightforward way to deal with this with two queries, and no need for cumbersome recordsets.
First, Append the appropriate field(s) in the new records to the existing table1 (table1 seems like such an ambiguous name, though, are you sure that's it's real name?).
I assume that you have designated a Primary Key for table1 and that that Primary Key is what you need to insert with the records for table2. That's a standard approach, but to be sure, is that how you did this?
Now, you have a new group of records in table1, with a Primary Key and unique values in your other field. You would normally join on Primary Keys/Foreign Keys to append the records to table2. However, since you want to append the newly created Primary Key to table2, you can take advantage of the fact this the required field for the first append is also unique.
Join the original import to table1 on that field. Append the Primary Key from table1 and the other required field from the import into table2. (See why it's so ambiguous to alias tables and fields?)
Query one could be:
INSERT INTO YourTable1NameGoesHere Values(TheRealNameofFieldOneGoesHere)
SELECT Field1
FROM YourImportTableNameGoesHere
Query two could be:
INSERT INTO YourTable2NameGoesHere Values (PrimaryKeyField1NameFromTable1GoesHere, Field2)
SELECT YourTable1NameGoesHere INNER JOIN YourImportTableNameGoesHere
ON YourTable1NameGoesHere.Field1 = YourImportTableNameGoesHere.Field1
Why two tables? Why 1 row at a time? Are you validating the data during that process, one line at a line?
The most typical approach would probably be two queries, one to append data to one destination table and the second for the second table?
How are the two destination tables different? How are they related?
- Two Tables because I want to copy data from Column1 to Table1 and Column2 data to Table2. That's y I am approaching the row by row thing.
Both tables have one field Common "Order Id".
It should be like I read data from Row 1 first then -
Column1 data goes to table1, Order Id gets generated and using that Order Id I want to add Column2 to Table2.The two tables have a one-to-many relationship between them then?
One record in Table1 can be related to one or more records in Table2?
Are the values in "Field1" unique within that import? I assume they have to be or the scenario wouldn't make sense otherwise.
And, btw, I've often noted that trying to "simplify" a question by using only generic terms and descriptions actually makes it harder to communicate what you need.....
