I am looking for some help or general direction. Here is what I am trying to do:
I have an existing table that was exported from another database (Table 1) and includes student records by academic year and an “OtherDbaseID”. However the ID is not unique in this Table 1, as the duplicates of OtherDatabaseID, have fields that change by academic year (see example below).
Table 1 (Child)
OtherDBaseID, FirstName, LastName, AcadYear, OtherFieldText
001, James, Smith, 2016, xxxxx
001, James, Smith, 2017, rrrrrr
002, John, Smith, 2017, eeerse
003, Jim, Doe, 2016, vsfafsdfdsf
003, Jim, Doe, 2018, fasfasdfadsfa
004, Jenny, Doe, 2016, vbnvnnvm
004, Jenny, Doe, 2017, bassdfasdfa
004,Jenny, Doe, 2018, asfasdfdsdf
I want to build a new Parent (Table 2) with a Primary Key that relates fields via the “OtherDbaseID” on Table 1 and 2.
Table 2 (Parent)
Primary Key, OtherDBaseID, FirstName, LastName
1000, 001, James, Smith
1001, 002, John, Smith,
1002, 003, Jim, Doe
1003, 004, Jenny, Doe
Could someone point me in the right direction on:
How can I extract a unique list of OtherDbaseID from the given Table 1, and create a Parent table 2 from this extraction that only list unique OtherDbaseID? Once I have this I was hoping to add a Primary Key and relate the two tables.
Thank you in advance for any and all suggestions.