convert layout

[Guest]

I have a table with the following field names:

Code Date Dept1 Dept2 Dept3 Dept4.....Dept 30
123 10/1 3258 8751
345 10/6 5587


The "Code" field is a unique record and the values for each of the "DeptN"
fields are numeric. I would like to convert this table so there is a
different record for each DeptN value. For instance:

Code Date Dept Value
123 10/1 Dept1 3258
123 10/1 Dept2 8751
345 10/6 Dept3 5587

Can this be accomplished with a query? Any help is greatly appreciated.

Kevin C.

 

[Jim Bunton]

I don't think this is possible with one single query.

However you can do it by using a subroutine.to transfer the old data tyo a
new table
First create a new table e.g.
DeptCodes
------------
DeptCodeId Counter Key
Dept Integer(?) {unique
Code Integer(?) index}
dcDate DateTime [how meaningful is this - is it always the same
for each code?]
-------------

Sub BuildNewTable() ' in pigeon basic! it's a while since!
dim ncols as integer
openrecordset Olddata read only for original table
OldData.movefirst
openrecordset NewData dynaset for New Table
nCols = [number of columns in your original table]
while not OldData.eof
for colno = 2 to nCols -1 [because the first column is numbered 0]
if not isnull(OldData.field(colno)) then
add a record to the new table
col 1 = oldset.field(colno)
col 2 = oldset.fileds(0)
col 3 = oldset filed(1)
end if
next
OldData.movenext
wend
close the recordsets etc
end

check the new data!

 

[Jim Bunton]

I don't think this is possible with one single query.

However you can do it by using a subroutine.to transfer the old data tyo a
new table
First create a new table e.g.
DeptCodes
------------
DeptCodeId Counter Key
Dept Integer(?) {unique
Code Integer(?) index}
dcDate DateTime [how meaningful is this - is it always the same
for each code?]
-------------

Sub BuildNewTable() ' in pigeon basic! it's a while since!
dim ncols as integer
openrecordset Olddata read only for original table
OldData.movefirst
openrecordset NewData dynaset for New Table
nCols = [number of columns in your original table]
while not OldData.eof
for colno = 2 to nCols -1 [because the first column is numbered 0]
if not isnull(OldData.field(colno)) then
add a record to the new table
col 1 = oldset.field(colno)
col 2 = oldset.fileds(0)
col 3 = oldset filed(1)
end if
next
OldData.movenext
wend
close the recordsets etc
end

check the new data!

Jim