0

I'm struggling with the logic and feasibility of a certain workflow. I have a list in Excel containing DatabaseName, TableName, FieldName, SubtypeName, and DomainName. I'd like to select all of the rows in the Excel and paste into an Access database containing the logically-related tables; the goal is to take the denormalized data from Excel and load it into the normalized tables in Access. These items are all logically related in accordance with this Relationship diagram:

Relationship Diagram

Relationship description:

  • Databases can have multiple Tables
  • Tables can have multiple Fields
  • Tables can have multiple Subtypes
  • Fields can have multiple Subtypes and Subtypes can have multiple Fields
  • Fields can have multiple Domains and Domains can have multiple Fields
  • Subtypes can have multiple Domains and Domains can have multiple Subtypes

Having constructed these tables and relationships in Access, I'd like to be able to copy and paste from the aforementioned Excel, and have all of these tables automatically filling out and creating/relating the primary and foreign keys in all of these tables.

My question is: Is this feasible in one fell swoop? And if not, any step-by-step suggestions? I'm hitting my brain's logical limitations. I do have the Foreign Key fields in all of the tables set up as ComboBoxes with the RowSource being, in the case of FieldID for example, "SELECT ID, FieldName FROM Fields", so that in any table or query context referencing the Foreign Key fields, you can see the nice, descriptive value instead of the actual ID number. Thank you for any and all help!

1 Answer 1

0

You will have to do it in steps. Start by linking the spreadsheet from Access so you can access it as a table. You can then add the unique databases, tables, domains, etc. with a number of append queries. You fill the foreign keys later with update queries. The last step is filling the junction table (MMM...) with an append query. It will no be simple to do, but doable.

3
  • Thank you very much! The suggestion to load in steps is helpful, and is no problem, up until I get to where I have to load the MMM table. I am unsure how to approach loading that table. I know that the append query that's doing the loading somehow has to reference the correct key fields in the adjacent tables. I'm just not sure how to technically execute that. Any suggestions on that front?
    – jdawgx
    Commented Feb 13, 2023 at 13:00
  • I assume that the excel file only contains names? And that in Access the IDs are autonumber fields? In that case, the principle is no different from, for example, linking tables to databases. The only difference is that you have to retrieve IDs from three tables at the same time. In this case you only have an append query where you do that. It is difficult to make an example because then I would have to rebuild the entire database and come up with excel data.
    – XPS35
    Commented Feb 14, 2023 at 8:38
  • Hey, thank you yet again, this did help. The Excel file indeed only contained names, and the ID fields in Access were Autonumber Primary Key fields. I ended up loading in steps as you suggested, starting with the tables that weren't dependent on other tables, and working "inwards" towards the MMM table; any append query that was trying to load into a dependent table (a table that did have a foreign key) needed to "pull in" the adjacent table's key from the row having a text match between all of the Excel source fields and the "Name" fields in the related tables.
    – jdawgx
    Commented Feb 15, 2023 at 12:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.