UNION SELECT

B

Brad

Thanks for taking the time to read my question.

I'm updating a DB and currently all the prices are split into different
columns based on package size. I'd like to get all prices into one column
now, as each package size now has it's own product code (Before there was
only one code regardless of package size).

What I've tried is a UNION SELECT query putting all the prices under one
column. What I'm getting is double the records. One record has a price, the
duplicate doesn't. I've put my SQL below.

How do I get rid of the duplicate record?

Thanks,

Brad

SQL:
SELECT DISTINCTROW qry_MillPricing.Facility, qry_MillPricing.Group,
qry_MillPricing.number, qry_MillPricing.productcode, qry_MillPricing.CustNum,
qry_MillPricing.Description, qry_MillPricing.species,
qry_MillPricing.subspecies, qry_MillPricing.texture, qry_MillPricing.Mill,
qry_MillPricing.TotePrice AS Price, qry_MillPricing.SellingUofM_XX,
qry_MillPricing.IUMRC_XX, qry_MillPricing.BCHLD_XX
FROM qry_MillPricing

UNION SELECT DISTINCTROW qry_MillPricing.Facility, qry_MillPricing.Group,
qry_MillPricing.number, qry_MillPricing.productcode, qry_MillPricing.CustNum,
qry_MillPricing.Description, qry_MillPricing.species,
qry_MillPricing.subspecies, qry_MillPricing.texture, qry_MillPricing.Mill,
qry_MillPricing.[25KGBagPrice], qry_MillPricing.SellingUofM_XX,
qry_MillPricing.IUMRC_XX, qry_MillPricing.BCHLD_XX
FROM qry_MillPricing

UNION SELECT DISTINCTROW qry_MillPricing.Facility, qry_MillPricing.Group,
qry_MillPricing.number, qry_MillPricing.productcode, qry_MillPricing.CustNum,
qry_MillPricing.Description, qry_MillPricing.species,
qry_MillPricing.subspecies, qry_MillPricing.texture, qry_MillPricing.Mill,
qry_MillPricing.BulkPrice, qry_MillPricing.SellingUofM_XX,
qry_MillPricing.IUMRC_XX, qry_MillPricing.BCHLD_XX
FROM qry_MillPricing

UNION SELECT DISTINCTROW qry_MillPricing.Facility, qry_MillPricing.Group,
qry_MillPricing.number, qry_MillPricing.productcode, qry_MillPricing.CustNum,
qry_MillPricing.Description, qry_MillPricing.species,
qry_MillPricing.subspecies, qry_MillPricing.texture, qry_MillPricing.Mill,
qry_MillPricing.EachPrice, qry_MillPricing.SellingUofM_XX,
qry_MillPricing.IUMRC_XX, qry_MillPricing.BCHLD_XX
FROM qry_MillPricing

UNION SELECT DISTINCTROW qry_MillPricing.Facility, qry_MillPricing.Group,
qry_MillPricing.number, qry_MillPricing.productcode, qry_MillPricing.CustNum,
qry_MillPricing.Description, qry_MillPricing.species,
qry_MillPricing.subspecies, qry_MillPricing.texture, qry_MillPricing.Mill,
qry_MillPricing.[10KGBagPrice], qry_MillPricing.SellingUofM_XX,
qry_MillPricing.IUMRC_XX, qry_MillPricing.BCHLD_XX
FROM qry_MillPricing

UNION SELECT DISTINCTROW qry_MillPricing.Facility, qry_MillPricing.Group,
qry_MillPricing.number, qry_MillPricing.productcode, qry_MillPricing.CustNum,
qry_MillPricing.Description, qry_MillPricing.species,
qry_MillPricing.subspecies, qry_MillPricing.texture, qry_MillPricing.Mill,
qry_MillPricing.[20KGBagPrice], qry_MillPricing.SellingUofM_XX,
qry_MillPricing.IUMRC_XX, qry_MillPricing.BCHLD_XX
FROM qry_MillPricing

UNION SELECT DISTINCTROW qry_MillPricing.Facility, qry_MillPricing.Group,
qry_MillPricing.number, qry_MillPricing.productcode, qry_MillPricing.CustNum,
qry_MillPricing.Description, qry_MillPricing.species,
qry_MillPricing.subspecies, qry_MillPricing.texture, qry_MillPricing.Mill,
qry_MillPricing.[30KGBagPrice], qry_MillPricing.SellingUofM_XX,
qry_MillPricing.IUMRC_XX, qry_MillPricing.BCHLD_XX
FROM qry_MillPricing

UNION SELECT DISTINCTROW qry_MillPricing.Facility, qry_MillPricing.Group,
qry_MillPricing.number, qry_MillPricing.productcode, qry_MillPricing.CustNum,
qry_MillPricing.Description, qry_MillPricing.species,
qry_MillPricing.subspecies, qry_MillPricing.texture, qry_MillPricing.Mill,
qry_MillPricing.[227KGBagPrice], qry_MillPricing.SellingUofM_XX,
qry_MillPricing.IUMRC_XX, qry_MillPricing.BCHLD_XX
FROM qry_MillPricing

UNION SELECT DISTINCTROW qry_MillPricing.Facility, qry_MillPricing.Group,
qry_MillPricing.number, qry_MillPricing.productcode, qry_MillPricing.CustNum,
qry_MillPricing.Description, qry_MillPricing.species,
qry_MillPricing.subspecies, qry_MillPricing.texture, qry_MillPricing.Mill,
qry_MillPricing.PerKGPrice, qry_MillPricing.SellingUofM_XX,
qry_MillPricing.IUMRC_XX, qry_MillPricing.BCHLD_XX
FROM qry_MillPricing;
 
B

Bob Barrows

Brad said:
Thanks for taking the time to read my question.

I'm updating a DB and currently all the prices are split into
different columns based on package size. I'd like to get all prices
into one column now, as each package size now has it's own product
code (Before there was only one code regardless of package size).

What I've tried is a UNION SELECT query putting all the prices under
one column. What I'm getting is double the records. One record has a
price, the duplicate doesn't. I've put my SQL below.

How do I get rid of the duplicate record?
Click to expand...

First of all, they cannot be duplicate ... using UNION without the ALL
keyword caused duplicates to be eliminated from the results (you ddon't
need that DISTINCTROW keyword either).

So, there has to be a difference in at least one of the fields
preventing the rows from being considered duplicates. Show us an example
of these "duplicates"
 
B

Brad

Ok, they're not 100% the same.

One has a price and the next one doesn't. Each UNION SELECT is based on the
same sub query, so I'm not sure where the record with the Price = NULL is
coming from.

Results from Sub Query
Facility Group number productcode CustNum Description species subspecies texture Mill TotePrice 25KGBagPrice BulkPrice EachPrice 10KGBagPrice 20KGBagPrice 30KGBagPrice 227KGBagPrice PerKGPrice SellingUofM_XX IUMRC_XX BCHLD_XX
ME 9209 3000112 009209 18 CALF ST MONBKTXT DAIRY REPLACEMENT MASH /
TEXTURED 314.3 MT 1000 3000170


Results from UNION SELECT
Facility Group number productcode CustNum Description species subspecies texture Mill Price SellingUofM_XX IUMRC_XX BCHLD_XX
ME 9209 3000112 009209 18 CALF ST MONBKTXT DAIRY REPLACEMENT MASH /
TEXTURED MT 1000 3000170
ME 9209 3000112 009209 18 CALF ST MONBKTXT DAIRY REPLACEMENT MASH /
TEXTURED 314.3 MT 1000 3000170


Sorry the data is all mashed together here.

Brad
 
B

Bob Barrows

Brad said:
Ok, they're not 100% the same.

One has a price and the next one doesn't. Each UNION SELECT is based
on the
same sub query, so I'm not sure where the record with the Price =
NULL is
coming from.
Click to expand...
Without access to your source data, I do not see how I can help.
You will need to find out by running each part of the union separately
(filter for the particular data)
 
B

Brad

I've figured it out.

The problem was that for each package size there is no price for all but
one. So the records with no price would form one of the duplicates, and the
single record with a price would form the other duplicate.

So I added Not Is Null to each UNION SELECT to get rid of the "Empty Price"
record and return only the record with a price.

Thanks for steering me in the right direction Bob,

Brad


SELECT DISTINCTROW qry_MillPricing.Facility, qry_MillPricing.Group,
qry_MillPricing.number, qry_MillPricing.productcode, qry_MillPricing.CustNum,
qry_MillPricing.Description, qry_MillPricing.species,
qry_MillPricing.subspecies, qry_MillPricing.texture, qry_MillPricing.Mill,
qry_MillPricing.TotePrice AS Price, qry_MillPricing.SellingUofM_XX,
qry_MillPricing.IUMRC_XX, qry_MillPricing.BCHLD_XX
FROM qry_MillPricing
WHERE ((Not (qry_MillPricing.TotePrice) Is Null))

UNION SELECT DISTINCTROW qry_MillPricing.Facility, qry_MillPricing.Group,
qry_MillPricing.number, qry_MillPricing.productcode, qry_MillPricing.CustNum,
qry_MillPricing.Description, qry_MillPricing.species,
qry_MillPricing.subspecies, qry_MillPricing.texture, qry_MillPricing.Mill,
qry_MillPricing.[30KGBagPrice], qry_MillPricing.SellingUofM_XX,
qry_MillPricing.IUMRC_XX, qry_MillPricing.BCHLD_XX
FROM qry_MillPricing
WHERE ((Not (qry_MillPricing.[30KGBagPrice]) Is Null))

UNION SELECT DISTINCTROW qry_MillPricing.Facility, qry_MillPricing.Group,
qry_MillPricing.number, qry_MillPricing.productcode, qry_MillPricing.CustNum,
qry_MillPricing.Description, qry_MillPricing.species,
qry_MillPricing.subspecies, qry_MillPricing.texture, qry_MillPricing.Mill,
qry_MillPricing.[227KGBagPrice], qry_MillPricing.SellingUofM_XX,
qry_MillPricing.IUMRC_XX, qry_MillPricing.BCHLD_XX
FROM qry_MillPricing
WHERE ((Not (qry_MillPricing.[227KGBagPrice]) Is Null))

UNION SELECT DISTINCTROW qry_MillPricing.Facility, qry_MillPricing.Group,
qry_MillPricing.number, qry_MillPricing.productcode, qry_MillPricing.CustNum,
qry_MillPricing.Description, qry_MillPricing.species,
qry_MillPricing.subspecies, qry_MillPricing.texture, qry_MillPricing.Mill,
qry_MillPricing.[20KGBagPrice], qry_MillPricing.SellingUofM_XX,
qry_MillPricing.IUMRC_XX, qry_MillPricing.BCHLD_XX
FROM qry_MillPricing
WHERE ((Not (qry_MillPricing.[20KGBagPrice]) Is Null))

UNION SELECT DISTINCTROW qry_MillPricing.Facility, qry_MillPricing.Group,
qry_MillPricing.number, qry_MillPricing.productcode, qry_MillPricing.CustNum,
qry_MillPricing.Description, qry_MillPricing.species,
qry_MillPricing.subspecies, qry_MillPricing.texture, qry_MillPricing.Mill,
qry_MillPricing.[10KGBagPrice], qry_MillPricing.SellingUofM_XX,
qry_MillPricing.IUMRC_XX, qry_MillPricing.BCHLD_XX
FROM qry_MillPricing
WHERE ((Not (qry_MillPricing.[10KGBagPrice]) Is Null))

UNION SELECT DISTINCTROW qry_MillPricing.Facility, qry_MillPricing.Group,
qry_MillPricing.number, qry_MillPricing.productcode, qry_MillPricing.CustNum,
qry_MillPricing.Description, qry_MillPricing.species,
qry_MillPricing.subspecies, qry_MillPricing.texture, qry_MillPricing.Mill,
qry_MillPricing.EachPrice, qry_MillPricing.SellingUofM_XX,
qry_MillPricing.IUMRC_XX, qry_MillPricing.BCHLD_XX
FROM qry_MillPricing
WHERE ((Not (qry_MillPricing.EachPrice) Is Null))

UNION SELECT DISTINCTROW qry_MillPricing.Facility, qry_MillPricing.Group,
qry_MillPricing.number, qry_MillPricing.productcode, qry_MillPricing.CustNum,
qry_MillPricing.Description, qry_MillPricing.species,
qry_MillPricing.subspecies, qry_MillPricing.texture, qry_MillPricing.Mill,
qry_MillPricing.[25KGBagPrice], qry_MillPricing.SellingUofM_XX,
qry_MillPricing.IUMRC_XX, qry_MillPricing.BCHLD_XX
FROM qry_MillPricing
WHERE ((Not (qry_MillPricing.[25KGBagPrice]) Is Null))

UNION SELECT DISTINCTROW qry_MillPricing.Facility, qry_MillPricing.Group,
qry_MillPricing.number, qry_MillPricing.productcode, qry_MillPricing.CustNum,
qry_MillPricing.Description, qry_MillPricing.species,
qry_MillPricing.subspecies, qry_MillPricing.texture, qry_MillPricing.Mill,
qry_MillPricing.PerKGPrice, qry_MillPricing.SellingUofM_XX,
qry_MillPricing.IUMRC_XX, qry_MillPricing.BCHLD_XX
FROM qry_MillPricing
WHERE ((Not (qry_MillPricing.PerKGPrice) Is Null))

UNION SELECT DISTINCTROW qry_MillPricing.Facility, qry_MillPricing.Group,
qry_MillPricing.number, qry_MillPricing.productcode, qry_MillPricing.CustNum,
qry_MillPricing.Description, qry_MillPricing.species,
qry_MillPricing.subspecies, qry_MillPricing.texture, qry_MillPricing.Mill,
qry_MillPricing.BulkPrice, qry_MillPricing.SellingUofM_XX,
qry_MillPricing.IUMRC_XX, qry_MillPricing.BCHLD_XX
FROM qry_MillPricing
WHERE ((Not (qry_MillPricing.BulkPrice) Is Null))
ORDER BY qry_MillPricing.Group;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

ADO left join count matches 0
Using CheckSums to insure data integrity 5
Union Query 3
union query 4
Union query not displaying select statement 1
Comlex union query question 5
Not In (SELECT DISTINCTROW 14
dsum sytax error 5

Top