Union query question ...

[vbahelp07]

i'm new to Union queries and having a problem trying to figure out how to correct my query.

I have a query named "Kits" this links the tables with the parent and the component items that make up the Kit.

The Kits query result is basically this:

OrderNo	ProductNumber	Qty	Unit_Price	ComponentItemCode	QtyPerAssemblyStd	LineSeqNumber
S101519	DA/DRCK102/108	1	 $692.50	   LC041	        1	1
S101519	DA/DRCK102/108	1	 $692.50	   LC042	        1	2
S101519	DA/DRCK102/108	1	 $692.50	   LC050	        1	3
S101519	DA/DRCK102/108	1	 $692.50	   LC071	        1	4
S101519	DA/DRCK102/108	1	 $692.50	   LC074	        1	5
S101519	DA/DRCK102/108	1	 $692.50	   LC092	        1	6
S101519	DA/DRCK102/108	1	 $692.50	   LC202	        1	7
S101519	DA/DRCK102/108	1	 $692.50	   LC133	        1	8
S101519	DA/DRCK102/108	1	 $692.50	   LC128	        1	9
S101519	DA/DRCK102/108	1	 $692.50	   LC123	       1	10
S101519	GX202	         2     $12.00	        GX202NS	       1	1
S101519	GX202	         2     $12.00	        GX200-STAND	1	2
S101519	GX206	         2     $12.00	        GX206NS	       1	1
S101519	GX206	         2     $12.00	        GX200-STAND	1	2
S101519	GX214	         2     $12.00	        GX214NS	        1	1
S101519	GX214	         2     $12.00	        GX200-STAND	1	2

where the last 3 columns are from the components (Kit table) of the parent item (ProductNumber).
The LineSeqNumber is the order of the component for the parent.

When I do the UNION query it's to combine the component items into the ProductNumber as I can only use the ProductNumber field to list ALL the items for the order.

If I include the LineSeqNumber it does not work and shows me duplicate values. When I leave it out it gives me the data with the Component items in the ProductNumber with the parent item.

I need the LineSeqNumber so that the sortorder of the Parent and component is sorted as it should be and together on the results view.

I realize that it's somehow b/c the LineSeqNumber does not exist on the parent side and only in the component side of the Kit.

please help.

thank you in advance.

 

[KenReay]

Hi

You do not show the SQL of your query, so I can only reply in general terms, but you realise that all of the SELECTs in a union query must select the same number of coulmns, and those columns must be of the same type. So if your Kit table does not have a sequence number, and your component table does, then perhaps you need something like:

SELECT field1, Field2, SequenceNo ... FROM tblComponents
UNION
SELECT field1, Field2, 0 as SequenceNo ... FROM tblKit
ORDER BY SequenceNo



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[vbahelp07]

hi there,
you know I thought about doing the 0 as LineSeqNumber.but it still does not give me the info in the order required / needed.

not sure how to have the query sort in the order of the items so they are kept together.

so in the example:

S101519    GX202             2     $12.00            GX202NS           1    1
S101519    GX202             2     $12.00            GX200-STAND    1    2
S101519    GX206             2     $12.00            GX206NS           1    1
S101519    GX206             2     $12.00            GX200-STAND    1    2

the final output should be:

S101519    GX202        1      0     $12
S101519    GX202NS      1      1     $0
S101519    GX200-STAND  1      2     $0
S101519    GX206        1      0     $12
S101519    GX206NS      1      1     $0
S101519    GX200-STAND  1      2     $0

 

[KenReay]

Hi

In the absence of the SQL, still stumbling in the dark, but why not:

ORDER BY OrderNo, SequenceNo

(guessing at column names from your original post)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[vbahelp07]

Hi Ken,
that doesn't work.
at least not the way i need it.

please see:

http://tek-tips.com/viewthread.cfm?qid=1453027&page=1

thank you!

 

[KenReay]

Hi

OK I have looked at the link, you appear to be dealing with a Bill of Materials type application, may I direct you to:

http://www.mvps.org/access/modules/mdl0027.htm

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[vbahelp07]

thanks.
however i've all ready determined the link between the parent to the component.

what I need to do from that is keep them together with the other items on the order otherwise it does not make sense.

the only place that has a seqnumber is in the Kits (AKA: BOM) detail table of the components.