Access 2010 Need help connecting/updating database data

Question

I have a large database that has sporadic fields with incomplete or missing data. Quite the inherited project, I have to say. I'm new to Access, so if you have any ideas on how to simplify this pile of awesomeness, I'd love to hear them. Here's a bit of a primer on my situation:

Columns in tblMaster(yes, it's a mess. Manufacturing what we do uses a lot of parts):
itemNumber
fittingNumber 1-13
fittingQuantity 1-13
fittingDescription 1-13
fittingInfo 1-13
fittingColor 1-13

Columns in tblDescriptions:
fittingNumber
fittingDescription
fittingInfo
fittingColor

This sprawls over thousands of records. The problem is that the table of descriptions was incomplete when it was initially imported from Excel, and thus there's a ton of records in the main table with missing descriptions. I have since updated the descriptions table information to include everything that was missing previously. My entry form uses the updated information just fine, but the stuff prior is incomplete. Here's what the tables look like. tblMaster has columns for the fitting IDs, though they're not in the screenshot.

My question is this: If each of those fitting columns in tblMaster is a separate fitting, how do I match each of those IDs to their respective descriptions in the other table and update the main table with the completed information? I've been looking all over the place on how to do this; All I've seen is stuff on lookup tables and I don't want to use those for obvious reasons. I'm about to scrap it and start over.

I would greatly appreciate any input on this.
Thanks

Answer 1

Alright guys, here's what I did. I basically exported what I had, brought it into Excel, and wrote some code to restructure the data. Now I have more records, but far less columns. Now each Fitting ID will easily correspond to its respective description in the second table. and here's the code for anyone who's interested:

Private Sub restructure()
Dim sh1 As Worksheet
Dim sh2 As Worksheet

Set sh1 = ThisWorkbook.Worksheets("tblMaster")
Set sh2 = ThisWorkbook.Worksheets("tblNew")

Dim lastRow As Long
Dim lastCol As Long

Dim sourceRow As Long
Dim sourceCol As Long
Dim destRow As Long
Dim destCol As Long
Dim initRow As Long
Dim initCol As Long

Dim n As Long
Dim m As Long
Dim coolerID As String


lastRow = sh1.Range("A2", sh1.Range("A2").End(xlDown)).Rows.Count
lastCol = 20

sourceRow = 2
sourceCol = 2
destRow = 2
destCol = 2
initRow = 2
initCol = 2


For n = 2 To lastRow

    coolerID = sh1.Cells(n, sourceCol - 1).Value

    For m = 2 To lastCol

        sh2.Cells(destRow, destCol - 1).Value = sh1.Cells(sourceRow, initCol - 1).Value

        If IsEmpty(sh1.Cells(sourceRow, sourceCol)) = False Then

            If sourceCol <= 14 Then
                sh2.Cells(destRow, destCol).Value = sh1.Cells(sourceRow, sourceCol).Value
                sh2.Cells(destRow, destCol + 1).Value = sh1.Cells(sourceRow, sourceCol + 13).Value
                sh2.Cells(destRow, destCol + 2).Value = sh1.Cells(sourceRow, initCol + 26).Value
                sh2.Cells(destRow, destCol + 3).Value = sh1.Cells(sourceRow, initCol + 27).Value
                destRow = destRow + 1
                sourceCol = sourceCol + 1
            ElseIf sourceCol > 14 Then
                destRow = destRow + 1
                destCol = initCol
                sourceRow = sourceRow + 1
                sourceCol = initCol
            End If
        End If
        If IsEmpty(sh1.Cells(sourceRow, sourceCol)) = True Then
            destRow = destRow + 1
            destCol = initCol
            sourceRow = sourceRow + 1
            sourceCol = initCol
        End If
    Next m
Next n

End Sub

So glad I got this figured out! Hope this helps somebody in the future