make single line records into multiple lines - tricky

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.

re: make single line records into multiple lines - tricky

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



[color=blue]
>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.[/color]

re: make single line records into multiple lines - tricky

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

re: make single line records into multiple lines - tricky

On 28 Nov 2005 05:25:38 GMT, Thelma Lubkin <thelma@alpha2.csd.uwm.edu>
wrote:
[color=blue]
>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[/color]

s/b

rstOutput.Update


[color=blue]
>: next i
>' ******* Loop /\ replaces next i ********
>
>: rstInput.MoveNext
>
>: Loop
> <snip end cleanup>
> --thelma[/color]

re: make single line records into multiple lines - tricky

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" <kcap@NOSPAM.COM> wrote in message
news:971BA9A9Bkarlcsueduco@216.196.97.136...[color=blue]
>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.[/color]

re: make single line records into multiple lines - tricky

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" <terry.kreft@mps.co.uk> wrote in message
news:uhmdnVE-VrPEYBfeSa8jmw@karoo.co.uk...[color=blue]
> 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" <kcap@NOSPAM.COM> wrote in message
> news:971BA9A9Bkarlcsueduco@216.196.97.136...[color=green]
>>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.[/color]
>
>[/color]

re: make single line records into multiple lines - tricky

"Terry Kreft" <terry.kreft@mps.co.uk> wrote in
news:cxqdndMiu5eJYxfeSa8jmw@karoo.co.uk:
[color=blue]
> 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[/color]

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

re: make single line records into multiple lines - tricky

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

--
Terry Kreft



"David W. Fenton" <dXXXfenton@bway.net.invalid> wrote in message
news:Xns971CB8E66BA50dfentonbwaynetinvali@216.196. 97.142...[color=blue]
> "Terry Kreft" <terry.kreft@mps.co.uk> wrote in
> news:cxqdndMiu5eJYxfeSa8jmw@karoo.co.uk:
>[color=green]
>> 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[/color]
>
> 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[/color]

re: make single line records into multiple lines - tricky

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.