Thread: Database Design

K, trying it now!

Dave, you are a genius and I could kiss you (but I won't)!

However, the Cansim isn't loading the Vseries, it is loading number values for some reason but it is so darn close it's not even funny!

Ah, the problem is with the Excel macro. I didn't notice that the V number is in column 4 on one sheet, and column 3 for the rest of them. My previous code tried to always grab it from column 4. Try this instead:

Public Sub PrepForImport()

    Dim i As Integer, j As Integer
    Dim lngNR As Long
    Dim w As Object, ws As Object
    
    For Each w In ThisWorkbook.Worksheets
        Application.DisplayAlerts = False
        If w.Name = "ForImport" Then w.Delete
        Application.DisplayAlerts = True
    Next w

    Worksheets.Add before:=Sheets(1)
    ActiveSheet.Name = "ForImport"
    With Sheets("ForImport")
        .Cells(1, 1).Value = "Date"
        .Cells(1, 2).Value = "Region"
        .Cells(1, 3).Value = "Cansim"
        .Cells(1, 4).Value = "VariableName"
        .Cells(1, 5).Value = "MonthlyValue"
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "ForImport" And ws.Name <> "Index" And ws.Name <> "Other Source (BMO)" And ws.Name <> "Insert" Then
                For i = ws.Cells(1, 3).End(xlDown).Row + 1 To ws.Cells(Rows.Count, 3).End(xlUp).Row
                    For j = 5 To ws.Cells(ws.Cells(1, 3).End(xlDown).Row, ws.Cells(ws.Cells(1, 3).End(xlDown).Row, Columns.Count).End(xlToLeft).Column).Column
                        lngNR = .Cells(Rows.Count, 1).End(xlUp).Row + 1
                        .Cells(lngNR, 1).Value = ws.Cells(ws.Cells(1, 3).End(xlDown).Row, j).Value
                        .Cells(lngNR, 2).Value = ws.Name
                        If ws.Name = "CdnCPI&MjrComp" Then
                            .Cells(lngNR, 3).Value = ws.Cells(i, 4).Value
                        Else
                            .Cells(lngNR, 3).Value = ws.Cells(i, 3).Value
                        End If
                        .Cells(lngNR, 4).Value = ws.Cells(i, 2).Value
                        .Cells(lngNR, 5).Value = ws.Cells(i, j).Value
                    Next j
                Next i
            End If
        Next ws
    End With
    
End Sub

I think this is kind of a sloppy fix, but as long as your template stays the same it will run the fastest.

The other way would probably be to search and find the Cansim cell on each sheet, then refer to the column it is in when finding the v numbers. If you Cansim column ends up switching a lot on future spreadsheets, you would want to use that method instead. Let me know and I can show you how I'd set it up.

I wonder if named ranges would make a difference. I tried on the weekend to rework the Excel Sub but to no avail. For the data extraction from the source, I use countable cansim-named ranges as well as countable dates-named ranges. Regarding the cansim for instance, the ranges are named MBHCansim1, MBHCansim2,...MBHCansim21. Dates = MBHDates1,MBHDates2,...MBHDates21. Using the MBHCansim's I was able to load all of the cansim numbers onto the "ForImport" sheet with the following code

For xCnt = 1 To 21
            CnsmCnt = Range("MBHCansim" & xCnt).Cells.Count
            With Range("MBHCansim" & xCnt)
                
                If xCnt = 1 Then
                    Set ForImportCansim = Sheets("ForImport").Cells(2, 3).Resize(CnsmCnt, 1)
                    Set ForImportVarName = Sheets("ForImport").Cells(2, 4).Resize(CnsmCnt, 1)
                    Set ForImportRegionName = Sheets("ForImport").Cells(2, 2).Resize(CnsmCnt, 1)
                    ForImportCansim.Cells.Value = Range("MBHCansim" & xCnt).Cells.Value
                    ForImportVarName.Cells.Value = Range("MBHCansim" & xCnt).Offset(0, -1).Cells.Value
                    ForImportRegionName.Cells.Value = Range("MBHCansim" & xCnt).Worksheet.Name
                Else
                    Set ForImportCansim = Sheets("ForImport").Cells(1, 3).End(xlDown).Offset(1, 0).Resize(CnsmCnt, 1)
                    Set ForImportVarName = Sheets("ForImport").Cells(2, 4).End(xlDown).Offset(1, 0).Resize(CnsmCnt, 1)
                    Set ForImportRegionName = Sheets("forImport").Cells(2, 2).End(xlDown).Offset(1, 0).Resize(CnsmCnt, 1)
                    ForImportCansim.Cells.Value = Range("MBHCansim" & xCnt).Cells.Value
                    ForImportVarName.Cells.Value = Range("MBHCansim" & xCnt).Offset(0, -1).Cells.Value
                    ForImportRegionName.Cells.Value = Range("MBHCansim" & xCnt).Worksheet.Name
                    
                End If
                'Debug.Print ForImportCansim.Address
            End With
        Next xCnt

As you can see, this loads the Cansim name, the Variable name, and the Region name but now I am stuck trying 1)understand your code here

For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "ForImport" And ws.Name <> "Index" And ws.Name <> "Other Source (BMO)" And ws.Name <> "Insert" Then
                For i = ws.Cells(1, 3).End(xlDown).Row + 1 To ws.Cells(Rows.Count, 3).End(xlUp).Row
                    For j = 5 To ws.Cells(ws.Cells(1, 3).End(xlDown).Row, ws.Cells(ws.Cells(1, 3).End(xlDown).Row, Columns.Count).End(xlToLeft).Column).Column
                        lngNR = .Cells(Rows.Count, 1).End(xlUp).Row + 1
                        .Cells(lngNR, 1).Value = ws.Cells(ws.Cells(1, 3).End(xlDown).Row, j).Value
                        .Cells(lngNR, 2).Value = ws.Name
                        .Cells(lngNR, 3).Value = ws.Cells(i, 4).Value
                        .Cells(lngNR, 4).Value = ws.Cells(i, 2).Value
                        .Cells(lngNR, 5).Value = ws.Cells(i, j).Value
                    Next j
                Next i
            End If
        Next ws

and how it can be applied to what I have done. Is there middle ground?

Originally Posted by davegugg

Ah, the problem is with the Excel macro. I didn't notice that the V number is in column 4 on one sheet, and column 3 for the rest of them.

That is because of how the data comes in on the first sheet, there is one extra column added to the table to the left of Cansim. It cannot be removed on that one sheet (user requirements) but yet it is not necessary in the database (user requirements). I can have it removed I guess and tell the user that it has to be in order to create consistency within the workbook.

For this workbook, and considering that it is now being dumped into a database, should I extract all the source data straight into the "ForImport" without populating all the other sheets? Once it is in the database, the running of my other code for extraction seems redundant. I think I just answered my own question.

Alright, I removed the one column and am using your original code. Hopefully this works. Thanks to you I have learned that there is no room, at all, for inconsistencies when dumping mass amounts of data into Access from Excel.

This seems strange to me, In the Statistics_Canada_CPI table, from row 1635 to 40319, the cansim numbers still do not exist, even though they do in tbl_Import. tbl_Import is properly loaded from excel so that part is now 100% but the final dump is slightly off. I looked in the SQL statement but it seems to be alright. Any clue? Should I re-upload?

Regarding the use of named ranges, I'm not very good at that, so I'm afraid I can't really weigh in on whether or not they could work better. Basically, you need to get all the data into those five columns on the ForImport sheet. How you do it doesn't matter too much, aside from the fact that some ways will take longer to run than others.

Here is the code with comments to explain:

'Loop through each worksheet in this workbook.
        For Each ws In ThisWorkbook.Worksheets
            'We don't want to pull any data from the worksheets listed here, so only proceed if the workbook name isn't one of them.
            If ws.Name <> "ForImport" And ws.Name <> "Index" And ws.Name <> "Other Source (BMO)" And ws.Name <> "Insert" Then
                'Loop through each row in the table of data.
                For i = ws.Cells(1, 3).End(xlDown).Row + 1 To ws.Cells(Rows.Count, 3).End(xlUp).Row
                    'Loop through each column in the table of data.  Everything after the To is just finding the last column of data.
                    For j = 5 To ws.Cells(ws.Cells(1, 3).End(xlDown).Row, ws.Cells(ws.Cells(1, 3).End(xlDown).Row, Columns.Count).End(xlToLeft).Column).Column
                        'Find the next available row on the ForImport sheet.
                        lngNR = .Cells(Rows.Count, 1).End(xlUp).Row + 1
                        'Fill column 1 with the date, usually found in row 7.
                        .Cells(lngNR, 1).Value = ws.Cells(ws.Cells(1, 3).End(xlDown).Row, j).Value
                        'Fill column 2 with the worksheet's name
                        .Cells(lngNR, 2).Value = ws.Name
                        'Fill column 3 with the Cansim v number, found in column 4.
                        .Cells(lngNR, 3).Value = ws.Cells(i, 4).Value
                        'FIll column 4 with the Variable name, found in column 2.
                        .Cells(lngNR, 4).Value = ws.Cells(i, 2).Value
                        'Fill column 5 with the data value.
                        .Cells(lngNR, 5).Value = ws.Cells(i, j).Value
                    Next j
                Next i
            End If
        Next ws

All the j's you see are the current column that is being looped through. All the i's are the current row being looped through.

I don't know why the cansim numbers would not exist, you can upload your db if you are not able to figure it out, and I'll see if I can.

I guess I'll have to try and figure this one out on my own because I cannot upload the file (zipped it is 7,000 + KB while not zipped it is 45,000 + KB).

Thank you for the explanation of the code by the way.

Heck ya I think you've hit pay-dirt with this Dave and thank you. What I did was I deleted all of the records in the Statistics_Canada_CPI table and then re-ran the qry_TransferImport query. Now everything has been added properly. The autonumber is at 400,000 + but that is because of all the times I have run the query. If I remember my basic studies from scholl, that cannot be changed (unless I start from scratch right?). Thanks a whole bunch Dave, I couldn't tap your scales enough to convey my appreciation.

Now comes the learning regarding clever querying.