union query with filter of duplicates

[toby]

hi, gunny
thank you for your help. but my items are not exactly repeated from the
sources.
these items may repeated only in column "title", "isbn", or "author". but i
want to filter out all the items for hving the same title or same isbn, so
is there any method?!?!

thanks in advanced.

Hi, Toby.

A UNION query will automatically filter out duplicate records from the
different source tables. You would need to use UNION ALL to retrieve all
records from the tables, including the duplicates.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Any human can read my reply E-mail address and should alter it so that a
message will be forwarded to me. Spammers are free to use my UNALTERED
reply E-mail address. I will *never* get those messages!)

hi all,

i've an access using union query to draw / select rows of data from 4
different excel sources using the direct file link method.
i've used the following command,SELECT catno, ddc, catcc, catg, isbn, title, author, pubdate, pricecc,
publisher, "yw" as [relationship]
FROM yw
WHERE ddc not In ("470","474","370","462","440","500","502","275","320")
UNION select catno, ddc, catcc, catg, isbn, title, author, pubdate, pricecc,
publisher, "ss"
from ss
where catcc not in ("S","G","V","LM")
UNION select catno, ddc, catcc, catg, isbn, title, author, pubdate, pricecc,
publisher, "cn"
from cn
which "catno" "ddc" "title" "author" are the column names.
there may be same or repeated items among these sources.
so is there any method that i can use to filter out these repeated items?!!?

thanks in advance.

toby

 

[John Spencer (MVP)]

Does it make any difference which row of data you keep of the non-repeating
data? You have to discard some of the data to get just some rows back.

You could use the UNION query as the source of a group by (or totals) query.

GROUP on the fields that you want to be unique and use first on the other fields.

SELECT TITLE, ISBN, AUTHOR,
First(catno) as Cat_No, First(ddc) as dc_c,
First(catcc) as Cat_CC, First(catg) as Cat_G,
First(pubdate) as Pub_Date, First(pricecc) as Price_CC,
First(publisher) as Publish, First([relationship) as Relation_ship
FROM YourUnionQuery
GROUP BY TITLE, ISBN, AUTHOR

By the way, it is a good idea to keep the responses in the same thread. I was
able to puzzle out the answer since all the information was included in your
"reply" message.

Your other possibility is to drop the field from the UNION query that are not duplicates.

If you are trying to find duplicate records based on TITLE, ISBN, and AUTHOR and
want to return all the records where two or more records exist with the same
combination of values, then that is another query.

hi, gunny
thank you for your help. but my items are not exactly repeated from the
sources.
these items may repeated only in column "title", "isbn", or "author". but i
want to filter out all the items for hving the same title or same isbn, so
is there any method?!?!

thanks in advanced.

Hi, Toby.

A UNION query will automatically filter out duplicate records from the
different source tables. You would need to use UNION ALL to retrieve all
records from the tables, including the duplicates.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Any human can read my reply E-mail address and should alter it so that a
message will be forwarded to me. Spammers are free to use my UNALTERED
reply E-mail address. I will *never* get those messages!)

hi all,

i've an access using union query to draw / select rows of data from 4
different excel sources using the direct file link method.
i've used the following command,

SELECT catno, ddc, catcc, catg, isbn, title, author, pubdate, pricecc,
publisher, "yw" as [relationship]
FROM yw
WHERE ddc not In ("470","474","370","462","440","500","502","275","320")
UNION select catno, ddc, catcc, catg, isbn, title, author, pubdate, pricecc,
publisher, "ss"
from ss
where catcc not in ("S","G","V","LM")
UNION select catno, ddc, catcc, catg, isbn, title, author, pubdate, pricecc,
publisher, "cn"
from cn


which "catno" "ddc" "title" "author" are the column names.
there may be same or repeated items among these sources.
so is there any method that i can use to filter out these repeated items?!!?

thanks in advance.

toby

 

[toby]

it works. thank you
john.

Does it make any difference which row of data you keep of the non-repeating
data? You have to discard some of the data to get just some rows back.

You could use the UNION query as the source of a group by (or totals) query.

GROUP on the fields that you want to be unique and use first on the other fields.

SELECT TITLE, ISBN, AUTHOR,
First(catno) as Cat_No, First(ddc) as dc_c,
First(catcc) as Cat_CC, First(catg) as Cat_G,
First(pubdate) as Pub_Date, First(pricecc) as Price_CC,
First(publisher) as Publish, First([relationship) as Relation_ship
FROM YourUnionQuery
GROUP BY TITLE, ISBN, AUTHOR

By the way, it is a good idea to keep the responses in the same thread. I was
able to puzzle out the answer since all the information was included in your
"reply" message.

Your other possibility is to drop the field from the UNION query that are not duplicates.

If you are trying to find duplicate records based on TITLE, ISBN, and AUTHOR and
want to return all the records where two or more records exist with the same
combination of values, then that is another query.

hi, gunny
thank you for your help. but my items are not exactly repeated from the
sources.
these items may repeated only in column "title", "isbn", or "author". but i
want to filter out all the items for hving the same title or same isbn, so
is there any method?!?!

thanks in advanced.

"'69 Camaro" <[email protected]_SPAM> |b¶l¥ó

Hi, Toby.

A UNION query will automatically filter out duplicate records from the
different source tables. You would need to use UNION ALL to retrieve all
records from the tables, including the duplicates.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Any human can read my reply E-mail address and should alter it so that a
message will be forwarded to me. Spammers are free to use my UNALTERED
reply E-mail address. I will *never* get those messages!)


hi all,

i've an access using union query to draw / select rows of data from 4
different excel sources using the direct file link method.
i've used the following command,

SELECT catno, ddc, catcc, catg, isbn, title, author, pubdate, pricecc,
publisher, "yw" as [relationship]
FROM yw
WHERE ddc not In ("470","474","370","462","440","500","502","275","320")
UNION select catno, ddc, catcc, catg, isbn, title, author, pubdate,
pricecc,
publisher, "ss"
from ss
where catcc not in ("S","G","V","LM")
UNION select catno, ddc, catcc, catg, isbn, title, author, pubdate,
pricecc,
publisher, "cn"
from cn


which "catno" "ddc" "title" "author" are the column names.
there may be same or repeated items among these sources.
so is there any method that i can use to filter out these repeated
items?!!?

thanks in advance.

toby