I've been reading posts on this issue and am sooo behind the curve.

I have a db that works to keep track of Dogs, Adopters, and Adoptions using a Dogs table, an Adopters table, an AdoptionDetails table, and a junction table. Dogs, Adopters and Adoptions each use an autonumber field as the primary key.

It works fine as long as new info is being added.

HOWEVER, I expect that many users will want to import data from Excel worksheets that have a single record that includes both Dog and Adopter info, combined in one row.

How do I approach this problem conceptually to make it (relatively) easy for users to import their data to the proper tables and maintain the relationships?

I need some really basic info to get started. Am I creating a Query or what?

Sorry to be such an ignoramus, but if you can point me in the right direction maybe the existing posts will make more sense to me.

Thanks!

Jan

I generally just import all the Excel data into a BRAND NEW table. Then I decide which fields in the table to append to which already-existing tables and develop queries to do that.

In your case, your tables already exist, it should be no problem once you have the imported data to develop an Append query to put the fields in the imported data into the correct destination table.

Thanks for the very prompt reply, Frank! You've helped me a number of times, indirectly, as I've found answers in your posts to others.

What I'm not quite "getting" is this:

When I import half of the Excel worksheet to the Dogs table, and half to the Adopters table, each record will be assigned an Autonumber ID. How do I transfer information to get them matched up in my junction table?

Thanks!

Jan

To do that you need to redundantly store the for instance Dogs name and the Adopters name in the junction table. (Or even a combination of fields if that is what you need.)

Then you can use join the Dogs table to the junction table on the redundant fields and use an update query to move the PK from the Dogs table to the junction table. When happy, delete the Dogs name field from the junction table.

The same for the Adopters table.

Thanks, Frank! (I'm using this phrase so often, I should store it as a macro!) That's the info I needed!

Jan

Jan, you're welcome. I am glad I was able to help.

Here is an Excel Import I have, though it may take a little customizing.

It is set up to only look in the selected folder (though it can be made to drill down by uncommenting the designated line in Module mdlFindFiles, Function FindFilesAPI.

You can also include any file type in the search by removing the criteria from the lstExtensions RowSource (it says ".xls" to limit to Excel files).

My import is only looking at the first column, but you can really skip most of the row-by-row analysis stuff and only do DelRows and ExcelImport.

Sean.