Linearising Access Data

[James G via AccessMonster.com]

Hi,
This may be obvious, but it has been puzzling me and I would have thought
that it comes up quite often…
I been sent a large access database containing a table of the form:

ID Date 1 2 3 4 ..etc
34 01/01/05 4565 73 56 876 ….
34 01/02/05 87 876 786 876 ….

And I want to convert into another Access table in the format:

ID Date Hour Value
34 01/01/05 1 4565
34 01/01/05 2 73
34 01/01/05 3 56
34 01/01/05 4 876
etc..
34 01/02/05 1 87
etc..

Any body got any suggestions on the quickest way of doing this bearing in
mind the original database has close to 90,000 entries so manipulating in
Excel isn’t really possible?

Cheers

James

 

[John Nurick]

Hi James,

First create your table. I strongly recommend you don't use field names
like Date and Hour because they are also the names of common functions
or properties; some people would argue against Value for the same
reason. Numeric field names are also a bad idea.

Then write a union query (the square brackets will hopefully cause the
numerals to be interpreted as field names). Just create a new query,
switch to SQL view and type it in, using your actual names:

SELECT ID, TheDate, 1 AS TheHour, [1] AS TheValue FROM MyTable
UNION
SELECT ID, TheDate, 2 AS TheHour, [2] AS TheValue FROM MyTable
UNION
...and so on...
;

Finally populate the table by using an append query whose data source is
the union query.

There is a limit to the number of UNION clauses allowed in a query. If
necessary, append the first lot of fields as above, and then modify the
union query to use the next lot and run the append query again.

 

[George Nicholson]

-Set up a table with your 4 fields: ID, Date, Hour, Value (well, to be safe,
use something other than "Date" & "Hour" since they are keywords)
-Create a new Append query. Select the table with your old data for the
query, you will be Appending to the new table.
-ID appends to ID & Date appends to Date. The other 2 fields require that
you run the query multiple times, editing it slightly between passes.
-exp1: 1 appends to Hour (make sure QBE does *not* fill in the table name.
You want to append the numerical value, not the Field("1") value.)
-1 appends to Value (make sure QBE *does* fill in the table name since we do
want the value contained in field("1").
-Run query
-change exp1: 1 to exp1: 2 appending to Hour
-change 1 to 2 appending to Value
run query
repeat as necessary for all hour fields in OldData

The SQL would look something like:
INSERT INTO [NewData] ( ID, [Date], [Hour], [Value] )
SELECT [OldData].ID, [OldData].Date, 1 AS Exp1, [OldData].[1] AS Exp2
FROM [OldData];

The trick is to be sure that the numbers 1,2,3, etc. append to Hour, *not*
the contents of the fields with those names and that the Fields called
1,2,3, etc. get appended to Value.

If you only have to do this once, re-running the query 24 times is a pain,
but bearable. If you will have to do this more than once, or if you prefer,
the above SQL could be put into a VBA loop.

Dim i as Integer
Dim strSQL as String

For i = 1 to 24
strSQL = "INSERT INTO [NewData] ( ID, [Date], [Hour], [Value] ) "
strSQL = strSQL & "SELECT [OldData].ID, [OldData].Date, " & i & " AS
Exp1, [OldData].[" & i & "] AS Exp2 "
strSQL = strSQL & "FROM [OldData];"
DoCmd.RunSQL strSQL
Next i

HTH,

 

[James G via AccessMonster.com]

Thanks for the advice guys, I managed to get things sorted..!

James