Merging data with a query

[cruiser01]

Didn't get a solution the first time so thought I would try again. Have a table that looks like this:

MasterOrder Order2 Order3 Order4 Costperlb
111111 222222 333333 444444 .088
123456 456789 .109
777777 888888 .045

All the order fields on a record (a shipment) refer to a common field called "Costperlb". These records will not always have multiple order numbers.

I basically need to have a query that will bring back results that identifies each individual order number and the referring "Costperlb". So it would basically look like this, using an alias of "Orderno" for all the different "order" fields.

Orderno Costperlb
111111 .088
123456 .109
222222 .088
333333 .088
444444 .088
456789 .109
777777 .045
etc.

Have tried several angles at getting this, but no luck. Any thoughts.

 

[JZajda]

Have you tried a union query?

Select MasterOrder as Orderno, Costperlb
from Table
where MasterOrder <> ""

Union

Select Order2 as Orderno, Costperlb
from Table
where Order2 <> ""

Union

Select Order3 as Orderno, Costperlb
from Table
where Order3 <> ""

Union

Select Order4 as Orderno, Costperlb
from Table
where Order4 <> ""

 

[cruiser01]

I think you're on the right track. Tried some union queries, but kept getting duplicates. I did try what you suggested and it makes sense to me, but getting a "data type mismatch", so I'm working through it.

 

[cruiser01]

Thanks a bunch. Was simply just simply the fact that "order" numbers were a numberic field, so just changed the criteria to >0.