union query with filter of repeated items

  • Thread starter Thread starter toby
  • Start date Start date
T

toby

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
 
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!)


toby said:
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
Click to expand...
 
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 said:
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!)


toby said:
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
Click to expand...
Click to expand...
 
You're welcome. I see that John Spencer has already answered this question
in the other thread you started after this one, so I'll let this thread die.
Good luck on your queries.

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!)


toby said:
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 said:
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!)


toby said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top