Sort issue with UNION query

[vbahelp07]

I will start with the query from where the data is coming from and show how that looks. Then the UNION query that is used to combine the Parent in ProductNumber with the ComponentItemCode as the ProductNumber.

SQL:

SELECT ShowroomOrdersDetails.CustID, ShowroomOrdersDetails.Division, ShowroomOrdersDetails.PONumber, ShowroomOrdersDetails.OrderDate AS SalesOrderDate, ShowroomOrdersDetails.OrderNo, ShowroomOrdersDetails.RegSalesAct, ShowroomOrdersDetails.ShipToCode, ShowroomOrdersDetails.ShipToName, ShowroomOrdersDetails.ShipToAdd1, ShowroomOrdersDetails.ShipToAdd2, ShowroomOrdersDetails.ShipToCity, ShowroomOrdersDetails.ShipToState, ShowroomOrdersDetails.ShipToZip, ShowroomOrdersDetails.ShipToCountry, ShowroomOrdersDetails.[Ship Date] AS shipdate, ShowroomOrdersDetails.CancelDate, ShowroomOrdersDetails.FOB, ShowroomOrdersDetails.Comment, ShowroomOrdersDetails.ProductNumber, ShowroomOrdersDetails.ItemDescription, ShowroomOrdersDetails.SalesIncomeAcctNumber, ShowroomOrdersDetails.CostOfSalesAcctNumber, IIf([Quantity]<[MinQty],[MinQty],[Quantity]) AS Qty, ShowroomOrdersDetails.Unit_Price, ShowroomOrdersDetails.BillToName, ShowroomOrdersDetails.BillToAdd1, ShowroomOrdersDetails.BillToAdd2, ShowroomOrdersDetails.BillToCity, ShowroomOrdersDetails.BillToState, ShowroomOrdersDetails.BillToZip, ShowroomOrdersDetails.Field60 AS Country, ShowroomOrdersDetails.BillToPhone, ShowroomOrdersDetails.BillToPhone2, ShowroomOrdersDetails.BillToFax, ShowroomOrdersDetails.SalesPersonCode, ShowroomOrdersDetails.ShipToBuyer, ShowroomOrdersDetails.PriceLevel, ShowroomOrdersDetails.Rate, ShowroomOrdersDetails.SalesUM, ShowroomOrdersDetails.PODate, ShowroomOrdersDetails.TermsCode, ShowroomOrdersDetails.ShipMethod AS ShipVia, ShowroomOrdersDetails.ProductType, ShowroomOrdersDetails.ExplodeKit, ShowroomOrdersDetails.SalesUMFactor, IMO_SalesKitDetail.ComponentItemCode, IMO_SalesKitDetail.QtyPerAssemblyStd, IIf([ProductType]="K","Y","N") AS KitItem, IIf([ProductType]="K","N",Null) AS StdKitBill, IIf([ProductType]="K","Y",Null) AS KitComp, IIf([ComponentItemCode] Is Not Null,[QtyPerAssemblyStd],0) AS QtyPerBill, IMO_SalesKitDetail.LineSeqNumber
FROM (ShowroomOrdersDetails LEFT JOIN MinQtyToOrder ON ShowroomOrdersDetails.ProductNumber = MinQtyToOrder.ItemNumber) LEFT JOIN IMO_SalesKitDetail ON ShowroomOrdersDetails.ProductNumber = IMO_SalesKitDetail.SalesKitNumber
ORDER BY ShowroomOrdersDetails.OrderNo, ShowroomOrdersDetails.ProductNumber, IMO_SalesKitDetail.LineSeqNumber;

Parent to component link to determine the component of the parent (ProductNumber)

OrderNo	ProductNumber	Qty	Unit_Price	ComponentItemCode	QtyPerAssemblyStd	LineSeqNumber
S101523	CIN101	12	$2.75			
S101523	GX259	2	$12.00	GX259NS	1	1
S101523	GX259	2	$12.00	GX200-STAND	1	2
S101523	HP108	2	$12.50			
S101523	MTLPP3	2	$96.00			
S101523	PFR4628	2	$12.00			
S101523	PFR4659	2	$12.00			
S101523	PFR4661	2	$12.00			
S101523	VAS116	2	$18.00			
S101523	VAS156	2	$18.00			
S101523	VAS175	4	$18.00			
S101523	VAS186	2	$18.00			
S101523	VAS194	2	$18.00

UNION query:

SELECT CustID, Division, PONumber, SalesOrderDate, OrderNo, ProductNumber, Qty, RegSalesAct, ShipToCode, ShipToName, ShipToAdd1, ShipToAdd2, ShipToCity, ShipToState, ShipToZip, ShipToCountry, ShipDate, CancelDate, FOB, Comment, ItemDescription, SalesIncomeAcctNumber, CostOfSalesAcctNumber, Unit_Price, BillToName, BillToAdd1, BillToAdd2, BillTocity, BillToState, BillToZip, Country, BillToPhone, BillToPhone2, BillToFax, SalesPersonCode, ShipToBuyer, PriceLevel, Rate, SalesUM, PODate, TermsCode, ShipVia, ProductType, ExplodeKit, SalesUMFactor, KitItem, StdKitBill, KitComp, QtyPerBill, LineSeqNumber
FROM Kits
GROUP BY CustID, Division, PONumber, SalesOrderDate, OrderNo, ProductNumber, Qty, RegSalesAct, ShipToCode, ShipToName, ShipToAdd1, ShipToAdd2, ShipToCity, ShipToState, ShipToZip, ShipToCountry, ShipDate, CancelDate, FOB, Comment, ItemDescription, SalesIncomeAcctNumber, CostOfSalesAcctNumber, Unit_Price, BillToName, BillToAdd1, BillToAdd2, BillTocity, BillToState, BillToZip, Country, BillToPhone, BillToPhone2, BillToFax, SalesPersonCode, ShipToBuyer, PriceLevel, Rate, SalesUM, PODate, TermsCode, ShipVia, ProductType, ExplodeKit, SalesUMFactor, KitItem, StdKitBill, KitComp, QtyPerBill, LineSeqNumber
UNION ALL SELECT CustID, Division, PONumber, SalesOrderDate, OrderNo, ComponentItemCode, QtyPerAssemblyStd, RegSalesAct, ShipToCode, ShipToName, ShipToAdd1, ShipToAdd2, ShipToCity, ShipToState, ShipToZip, ShipToCountry, ShipDate, CancelDate, FOB, Comment, ItemDescription, SalesIncomeAcctNumber, CostOfSalesAcctNumber, 0 as Unit_Price, BillToName, BillToAdd1, BillToAdd2, BillTocity, BillToState, BillToZip, Country, BillToPhone, BillToPhone2, BillToFax, SalesPersonCode, ShipToBuyer, Null as PriceLevel, Null as Rate, SalesUM, PODate, TermsCode, ShipVia, ProductType, ExplodeKit, SalesUMFactor, KitItem, StdKitBill, KitComp, QtyPerBill, 0 as LineSeqNumber
FROM Kits;

OrderNo	ProductNumber	Qty	ProductType	ExplodeKit	KitItem	StdKitBill	KitComp	QtyPerBill	LineSeqNumber
S101523	CIN101	12	F	P	N			0	
S101523	GX259	2	K	A	Y	N	Y	1	1
S101523	GX259	2	K	A	Y	N	Y	1	2
S101523	HP108	2	F	P	N			0	
S101523	MTLPP3	2	F	P	N			0	
S101523	PFR4628	2	F	P	N			0	
S101523	PFR4659	2	F	P	N			0	
S101523	PFR4661	2	F	P	N			0	
S101523	VAS116	2	F	P	N			0	
S101523	VAS156	2	F	P	N			0	
S101523	VAS175	4	F	P	N			0	
S101523	VAS186	2	F	P	N			0	
S101523	VAS194	2	F	P	N			0	
S101523	GX259NS	1	K	A	Y	N	Y	1	0
S101523	GX200-STAND	1	K	A	Y	N	Y	1	0

How or what do i change or do so that the ProductNumber of the parent does not repeat and only show it once and show the component items WITH the parent item as shown in the 1st query?

So that GX259 has GX259NS and GX200-STAND in the order based on the LineSeqNumber?

GX259
GX259NS - it takes this cross design item
GX200-STAND - it takes this cross stand

The cross stand is used for all products with GX2## as the item. So if the order has GX261 it takes a GX261NS and a GX200-STAND to make the Gx261. The GX200-STAND for GX261 needs to stay WITH the originating parent item. hope this makes sense.