[AccessD] Create Junction Table From Existing Data

Hi Mark

How about something like:

SELECT CODE, SA
FROM T1
WHERE SA NOT IS NULL
UNION
SELECT CODE, SAR
FROM T1
WHERE SAR NOT IS NULL
UNION
SELECT CODE, SRD
FROM T1
WHERE SRD NOT IS NULL;

Then you only need to get your PK right
If you really wnat the numbering as indicated you could pass the above query
to a table with a PK as autonumber

There must be a way to do this in the query, I have seen it somewhere, but
where?

Regards

Harry

>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com 
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
>Mitsules, Mark S. (Newport News)
>Sent: Tuesday, April 20, 2004 10:08 PM
>To: 'Access Developers discussion and problem solving'
>Subject: RE: [AccessD] Create Junction Table From Existing Data
>
>
>I did just that to create the primary tables.  But, since I've 
>never had to
>do this on such a grand scale, I need assistance in generating 
>the junction
>table.  Here is pseudo data for clarification.  I've placed dashes to
>maintain alignment.
>
>ID--CODE--SA--SAR--SRD
>1---EA1---SA--SAR--SRD
>2---EEY1--SA
>3---EEY2--SA--SAR--SRD
>
>...from the data above, I need:
>PK--CODE--TYPE
>1---EA1---SA
>2---EA1---SAR
>3---EA1---SRD
>4---EEY1--SA
>5---EEY2--SA
>6---EEY2--SAR
>7---EEY2--SRD
>
>Any suggestions?
>
>Mark
>
>
>
>-----Original Message-----
>From: William Hindman [mailto:wdhindman at bellsouth.net] 
>Sent: Tuesday, April 20, 2004 4:42 PM
>To: Access Developers discussion and problem solving
>Subject: Re: [AccessD] Create Junction Table From Existing Data
>
>
>...I know of no simple way to do this ...my approach has 
>always been to add
>an ANPK to the original table and then to use that as the control to
>populate the derivative tables through a series of queries ...but the
>circumstances change so much from mdb to mdb that I've never 
>been able to
>write code that ever worked for more than one situation.
>
>William Hindman
>"Always code as if the person who is maintaining or testing 
>your code is
>a violent psychopath who knows where you live." William Silverstein
>
>
>----- Original Message ----- 
>From: "Mitsules, Mark S. (Newport News)" <Mark.Mitsules at ngc.com>
>To: "'[AccessD]'" <AccessD at databaseadvisors.com>
>Sent: Tuesday, April 20, 2004 4:23 PM
>Subject: [AccessD] Create Junction Table From Existing Data
>
>
>> Group,
>>
>> Is there a method to create a junction table from a 
>non-normalized table?
>I
>> inherited a table that I now need to normalize into two 
>primary tables and
>a
>> joining junction table.  The primary tables were easy enough 
>to create,
>but
>> how can I create the junction table?
>>
>> I want to take a records such as:
>> Fld1 Fld2 Fld3 Fld4 Fld5 <=Fields
>> Dat1 Fld2 Fld4 Fld5 <=Record
>> Dat2 Fld3 Fld4 <=Record
>>
>> And turn it into:
>> Fld1 FldID <=Fields
>> Dat1 Fld2 <=Record
>> Dat1 Fld4 <=Record
>> Dat1 Fld5 <=Record
>> Dat2 Fld3 <=Record
>> Dat2 Fld4 <=Record
>>
>>
>> Mark
>> -- 
>> _______________________________________________
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>
>
>-- 
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>-- 
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>