If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Duplicates in Union Query
I have a quick question. . . If I have duplicates in Table A and I run a
union query between tables A and B, will it eliminate the duplicates in Table A, or do I have to use a "SELECT DISTINCT" when selecting table A? |
#2
|
|||
|
|||
Duplicates in Union Query
If you use UNION in the UNION query, then DISTINCT is applied against
all the records returned by the various queries in the UNION. If you use UNION ALL then no DISTINCT is applied. If you want to eliminate Duplicates in table A and not in table B then you would use UNION ALL and apply DISTINCT against the records in table A 'Distinct from A and all from B SELECT DISTINCT Field2, Field3 FROM A UNION ALL SELECT FieldA, FieldB FROM B 'Distinct over all records returned SELECT Field2, Field3 FROM A UNION SELECT FieldA, FieldB FROM B '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === anon wrote: I have a quick question. . . If I have duplicates in Table A and I run a union query between tables A and B, will it eliminate the duplicates in Table A, or do I have to use a "SELECT DISTINCT" when selecting table A? |
#3
|
|||
|
|||
Duplicates in Union Query
anon wrote:
I have a quick question. . . If I have duplicates in Table A and I run a union query between tables A and B, will it eliminate the duplicates in Table A, or do I have to use a "SELECT DISTINCT" when selecting table A? A UNION query effectively does a DISTINCT across all the records in its result set. If you want any duplicate records in the result set, then use UNION ALL -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|