adjusting data using vba access

[mmr3b9]

Hi,

I have a data that consist of 5 fields and is as follows:

YRQTR County month1Emp month2Emp month3Emp
19901 031 5 7 6
19902 043 11 21 14


I need to put these five columns into four columns as follows:

Year Month County Employment
1990 1 031 5
1990 2 031 7
1990 3 031 6
1990 4 043 11
1990 5 043 21
1990 6 043 14


19901 means that the year is 1990 and is the first quarter.
There are three months in each quarter. So i need to split each quarter up into individual months. and assign the employment vertically instead of horizontally.

Can someone please help me out with what is the best way to go about this problem. I am using office 2000.


thanks in advance,
mike

 

[dkalel]

Try the following code (I hope this is what you're looking for):


Sub Convert()

Dim X As Integer, SqlString As String

For X = 1 To 3
SqlString = "INSERT INTO Table2 ( [Year], [Month], Employment, County )"
SqlString = SqlString & " SELECT Left([YrQtr],4) AS Expr1, (Right([YrQtr],1)-1)*3+" & X
SqlString = SqlString & " AS Expr2, Table1.Month" & X & "Emp, Table1.County FROM Table1;"
DoCmd.RunSQL SqlString
Next X

End Sub

Table1 contains your original data, Table2 the resultant data, using the field names in your e-mail (not very original). Table2 must already exist, or you'll have to change the query type from an append to a make-table.

Dkalel

 

[mmr3b9]

Thanks a lot Dkalel that's what I was looking for.

Mike