Hi All:
I have 4 excel sheets, from which I need to create 1 Access Table.
Now, The data is in the following format:

Quantity
Date A B
1/1/2008 97 42
1/2/2008 16 9
1/3/2008 59 25
1/4/2008 41 55
1/5/2008 77 8
1/6/2008 70 32
1/7/2008 80 71
1/8/2008 22 27
1/9/2008 36 45
1/10/2008 52 4

Now, I need to transpose this data in such a manner that I can use a
field for both A and B as the Primary Key. In other words, I need the
transposed data in this format:

P_Key Date Quantity
A 1/1/2008 97
A 1/2/2008 16
A 1/3/2008 59
A 1/4/2008 41
A 1/5/2008 77
A 1/6/2008 70
A 1/7/2008 80
A 1/8/2008 22
A 1/9/2008 36
A 1/10/2008 52
B 1/1/2008 42
B 1/2/2008 9
B 1/3/2008 25
B 1/4/2008 55
B 1/5/2008 8
B 1/6/2008 32
B 1/7/2008 71
B 1/8/2008 27
B 1/9/2008 45
B 1/10/2008 4

In this way, I can use the access table to link up with other tables,
to link-up the other data correspoding to "A" and "B".
I know this can be easily done in Excel with a simple transpose, but
this case is different. Like A and B, I have 640 individual items, the
quantities go beyong 1500 rows. And, both Excel and Access cannot
handle more than 255 columns of data, that being the limit.
So, if anybody could help me with this data-transpose problem, I will
really appreciate it !!! I need to fine-tune this database for a
client's data before the end of tomorrow.

Thanks,
Regards,
S.

Sub transpose()

Sh1RowCount = 1
Sh2RowCount = 1

With Sheets("Sheet1")
Do While .Range("A" & Sh1RowCount) < ""
MyDate = .Range("A" & Sh1RowCount)
A_Quant = .Range("B" & Sh1RowCount)
B_Quant = .Range("C" & Sh1RowCount)
With Sheets("Sheet2")
.Range("A" & Sh2RowCount) = "A"
.Range("B" & Sh2RowCount) = MyDate
.Range("C" & Sh2RowCount) = A_Quant
.Range("A" & (Sh2RowCount + 1)) = "B"
.Range("B" & (Sh2RowCount + 1)) = MyDate
.Range("C" & (Sh2RowCount + 1)) = B_Quant
Sh2RowCount = Sh2RowCount + 2
End With
Sh1RowCount = Sh1RowCount + 1
Loop
End With
With Sheets("Sheet2")
Lastrow = Sh2RowCount - 1
Set SortRange = .Range("A1:C" & Lastrow)
SortRange.Sort _
Key1:=Range("A1"), _
Order1:=xlAscending, _
Key2:=Range("B1"), _
Order2:=xlAscending, _
Header:=xlGuess
End With
End Sub
"Sam" wrote:

Hi All:
I have 4 excel sheets, from which I need to create 1 Access Table.
Now, The data is in the following format:

Quantity
Date A B
1/1/2008 97 42
1/2/2008 16 9
1/3/2008 59 25
1/4/2008 41 55
1/5/2008 77 8
1/6/2008 70 32
1/7/2008 80 71
1/8/2008 22 27
1/9/2008 36 45
1/10/2008 52 4

Now, I need to transpose this data in such a manner that I can use a
field for both A and B as the Primary Key. In other words, I need the
transposed data in this format:

P_Key Date Quantity
A 1/1/2008 97
A 1/2/2008 16
A 1/3/2008 59
A 1/4/2008 41
A 1/5/2008 77
A 1/6/2008 70
A 1/7/2008 80
A 1/8/2008 22
A 1/9/2008 36
A 1/10/2008 52
B 1/1/2008 42
B 1/2/2008 9
B 1/3/2008 25
B 1/4/2008 55
B 1/5/2008 8
B 1/6/2008 32
B 1/7/2008 71
B 1/8/2008 27
B 1/9/2008 45
B 1/10/2008 4

In this way, I can use the access table to link up with other tables,
to link-up the other data correspoding to "A" and "B".
I know this can be easily done in Excel with a simple transpose, but
this case is different. Like A and B, I have 640 individual items, the
quantities go beyong 1500 rows. And, both Excel and Access cannot
handle more than 255 columns of data, that being the limit.
So, if anybody could help me with this data-transpose problem, I will
really appreciate it !!! I need to fine-tune this database for a
client's data before the end of tomorrow.

Thanks,
Regards,
S.