make single line records into multiple lines - tricky

[Ken Capriell]

I know that subject probably did not adequately explain anything so here
goes...

I have an access file that needs to be transposed from its current format to
a new format so that I can then export as a flat file to be imported into a
shopping cart. This file is to create a set of attributes/options for each
product.

The file I have is in a pretty standard format:

Prod_Code | ATTR_CODE | OPT1 | OPT2 | OPT3 | OPT4 | OPT5
0037 | Color | RED | GREEN |YELLOW |BROWN | BLUE
0037 | Size | SMALL | MED | LARGE | |

The above represents the single product with code 0037. The intended result
is just what one would assume. 2 selectable attributes, color and size and
each with their sub options (5 possible colors and 3 possible sizes).

Ok, the problem is the flat file needs to be structured thusly:

Prod_Code | ATTRCODE | OPTCODE
0037 | Color |RED
0037 | Color |GREEN
0037 | Color |YELLOW
0037 | Color |BROWN
0037 | Color |BLUE
0037 | Size |SMALL
0037 | Size |MED
0037 | Size |LARGE

The software expects incoming data to be one line at a time like that.

So, I need to revamp the structure of this table to make a new line every
time a value in one of the "OPTx" fields is encountered - copying over
whatever values were found in Prod_Code and ATTR_CODE and with the new
column OPTCODE to hold the values removed from the multiple OPTx columns.
It should be noted that some product's attributes have up to 14 options so
it goes all the way to OPT14.

What magical spell must I cast to get where I need to go? Thanks much.

[John Welch]

I would probably use a visual basic routine that reads each record from your
table, and then writes a record to another table for each option code found
in the read record.
Here is some untested code (that I'm sure has errors) that will hopefully
give you some ideas.

first make a table "yourOutputTable", with three fields: Prod_code,
Attr_code, and Option_code

Dim rstInput As DAO.Recordset
Dim rstOutput As DAO.Recordset
dim lngProdCode as long
dim strAttCode as string
dim i as int
dim strOptionCode as string

Set rstInput = CurrentDb.OpenRecordset("YourInputTable")
Set rstOutput = CurrentDb.OpenRecordset("YourOutputTable")
rstInput .MoveFirst

Do While Not rstInput .EOF
lngProdCode = rstInput ("Prod_Code")
strAttCode = rstInput ("Attr_Code")

for i = 3 to 14

'stop if you get to a null field, assuming once you get to a
null, there are no more after that
if isnull (rstInput.fields(i) ) then
exit for
end if

rstOutput .AddNew
rstOutput ("Prod_Code") = lngProdCode
rstOutput ("Attr_Code") = strAttCode
rstOutput ("Option_Code") = rstInput.fields(i)
rstto.Update
next i

rstInput.MoveNext

Loop
rstInput.close
rstOutput.close
Set rstInput = Nothing
Set rstOutput = Nothing
Hope this helps
-John

I know that subject probably did not adequately explain anything so here
goes...

I have an access file that needs to be transposed from its current format
to
a new format so that I can then export as a flat file to be imported into
a
shopping cart. This file is to create a set of attributes/options for
each
product.

The file I have is in a pretty standard format:

Prod_Code | ATTR_CODE | OPT1 | OPT2 | OPT3 | OPT4 | OPT5
0037 | Color | RED | GREEN |YELLOW |BROWN | BLUE
0037 | Size | SMALL | MED | LARGE | |

The above represents the single product with code 0037. The intended
result
is just what one would assume. 2 selectable attributes, color and size
and
each with their sub options (5 possible colors and 3 possible sizes).

Ok, the problem is the flat file needs to be structured thusly:

Prod_Code | ATTRCODE | OPTCODE
0037 | Color |RED
0037 | Color |GREEN
0037 | Color |YELLOW
0037 | Color |BROWN
0037 | Color |BLUE
0037 | Size |SMALL
0037 | Size |MED
0037 | Size |LARGE

The software expects incoming data to be one line at a time like that.

So, I need to revamp the structure of this table to make a new line every
time a value in one of the "OPTx" fields is encountered - copying over
whatever values were found in Prod_Code and ATTR_CODE and with the new
column OPTCODE to hold the values removed from the multiple OPTx columns.
It should be noted that some product's attributes have up to 14 options
so
it goes all the way to OPT14.

What magical spell must I cast to get where I need to go? Thanks much.

[Thelma Lubkin]

John Welch <john(remove)welch@cal(remove)central.com> wrote:

<preliminaries snipped>

: Set rstInput = CurrentDb.OpenRecordset("YourInputTable")
: Set rstOutput = CurrentDb.OpenRecordset("YourOutputTable")
: rstInput .MoveFirst

: Do While Not rstInput .EOF
: lngProdCode = rstInput ("Prod_Code")
: strAttCode = rstInput ("Attr_Code")
'*******If you replace the for and if in the next lines by**********

Dim i as Integer
Do While Not isnull(rst...etc)
i = i+1
you won't need to know the count of possible options, so the code
would not need modification if your options expand

: for i = 3 to 14

: 'stop if you get to a null field, assuming once you get to a
: null, there are no more after that
: if isnull (rstInput.fields(i) ) then
: exit for
: end if

: rstOutput .AddNew
: rstOutput ("Prod_Code") = lngProdCode
: rstOutput ("Attr_Code") = strAttCode
: rstOutput ("Option_Code") = rstInput.fields(i)
: rstto.Update
: next i
' ******* Loop /\ replaces next i ********

: rstInput.MoveNext

: Loop
<snip end cleanup>
--thelma

[Mike Preston]

On 28 Nov 2005 05:25:38 GMT, Thelma Lubkin <th****@alpha2.csd.uwm.edu>
wrote:

John Welch <john(remove)welch@cal(remove)central.com> wrote:

<preliminaries snipped>

: Set rstInput = CurrentDb.OpenRecordset("YourInputTable")
: Set rstOutput = CurrentDb.OpenRecordset("YourOutputTable")
: rstInput .MoveFirst

: Do While Not rstInput .EOF
: lngProdCode = rstInput ("Prod_Code")
: strAttCode = rstInput ("Attr_Code")
'*******If you replace the for and if in the next lines by**********

Dim i as Integer
Do While Not isnull(rst...etc)
i = i+1
you won't need to know the count of possible options, so the code
would not need modification if your options expand

: for i = 3 to 14

: 'stop if you get to a null field, assuming once you get to a
: null, there are no more after that
: if isnull (rstInput.fields(i) ) then
: exit for
: end if

: rstOutput .AddNew
: rstOutput ("Prod_Code") = lngProdCode
: rstOutput ("Attr_Code") = strAttCode
: rstOutput ("Option_Code") = rstInput.fields(i)
: rstto.Update
s/b

rstOutput.Update
: next i
' ******* Loop /\ replaces next i ********

: rstInput.MoveNext

: Loop
<snip end cleanup>
--thelma

[Terry Kreft]

Something like

SELECT Prod_Code, ATTR_CODE as ATTRCODE, OPT1 as OPTCODE
FROM tblProds
WHERE OPT1 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT2
FROM tblProds
WHERE OPT2 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT3
FROM tblProds
WHERE OPT3 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT4
FROM tblProds
WHERE OPT4 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT5
FROM tblProds
WHERE OPT5 Is Not Null
ORDER BY Prod_Code, ATTRCODE, OPTCODE

--
Terry Kreft

"Ken Capriell" <kc**@NOSPAM.COM> wrote in message
news:97*******************@216.196.97.136...

I know that subject probably did not adequately explain anything so here
goes...

I have an access file that needs to be transposed from its current format
to
a new format so that I can then export as a flat file to be imported into
a
shopping cart. This file is to create a set of attributes/options for
each
product.

The file I have is in a pretty standard format:

Prod_Code | ATTR_CODE | OPT1 | OPT2 | OPT3 | OPT4 | OPT5
0037 | Color | RED | GREEN |YELLOW |BROWN | BLUE
0037 | Size | SMALL | MED | LARGE | |

The above represents the single product with code 0037. The intended
result
is just what one would assume. 2 selectable attributes, color and size
and
each with their sub options (5 possible colors and 3 possible sizes).

Ok, the problem is the flat file needs to be structured thusly:

Prod_Code | ATTRCODE | OPTCODE
0037 | Color |RED
0037 | Color |GREEN
0037 | Color |YELLOW
0037 | Color |BROWN
0037 | Color |BLUE
0037 | Size |SMALL
0037 | Size |MED
0037 | Size |LARGE

The software expects incoming data to be one line at a time like that.

So, I need to revamp the structure of this table to make a new line every
time a value in one of the "OPTx" fields is encountered - copying over
whatever values were found in Prod_Code and ATTR_CODE and with the new
column OPTCODE to hold the values removed from the multiple OPTx columns.
It should be noted that some product's attributes have up to 14 options so
it goes all the way to OPT14.

What magical spell must I cast to get where I need to go? Thanks much.

[Terry Kreft]

Or maybe better

SELECT Prod_Code, ATTRCODE, OPTCODE
FROM
(

SELECT Prod_Code, ATTR_CODE as ATTRCODE, OPT1 as OPTCODE, 1 as OB
FROM tblProds
WHERE OPT1 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT2, 2 as OB
FROM tblProds
WHERE OPT2 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT3, 3 AS OB
FROM tblProds
WHERE OPT3 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT4, 4 AS OB
FROM tblProds
WHERE OPT4 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT5, 5 AS OB
FROM tblProds
WHERE OPT5 Is Not Null
)
ORDER BY Prod_Code, ATTRCODE, OB


--
Terry Kreft

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:uh********************@karoo.co.uk...

Something like

SELECT Prod_Code, ATTR_CODE as ATTRCODE, OPT1 as OPTCODE
FROM tblProds
WHERE OPT1 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT2
FROM tblProds
WHERE OPT2 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT3
FROM tblProds
WHERE OPT3 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT4
FROM tblProds
WHERE OPT4 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT5
FROM tblProds
WHERE OPT5 Is Not Null
ORDER BY Prod_Code, ATTRCODE, OPTCODE

--
Terry Kreft

"Ken Capriell" <kc**@NOSPAM.COM> wrote in message
news:97*******************@216.196.97.136...

I know that subject probably did not adequately explain anything so here
goes...

I have an access file that needs to be transposed from its current format
to
a new format so that I can then export as a flat file to be imported into
a
shopping cart. This file is to create a set of attributes/options for
each
product.

The file I have is in a pretty standard format:

Prod_Code | ATTR_CODE | OPT1 | OPT2 | OPT3 | OPT4 | OPT5
0037 | Color | RED | GREEN |YELLOW |BROWN | BLUE
0037 | Size | SMALL | MED | LARGE | |

The above represents the single product with code 0037. The intended
result
is just what one would assume. 2 selectable attributes, color and size
and
each with their sub options (5 possible colors and 3 possible sizes).

Ok, the problem is the flat file needs to be structured thusly:

Prod_Code | ATTRCODE | OPTCODE
0037 | Color |RED
0037 | Color |GREEN
0037 | Color |YELLOW
0037 | Color |BROWN
0037 | Color |BLUE
0037 | Size |SMALL
0037 | Size |MED
0037 | Size |LARGE

The software expects incoming data to be one line at a time like that.

So, I need to revamp the structure of this table to make a new line every
time a value in one of the "OPTx" fields is encountered - copying over
whatever values were found in Prod_Code and ATTR_CODE and with the new
column OPTCODE to hold the values removed from the multiple OPTx columns.
It should be noted that some product's attributes have up to 14 options
so
it goes all the way to OPT14.

What magical spell must I cast to get where I need to go? Thanks much.

[David W. Fenton]

"Terry Kreft" <te*********@mps.co.uk> wrote in
news:cx********************@karoo.co.uk:

Or maybe better

SELECT Prod_Code, ATTRCODE, OPTCODE
FROM
(

SELECT Prod_Code, ATTR_CODE as ATTRCODE, OPT1 as OPTCODE, 1 as OB
FROM tblProds
WHERE OPT1 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT2, 2 as OB
FROM tblProds
WHERE OPT2 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT3, 3 AS OB
FROM tblProds
WHERE OPT3 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT4, 4 AS OB
FROM tblProds
WHERE OPT4 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT5, 5 AS OB
FROM tblProds
WHERE OPT5 Is Not Null
)
ORDER BY Prod_Code, ATTRCODE, OB

That's not legal Jet SQL. This would be:

SELECT Prod_Code, ATTRCODE, OPTCODE
FROM
[

SELECT Prod_Code, ATTR_CODE as ATTRCODE, OPT1 as OPTCODE, 1 as OB
FROM tblProds
WHERE OPT1 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT2, 2 as OB
FROM tblProds
WHERE OPT2 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT3, 3 AS OB
FROM tblProds
WHERE OPT3 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT4, 4 AS OB
FROM tblProds
WHERE OPT4 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT5, 5 AS OB
FROM tblProds
WHERE OPT5 Is Not Null
]. As Prods
ORDER BY Prod_Code, ATTRCODE, OB

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Terry Kreft]

Well, it works, so that's ok then.

--
Terry Kreft

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:Xn**********************************@216.196. 97.142...

"Terry Kreft" <te*********@mps.co.uk> wrote in
news:cx********************@karoo.co.uk:

Or maybe better

SELECT Prod_Code, ATTRCODE, OPTCODE
FROM
(

SELECT Prod_Code, ATTR_CODE as ATTRCODE, OPT1 as OPTCODE, 1 as OB
FROM tblProds
WHERE OPT1 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT2, 2 as OB
FROM tblProds
WHERE OPT2 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT3, 3 AS OB
FROM tblProds
WHERE OPT3 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT4, 4 AS OB
FROM tblProds
WHERE OPT4 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT5, 5 AS OB
FROM tblProds
WHERE OPT5 Is Not Null
)
ORDER BY Prod_Code, ATTRCODE, OB

That's not legal Jet SQL. This would be:

SELECT Prod_Code, ATTRCODE, OPTCODE
FROM
[

SELECT Prod_Code, ATTR_CODE as ATTRCODE, OPT1 as OPTCODE, 1 as OB
FROM tblProds
WHERE OPT1 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT2, 2 as OB
FROM tblProds
WHERE OPT2 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT3, 3 AS OB
FROM tblProds
WHERE OPT3 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT4, 4 AS OB
FROM tblProds
WHERE OPT4 Is Not Null
UNION
SELECT Prod_Code, ATTR_CODE, OPT5, 5 AS OB
FROM tblProds
WHERE OPT5 Is Not Null
]. As Prods
ORDER BY Prod_Code, ATTRCODE, OB

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Lyle Fairfield]

TTBOMK
() is fine for JET 4.0
and
[]. AS Whatever is required for JET 3.5 and OK for JET 4.0.

Well, something changed to allow (); so 4.0 is my story and I'm
sticking to it. What else could it be?

[]. AS Whatever has an advantage in that it can be used in 3.5 and 4.0.

() has an advantage in that it's similar to other dialects.

As I doubt that I will ever be required to write 3.5 again, I will
probably use () from now on.