|
Home > Archive > MS Access database support > April 2006 > need to resort data
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
need to resort data
|
|
|
| Hello.
I have a single column table with data that looks like this:
0581-SS
0581-10K
0581-14K
0581-14KW
0581-GP
0797-10K
0797-SS
0797-14K
0797-GP
0797-14KW
on and on for thousands of records.
The convention is always the same. There are 5 entries with the same
string preceeding the hyphen and one of 5 possible suffixes.
What I need to do is transpose this single field table into one with 5
fields. So the above would become:
0581-SS | 0581-GP | 0581-10K | 0581-14K | 0581-14KW
0797-SS | 0797-GP | 0797-10K | 0797-14K | 0797-14KW
How might that be accomplished?
Thanks.
| |
|
| Hi Eddie,
working on the premise that the first four characters identify the key
that binds the records I would simply roll through the recordset and
build a new table based on the data. For example (DAO 3.6 reference
required). Watch for word wrapping and also you will need to create a
table first corresponding to the field values and adjust the code as
appropriate. If you want something a little more dynamic that is quite
easy as well...but see how this suits your needs.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT DISTINCT fldValOrigin FROM
MSYsTableInformation
ORDER BY fldValOrigin ;")
Do Until rs.EOF
CurrentDb.Execute ("INSERT INTO tbl_NewTable (FldVal1, FldVal2,
FldVal3, FldVal4, FldVal5) VALUES ('" & Left(rs("fldValOrigin"), 4) &
"-SS" & "', '" & Left(rs("fldValOrigin"), 4) & "-GP" & "', '" &
Left(rs("fldValOrigin"), 4) & "-10K" & "' , '" &
Left(rs("fldValOrigin"), 4) & "-14K" & "' , '" &
Left(rs("fldValOrigin"), 4) & "-14KW" & "') ;")
rs.MoveNext
Loop
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Cheers, Anton
| |
|
| Actually, the string preceeding the hyphen may be other legnths besides 4
characters long and may in fact contain it's own hyphen like this:
20-0038-SS
So can what you gave me (thank you!) be reworked to deal with that?
antonric@gmail.com (Anton) wrote in <1144290379.267848.225730
@i39g2000cwa.googlegroups.com>:
>Hi Eddie,
>
>working on the premise that the first four characters identify the key
>that binds the records I would simply roll through the recordset and
>build a new table based on the data. For example (DAO 3.6 reference
>required). Watch for word wrapping and also you will need to create a
>table first corresponding to the field values and adjust the code as
>appropriate. If you want something a little more dynamic that is quite
>easy as well...but see how this suits your needs.
>
>Dim db As DAO.Database
> Dim rs As DAO.Recordset
> Set db = CurrentDb
> Set rs = db.OpenRecordset("SELECT DISTINCT fldValOrigin FROM
> MSYsTableInformation
ORDER BY fldValOrigin ;")
> Do Until rs.EOF
> CurrentDb.Execute ("INSERT INTO tbl_NewTable (FldVal1, FldVal2,
>FldVal3, FldVal4, FldVal5) VALUES ('" & Left(rs("fldValOrigin"), 4) &
>"-SS" & "', '" & Left(rs("fldValOrigin"), 4) & "-GP" & "', '" &
>Left(rs("fldValOrigin"), 4) & "-10K" & "' , '" &
>Left(rs("fldValOrigin"), 4) & "-14K" & "' , '" &
>Left(rs("fldValOrigin"), 4) & "-14KW" & "') ;")
> rs.MoveNext
> Loop
> rs.Close
> db.Close
> Set rs = Nothing
> Set db = Nothing
>
>Cheers, Anton
>
>
| |
| Randy Harris 2006-04-06, 3:29 am |
| Eddie wrote:
> Hello.
>
> I have a single column table with data that looks like this:
>
> 0581-SS
> 0581-10K
> 0581-14K
> 0581-14KW
> 0581-GP
> 0797-10K
> 0797-SS
> 0797-14K
> 0797-GP
> 0797-14KW
>
> on and on for thousands of records.
>
> The convention is always the same. There are 5 entries with the same
> string preceeding the hyphen and one of 5 possible suffixes.
>
> What I need to do is transpose this single field table into one with 5
> fields. So the above would become:
>
> 0581-SS | 0581-GP | 0581-10K | 0581-14K | 0581-14KW
> 0797-SS | 0797-GP | 0797-10K | 0797-14K | 0797-14KW
>
> How might that be accomplished?
>
> Thanks.
Welll... It's rather ugly, but it works - I tested it.
I called the table Xform and the Column Col1.
TRANSFORM First(queryx.Col1) AS FirstOfCol1
SELECT queryx.LP
FROM [SELECT Xform.Col1,
Left([col1],instrrev([col1],"-")-1) AS LP,
Mid([col1],instrrev([col1],"-")+1) AS RP
FROM Xform]. AS queryx
GROUP BY queryx.LP
PIVOT queryx.RP;
Output:
LP 10K 14K 14KW G
P SS
0581 0581-10K 0581-14K 0581-14KW 0581-GP 0581-SS
0797 0797-10K 0797-14K 0797-14KW 0797-GP 0797-SS
32-589 32-589-10K 32-589-14K 32-589-14KW 32-589-GP 32-589-SS
--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
| |
|
| I get an Invalid bracketing of name 'SELECT Xform.Col1, Left([Col1'
error...
>
>Welll... It's rather ugly, but it works - I tested it.
>I called the table Xform and the Column Col1.
>
>TRANSFORM First(queryx.Col1) AS FirstOfCol1
>SELECT queryx.LP
>FROM [SELECT Xform.Col1,
>Left([col1],instrrev([col1],"-")-1) AS LP,
>
>Mid([col1],instrrev([col1],"-")+1) AS RP
>FROM Xform]. AS queryx
>GROUP BY queryx.LP
>PIVOT queryx.RP;
>
>Output:
>
>LP 10K 14K 14KW GP SS
>0581 0581-10K 0581-14K 0581-14KW 0581-GP 0581-SS
>0797 0797-10K 0797-14K 0797-14KW 0797-GP 0797-SS
>32-589 32-589-10K 32-589-14K 32-589-14KW 32-589-GP
>32-589-SS
>
| |
| Randy Harris 2006-04-06, 8:29 pm |
| Eddie wrote:
> I get an Invalid bracketing of name 'SELECT Xform.Col1, Left([Col1'
>
> error...
>
>
>
This might be some sort of version specific behavior. Which version of
Access are you using? A2K put those goofy square brackets in. This is
the way I would write the query:
TRANSFORM First(queryx.Col1) AS FirstOfCol1
SELECT queryx.LP
FROM (SELECT Xform.Col1, Left([col1],instrrev([col1],"-")-1) AS LP,
Mid([col1],instrrev([col1],"-")+1) AS RP
FROM Xform) AS queryx
GROUP BY queryx.LP
PIVOT queryx.RP;
--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
|
|
|
|
|