Normalising (?) data

[igillesp]

Hi all

I often have a requirement to 'normalise' (not sure if that's quite the right term?) population data with lots of age columns (View attachment Original.xls) into a single table with few columns and multiple rows (View attachment Final.xls).

I wrote the code below to do this. Briefly, it:

i) Cycles through the field names of the original data table and uses these to:
ii) modify a select query which pulls back the data for that column and
iii) use this select query as the source for an append query to add the data to the final data table.

I thought I'd post it in case it was any use to anyone else doing similar tasks, and to see if there's a simpler way of doing it.

Iain

Sub Normalise()
 
Dim db As Database
Dim tbl As TableDef
Dim qSelect_txt As String 'SQL string for the select query
Dim qAppend_txt As String 'SQL string for the append query
Dim qdfNewSel As DAO.QueryDef 'New select query def
Dim qdfNewApp As DAO.QueryDef 'New append query def
 
DoCmd.SetWarnings False
'1. Clear the previous results from Final
DoCmd.RunSQL ("DELETE From Final")
'2. Pick up the field names from Ori
Set db = CurrentDb()
Set tbl = db.TableDefs("Ori")
'Start from the third column in, change the query def to include the correct age and append
For i = 2 To tbl.Fields.Count - 1  'NB Need to adjust number depending on columns to skip
 
    '3. Modify qSelect Query Def for each age
 
        qSelect_txt = "SELECT Ori.LSOA, Ori.Sex, Ori.[" & tbl.Fields(i).Name & "] AS [Count] FROM Ori;"
 
    With CurrentDb
        .QueryDefs.Delete ("qSelect")
        Set qdfNewSel = .CreateQueryDef("qSelect", qSelect_txt)
        .Close
    End With
 
    '4. Modify qAppend query def for each age
 
        qAppend_txt = "INSERT INTO Final ( LSOA, Sex, Age, [Count] ) " & _
                      "SELECT qSelect.LSOA, qSelect.Sex, '" & tbl.Fields(i).Name & "' AS Age, qSelect.Count " & _
                      "FROM qSelect;"
 
    With CurrentDb
        .QueryDefs.Delete ("qAppend")
        Set qdfNewApp = .CreateQueryDef("qAppend", qAppend_txt)
        .Close
    End With
 
    '5. Run qAppend
 
    DoCmd.OpenQuery "qAppend"
Next i
DoCmd.SetWarnings True
MsgBox "Done"
End Sub

 

[Steve R.]

Here are a couple write-ups on normalization. Wikipedia Article on Database Normalization and Fred Coulson Tutorial

However, I have a more fundamental question. Why do you want to use a relational database program such as Access?

It seems that you are proposing to analyze a set of data. While Access can do that, a more appropriate use of Access is data management.

 

[igillesp]

Not using it for analysis in this instance - just data manipulation.

Iain

 

[Steve R.]

Not using it for analysis in this instance - just data manipulation.

But why MS Access? If the data is already in Excel, why not use Excel?

If you are intent on using MS Access first import the data (as is) into Access then manipulate it. DoCmd.TransferSpreadsheet or Import from Excel - Messy. There are many posts on this website concerning importing Excel data into Access.

 

[igillesp]

Because the average dataset has 86 columns (single year of age to 84, plus 85+ years) and 64,964 rows (32,482 geographical locations with data for both males and females), and therefore you end up with over five and a half million records.

Getting the data in was easy, but writing the code was more difficult, so I thought I'd post it in case someone else had a similar task and found it useful.

Iain