Importing data from two tables as an ID reference

[Pawtang]

I am trying to build a normalized database from a few Excel data sources, and one general problem I run into is translating direct text relationships into a relationship between a primary table and a reference table, referencing the ID from the primary table.



For example, I have a tblComponents ([ID], [Component], [Description], [kCategory1], [kCategory2] ,[kCategory3]) containing all of my components. Each of these can have up to three levels of categorization, which in this table is represented by three Number fields. These number fields should point to the PK of a reference table, which contains all of the categories and their levels ([ID], [Category], [categoryLevel]).

I have imported a table with four columns: component (text) and three category columns (text).

I want to match up the first category name with the reference table's text column, then bring the corresponding number into the tblComponents table. is there any clean way to do this easily? This is a problem I've run into in a bunch of different contexts when translating csv's to databases.

[Gicu]

What you have is not normalized; you need a join table with three fields: ComponentCategoryID (autonumber, PK), ComponentID_FK (Number, Long - foreign key link to tblComponent),CategoryID_FK (Number,Long, foreign key link to tblCategory).

Same thing for tblProcurementMast you shouldn't have fields that act as data (2021Quantity and 2021Spend) - you need another table with a proper structure: ProcValuesID (PK, autonumber), ProcurementID_FK(number, long), Quantity, Spend, ProcurementYear.

Cheers,

[Pawtang]

What you have is not normalized; you need a join table with three fields: ComponentCategoryID (autonumber, PK), ComponentID_FK (Number, Long - foreign key link to tblComponent),CategoryID_FK (Number,Long, foreign key link to tblCategory).

Same thing for tblProcurementMast you shouldn't have fields that act as data (2021Quantity and 2021Spend) - you need another table with a proper structure: ProcValuesID (PK, autonumber), ProcurementID_FK(number, long), Quantity, Spend, ProcurementYear.

Cheers,

tblProcurementMast is just an imported spreadsheet from which I am pulling data to populate the database. My goal was to establish these as temporary relationships simply for the importing process, after which tblComponents would have a numerical FK for each category field pointing to a PK number in the categories reference table. The tblProcurementMAST table would be removed from database once importing is complete

[Gicu]

Same thing applies to this; create a proper table structure then run three append queries to move the data from the procurement table into a temp table with one category (each of the three appends pushes one of the three Kearney_L... fields into a Category field).

Cheers,