[AccessD] Create Junction Table From Existing Data *SOLVED*

Although it seemed, at first, rather complicated, the union query worked
great.  My only thought was I wish there was a simpler way to turn a union
query into a Make-Table query:(  But we can't have everything, can we?


Mark


-----Original Message-----
From: Gustav Brock [mailto:gustav at cactus.dk] 
Sent: Wednesday, April 21, 2004 5:16 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Create Junction Table From Existing Data


Hi Mark

I would go with the union query as well.

The ID can be added by a function like this:

<code>

Public Function SequentialID( _
  ByVal booReset As Boolean, _
  Optional ByVal varDummy, _
  Optional ByVal intIncrement As Integer = 1, _
  Optional ByVal lngInitialID As Long) As Long
  
  ' Increments static variable lngCurrentID with intIncrement.
  ' Returns the new value of lngCurrentID.
  ' Parameter varDummy is used to force repeated calls of
  ' this function when used in a query.
  '
  ' Reset to start counting from zero incrementing by one:
  '   Call SequentialID(True)
  ' Reset to start counting from 1000:
  '   Call SequentialID(True, Null, 1, 1000)
  ' Reset to start counting from zero incrementing by 2:
  '   Call SequentialID(True, Null, 2)
  ' Reset to start counting from -2000 incrementing by -8
  ' and returning initial ID:
  '   lngID = SequentialID(True, Null, -8, -2000)
  '
  ' Retrieve the current ID:
  '   lngID = SequentialID(False)
  ' Do a count by one and retrieve the current ID:
  '   lngID = SequentialID(False, Null, 1)
  ' Do a count by one in a query and retrieve the current ID:
  '   lngID = SequentialID(False, [fldAnyField], 1)
  ' Do a count by minus two and retrieve the current ID:
  '   lngID = SequentialID(False, varAny, -2)
  '
  ' 2001-12-13. Cactus Data ApS, CPH.
  
  Static lngCurrentID As Long
  Dim intSgn          As Integer
  
  If booReset = True Then
    ' Reset ID.
    lngCurrentID = lngInitialID
  ElseIf Not intIncrement = 0 Then
    intSgn = Sgn(intIncrement)
    If intSgn * lngCurrentID < intSgn * lngInitialID Then
      ' Reset ID.
      lngCurrentID = lngInitialID
    Else
      ' Increment ID.
      lngCurrentID = lngCurrentID + intIncrement
    End If
  End If
  
  SequentialID = lngCurrentID
  
End Function

</code>

Have fun!

/gustav


> 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
>>
>>
>>
>>> 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

-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com