Combining results from every 3 rows of a table 2

[VickyC]

Greetings - I think I'm missing something obvious, but I could use a hand.

My table looks like...

ID(pk) Slot Grp1 Grp2
100 1 X W
101 1 P D
102 1 X E
103 2 U V
104 2 T P
105 2 S Y
106 3 etc...

After every 3 rows, the Slot number increases by 1.

I need to write a query that has this 'un-normalized' structure...

Slot N1 N2 N3 N4 N5 N6
1 X W P D X E
2 U V T P S Y
3 etc...

The values in the 'N?' columns MUST preserve the order they have in the table. Thanks for any assistance.

 

[SkipVought]

h,

So what is the business case for this requirement?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

I made a union query first

qryUnionData

SELECT ID, Slot, Grp1 as GrpValue 
FROM tblData
UNION ALL SELECT
ID, Slot, Grp2 as GrpValue
FROM tblData
ORDER BY 1, 2;

this makes something like

ID	Slot	GrpValue
100	1	W
100	1	X
101	1	D
101	1	P
102	1	E
102	1	X
103	2	V
103	2	U
104	2	P
104	2	T
105	2	Y
105	2	S

Then I did it in code like this

Public Sub NonNormalize()
  Dim rsIn As DAO.Recordset
  Dim I As Integer
  Dim strSql As String
  Dim strValues As String
  Set rsIn = CurrentDb.OpenRecordset("qryUnionData")
  Do While Not rsIn.EOF
    If I = 6 Then
      strValues = ""
      I = 1
    Else
      I = I + 1
    End If
    
    If strValues = "" Then
      strValues = "'" & rsIn!grpValue & "'"
    Else
      strValues = strValues & ", '" & rsIn!grpValue & "'"
    End If
    
    If I = 6 Then
      strValues = "(" & rsIn!slot & "," & strValues & ")"
      strSql = "Insert into tblNonNormal (Slot, N1, N2, N3, N4, N5, N6) VALUES " & strValues
      Debug.Print strSql
      CurrentDb.Execute strSql
    End If
     rsIn.MoveNext
  Loop
End Sub

There is probably a cleaner solution.

 

[MajP]

The union query needs an additional field to ensure the sort order.

SELECT ID, Slot, Grp1 as GrpValue, 1 as ValOrder
FROM tblData
UNION ALL SELECT
ID, Slot, Grp2 as GrpValue, 2 as ValOrder
FROM tblData
ORDER BY 1, 2, 4;

 

[dhookom]

Do this without any code like:
First create a Union query
quniVickyC

SELECT tblVickyC.ID+0 As GrpOrder, tblVickyC.Slot, tblVickyC.Grp1 as Grp
FROM tblVickyC
UNION ALL
SELECT tblVickyC.ID+0.5, tblVickyC.Slot, tblVickyC.Grp2
FROM tblVickyC
ORDER BY 1;

ranking query for each group of three that numbers them 1-3
qrnkVickyC

SELECT quniVickyC.Slot, quniVickyC.Grp, quniVickyC.GrpOrder, Count(quniVickyC.GrpOrder) AS CountOfGrpOrder
FROM quniVickyC INNER JOIN quniVickyC AS quniVickyC_1 ON quniVickyC.Slot = quniVickyC_1.Slot
WHERE (((quniVickyC_1.GrpOrder)<=[quniVickyC].[GrpOrder]))
GROUP BY quniVickyC.Slot, quniVickyC.Grp, quniVickyC.GrpOrder
ORDER BY quniVickyC.Slot, quniVickyC.GrpOrder;

Then create a crosstab from the ranking query:

TRANSFORM First(qrnkVickyC.Grp) AS FirstOfGrp
SELECT qrnkVickyC.Slot
FROM qrnkVickyC
GROUP BY qrnkVickyC.Slot
PIVOT "N" & [CountOfGrpOrder];

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Yep, that is a cleaner solution.