How to show components in line to parent item? 1

[vbahelp07]

Hope i will explain this correctly.
i have a query that lists the parent item and then the component items that makes up the parent item.

OrderNo	ProductNumber	Quantity	ComponentItemCode	QtyPerAssemblyStd
S101500	GX260	1	GX200-STAND	1
S101500	GX260	1	GX260NS	1
S101500	GX266	1	GX200-STAND	1
S101500	GX266	1	GX266NS	1

But i need the query to show like this:

OrderNo      ProductNumber     Quantity
S101500      GX260             1
S101500      GX200-STAND       1
S101500      GX260NS           1
S101500      GX266             1
S101500      GX200-STAND       1
S101500      GX266NS           1

 

[ck1999]

Where are you outputing the data from the query to?
what is the code?

ck1999

 

[vbahelp07]

humh, it's just a query to view the data in the format needed.

 

[ck1999]

the query has to be displayed in something?

A datasheet, form, report, word document, ect.

ck1999

 

[vbahelp07]

oh ... just a datasheet view of the query.

 

[ck1999]

You may need to make a form and then align left and open in datasheet view

ck1999

 

[vbahelp07]

ok, but not sure what you mean about doing the align left. what or how will that get the final result from the subset?

 

[ck1999]

Sorry I misunderstood.

What is your sql for the query you have?

ck1999

 

[vbahelp07]

SELECT ShowroomOrdersDetails.OrderNo, ShowroomOrdersDetails.ProductNumber, ShowroomOrdersDetails.ItemDescription, ShowroomOrdersDetails.Quantity, ShowroomOrdersDetails.Unit_Price, IMO_SalesKitDetail.ComponentItemCode, IMO_SalesKitDetail.QtyPerAssemblyStd
FROM ShowroomOrdersDetails LEFT JOIN IMO_SalesKitDetail ON ShowroomOrdersDetails.ProductNumber = IMO_SalesKitDetail.SalesKitNumber
ORDER BY ShowroomOrdersDetails.OrderNo DESC , ShowroomOrdersDetails.ProductNumber, IMO_SalesKitDetail.ComponentItemCode;

 

[ck1999]

You may need to run code to add a record to the IMO_SalesKitDetail for each Saleskitnumber with a qty of 1

I am not sure how to do that in the sql code but if you want a vba module to do that let me know

ck1999

 

[dhookom]

Try something like

SELECT OrderNo, ProductNumber, Quantity
FROM queryA
GROUP BY OrderNo, ProductNumber, Quantity
UNION ALL
SELECT OrderNo, ComponentItemCode, QtyPerAssemblyStd
FROM queryA;

Duane MS Access MVP

 

[vbahelp07]

that is not an option.
The IMO has the components that i am pulling to get the query result.

the result of what I am seeking needs to be in the query view.
although i don't have a problem if it first needs to go into a table and the corresponding records getting updated in the columns.

VBA module to do this is better then nothing

 

[vbahelp07]

Duane,
hi there!

well, that gave me this:

OrderNo	ProductNumber	Quantity
S101481	AP122	1
S101481	AP125	1
S101481	AP221	1
S101481	APM201	1
S101481	APM205	1
S101481	APW336WALH	2
S101481	LCR402B	1
S101481	LCR404B	1
S101481	LCW504	1
S101481	LCW512	1
S101481	LCW520	1
S101481	LCW533	1
S101481	MO198	2
S101481	PFR3587	1
S101481	TT122	1
S101481	VAS152	1
S101481	VAS182	1
S101481	VAS189	1
S101481	VAS193	1
S101481		
S101481		
S101481		
S101481		
S101481	LSL520	1
S101481	LBCW500	1
S101481		
S101481		
S101481	LSM402	1
S101481	LBCR400B	1
S101481		
S101481		
S101481		
S101481	LBCW500	1
S101481	LSL504	1
S101481	LSM404	1
S101481	LBCR400B	1
S101481	LSL533	1
S101481	LBCW500	1
S101481		
S101481	LSL512	1
S101481	LBCW500	1
S101481		
S101481		
S101481

from:

OrderNo	ProductNumber	ItemDescription	Quantity	Unit_Price	ComponentItemCode	QtyPerAssemblyStd
S101481	AP122	Red Grapes                    	1	20		
S101481	AP125	Wine & Cheese                 	1	20		
S101481	AP221	Hummingbird Tapestry          	1	16		
S101481	APM201	Grapes                        	1	35		
S101481	APM205	Birds of a Feather            	1	35		
S101481	APW336WALH	Tuscany                       	2	45		
S101481	LCR402B	Tuscany                       	1	14	LBCR400B	1
S101481	LCR402B	Tuscany                       	1	14	LSM402	1
S101481	LCR404B	Wine & Cheese                 	1	14	LBCR400B	1
S101481	LCR404B	Wine & Cheese                 	1	14	LSM404	1
S101481	LCW504	Tuscany                       	1	16.5	LBCW500	1
S101481	LCW504	Tuscany                       	1	16.5	LSL504	1
S101481	LCW512	Wine & Cheese                 	1	16.5	LBCW500	1
S101481	LCW512	Wine & Cheese                 	1	16.5	LSL512	1
S101481	LCW520	Red Grapes                    	1	16.5	LBCW500	1
S101481	LCW520	Red Grapes                    	1	16.5	LSL520	1
S101481	LCW533	Sunflower Field               	1	16.5	LBCW500	1
S101481	LCW533	Sunflower Field               	1	16.5	LSL533	1
S101481	MO198	Hummingbird Tapestry          	2	7.5		
S101481	PFR3587	Sunflower Field               	1	10		
S101481	TT122	Tuscany Rooster               	1	20		
S101481	VAS152	Wine & Cheese                 	1	18		
S101481	VAS182	Red Grapes                    	1	18		
S101481	VAS189	Sunflower Field               	1	18		
S101481	VAS193	Tuscany                       	1	18

 

[ck1999]

try this
create a table called tblstuff
enter 3 fields "Productnumber" "Ordernumber" "Quantity"

then create a module

Sub test2()
 
DoCmd.SetWarnings False
ssql1 = "DELETE tblStuff.* FROM tblStuff"
DoCmd.RunSQL ssql1
 
 
 
 sSQL2 = "INSERT INTO tblStuff ( OrderNumber, ProductNumber, quantity )" _
         & "SELECT ShowroomOrdersDetails.orderno, IMO_SalesKitDetail.ComponentItemCode, IMO_SalesKitDetail.QtyPerAssemblyStd " _
         & " FROM ShowroomOrdersDetails LEFT JOIN IMO_SalesKitDetail ON ShowroomOrdersDetails.productnumber = IMO_SalesKitDetail.SalesKitNumber;"

 DoCmd.RunSQL sSQL2


ssql3 = "INSERT INTO tblStuff ( OrderNumber, ProductNumber, quantity) " _
        & "SELECT Distinct ShowroomOrdersDetails.orderno, ShowroomOrdersDetails.productnumber, IMO_SalesKitDetail.QtyPerAssemblyStd " _
        & "FROM ShowroomOrdersDetails LEFT JOIN IMO_SalesKitDetail ON ShowroomOrdersDetails.productnumber = IMO_SalesKitDetail.SalesKitNumber; "

DoCmd.RunSQL ssql3



DoCmd.SetWarnings True
End Sub

then remake a query using tblstuff
using as sql

SELECT tblStuff.OrderNumber, tblStuff.ProductNumber, tblStuff.quantity
FROM tblStuff
ORDER BY tblStuff.OrderNumber DESC , tblStuff.ProductNumber, tblStuff.quantity;


This is the only way I know how to do it.

ck1999

 

[dhookom]

What don't you like about the results? Your original sample data didn't have any nulls in the ComponentItemCode field. Do you want to eliminate the records where there is no ComponentItemCode? If so, just set the where clause to eliminate these.

Duane MS Access MVP

 

[vbahelp07]

duane,
yes, the nulls. you're right i can just put a Is Not Null under ProductNumber. i will test. Thank you!


ck1999,
thanks, i will try this as well.


Thank you both!

 

[vbahelp07]

Duane,
your solutions is great ... however I need to change a couple of things and not sure how to modify it.

here's the query that encompasses all the fields required:

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
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
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, Unit_Price, BillToName, BillToAdd1, BillToAdd2, BillTocity, BillToState, BillToZip, Country, BillToPhone, BillToPhone2, BillToFax, SalesPersonCode, ShipToBuyer, PriceLevel, Rate, SalesUM, PODate, TermsCode, ShipVia
FROM Kits;

but in the output the following fields i need them to be null.
RegSalesAct, SalesIncomeAcctNumber, CostOfSalesAcctNumber, Unit_Price.
these fields are only specific to the ProductNumber of the parent item and can't show for the componentitemcode.

in addition i need to mark the ProductNumbers that are of the componentitems with these fields:
KitItem, StdKitBill, SkipComp?, QtyPerBill

Item	Desc	COGs	RegSales	KitItem	StdKitBill	SkipComp?	QtyOrdered	UnitPrice	Ext	QtyPerBill
GX206          	Victorian Butterflies//Rejoice	400000000	300000000	Y	N	Y	1	12	12	0
GX206NS        	Victorian Butterflies//Rejoice	         	         	N	 	 	1	0	0	1
GX200-STAND    	GX200 Tealight Stand          	         	         	N	 	 	1	0	0	1
LCR402W        	Tuscany                       	400000000	300000000	Y	N	Y	1	14	14	0
LSM402         	Tuscany                       	         	         	N	 	 	1	0	0	1
LBCR400W       	Lamp Base, Candle, Resin, 6 M"	         	         	N	 	 	1	0	0	1

thank you!

 

[dhookom]

I'm lost. If you want to add columns, you must make sure you add them to both selects in the union. They would get their "name" from the first select. If you want them to be blank in either SELECT, enter a value of Null.

SELECT Null as NewFieldA...
[code]

Duane MS Access MVP

 

[vbahelp07]

Duane,
thank you! i've never worked with UNION queries. this is a great tool.
that worked!

now ... not sure how to apply these to identify the difference from the parent to component now being under the ProductNumber.

Item	KitItem	StdKitBill	SkipComp?	QtyPerBill	QtyOrdered	UnitPrice
GX206          	Y	N	Y	0	1	12
GX206NS        	N	 	 	1	1	0
GX200-STAND    	N	 	 	1	1	0
LCR402W        	Y	N	Y	0	1	14
LSM402         	N	 	 	1	1	0
LBCR400W       	N	 	 	1	1	0

i can add the fields KitItem, StdKitBill, SkipComp?, QtyPerBill into the Kits query but how do i have the UNION'd fields to display the difference?

i hope that makes sense?