filling several columns with 0 and 1 (creating dummy variables)

I have a ms-access 2003 table with exp and imp columns. In these exp and imp columns I have 75 countries. I want to create dummy variables exp1-exp75, imp1-imp75, in the same table, showing which country is exp and which country is imp. So for example if exp is Australia (Australia is the 1st country) then exp1 must be 1 and all other exp2-exp75 should be 0. And if imp is开发者_开发技巧 UK (UK is the 5th country), imp5 should be 1 and all the other imp's should be 0. So the table should look like this (if USA is the 3rd and Italy is the 17th country)

exp           imp    exp1 exp2 ...exp17 ... exp75 imp1 imp2 imp3 ... imp5 ... imp75


Australia     UK      1    0        0         0     0    0    0        1        0


Italy         USA     0    0        1         0     0    0    1        0        0

Thanks.

I wrote this in the Access 2007 editor, but the VBA should be the same. I don't think you can do it with queries.

Private Sub FillTable()

Const firstCountry  As Integer = 1
Const lastCountry   As Integer = 75
Const maxRows       As Integer = 234 'or whatever you need...


Dim impCountry  As Integer
Dim expCountry  As Integer

Dim db  As DAO.Database
Dim rs  As DAO.Recordset
Dim i   As Integer

Dim j   As Integer

    'function Random1to75 is left as an exercise for the reader
    impCountry = Random1to75
    expCountry = Random1to75

    Do Until expCountry <> impCountry
        expCountry = Random1to75
    Loop

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("select * from YourTable", dbOpenDynaset)

    For j = 1 To maxRows
        rs.AddNew
            For i = firstCountry To lastCountry
                If i <> impCountry Then
                    rs("imp" & i) = 0
                Else
                    rs("imp" & i) = 1
                End If

                If i <> expCountry Then
                    rs("exp" & i) = 0
                Else
                    rs("exp" & i) = 1
                End If
            Next
        rs.Update
    Next

    rs.Close
    Set rs = Nothing
    Set db = Nothing

End Sub