Union Query

G

Guest

I have a union Query linked to 4 select queries. The field names are the same
in each of the four queries. I'm trying to combine the four queries into 1
table showing the customer with their corresponding territory, region and
broker. all the tables are related by a customer field.

It looks like the union query is giving me the list I want but the customer
count double and I don't know why. Can anyone tell me why the number of
records has doubled. I'm using the following code

SELECT agent1qry.*
FROM agent1qry
Union
Select agent2qry.*
FROM agent2qry
UNION
SELECT agent3qry.*
FROM agent3qry
UNION SELECT agent4qry.*
FROM agent4;
 
G

Guest

As you are using a UNION (as opposed to a UNION ALL) all the duplicates
should be removed. Since you are using the * wildcard, all fields are brought
in. If any field is slightly different, that is a different record. Could be
something as simple as a trailing or leading space in a field.

Instead of using the *, pull down the actual field names from each query
until you find the field that is causing the duplicates.
 
G

Guest

Hi Jerry, I went back and created another union query following your
suggestion.
However I'm still having a problem.
The total number of customers is 63,000 (found in table 1)
tables 2, 3, 4 has the territory region and broker for the 63,000 customers

when I try to create 1 table that shows all 63,000 customers with their
territory, region and broker it not showing all 63,000 customers

when I use union all I get back 72,000
when i use only union I get back 15,000

This is the code I'm using thanks for your time and help


SELECT table1.[customer], [Agency
Name],[Territory],[Region],[Broker],[Market],[Office],[Distribution]
FROM table1
Union
select Agent as customer,[Agency
Name],[Territory],[Region],[Broker],[Market],[office],[Distribution]
from table2
UNION
SELECT Producer as customer,[Agency
Name],[Territory],[Region],[Broker],[Market],[office],[Distribution]
FROM TABLE3;
 
G

Guest

How many records are returned from the following SQL statement? I'm thinking
that it's closer to 15,000 than 63,000. In other words you have fewer actual
customers in table1 than you think.

SELECT DISTINCT [customer],
[Agency Name],
[Territory],
[Region],
[Broker],
[Market],
[Office],
[Distribution]
FROM table1 ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


ram said:
Hi Jerry, I went back and created another union query following your
suggestion.
However I'm still having a problem.
The total number of customers is 63,000 (found in table 1)
tables 2, 3, 4 has the territory region and broker for the 63,000 customers

when I try to create 1 table that shows all 63,000 customers with their
territory, region and broker it not showing all 63,000 customers

when I use union all I get back 72,000
when i use only union I get back 15,000

This is the code I'm using thanks for your time and help


SELECT table1.[customer], [Agency
Name],[Territory],[Region],[Broker],[Market],[Office],[Distribution]
FROM table1
Union
select Agent as customer,[Agency
Name],[Territory],[Region],[Broker],[Market],[office],[Distribution]
from table2
UNION
SELECT Producer as customer,[Agency
Name],[Territory],[Region],[Broker],[Market],[office],[Distribution]
FROM TABLE3;



Jerry Whittle said:
As you are using a UNION (as opposed to a UNION ALL) all the duplicates
should be removed. Since you are using the * wildcard, all fields are brought
in. If any field is slightly different, that is a different record. Could be
something as simple as a trailing or leading space in a field.

Instead of using the *, pull down the actual field names from each query
until you find the field that is causing the duplicates.
Click to expand...
Click to expand...
 
G

Guest

it returns 6,935


Jerry Whittle said:
How many records are returned from the following SQL statement? I'm thinking
that it's closer to 15,000 than 63,000. In other words you have fewer actual
customers in table1 than you think.

SELECT DISTINCT [customer],
[Agency Name],
[Territory],
[Region],
[Broker],
[Market],
[Office],
[Distribution]
FROM table1 ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


ram said:
Hi Jerry, I went back and created another union query following your
suggestion.
However I'm still having a problem.
The total number of customers is 63,000 (found in table 1)
tables 2, 3, 4 has the territory region and broker for the 63,000 customers

when I try to create 1 table that shows all 63,000 customers with their
territory, region and broker it not showing all 63,000 customers

when I use union all I get back 72,000
when i use only union I get back 15,000

This is the code I'm using thanks for your time and help


SELECT table1.[customer], [Agency
Name],[Territory],[Region],[Broker],[Market],[Office],[Distribution]
FROM table1
Union
select Agent as customer,[Agency
Name],[Territory],[Region],[Broker],[Market],[office],[Distribution]
from table2
UNION
SELECT Producer as customer,[Agency
Name],[Territory],[Region],[Broker],[Market],[office],[Distribution]
FROM TABLE3;



Jerry Whittle said:
As you are using a UNION (as opposed to a UNION ALL) all the duplicates
should be removed. Since you are using the * wildcard, all fields are brought
in. If any field is slightly different, that is a different record. Could be
something as simple as a trailing or leading space in a field.

Instead of using the *, pull down the actual field names from each query
until you find the field that is causing the duplicates.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I have a union Query linked to 4 select queries. The field names are the same
in each of the four queries. I'm trying to combine the four queries into 1
table showing the customer with their corresponding territory, region and
broker. all the tables are related by a customer field.

It looks like the union query is giving me the list I want but the customer
count double and I don't know why. Can anyone tell me why the number of
records has doubled. I'm using the following code

SELECT agent1qry.*
FROM agent1qry
Union
Select agent2qry.*
FROM agent2qry
UNION
SELECT agent3qry.*
FROM agent3qry
UNION SELECT agent4qry.*
FROM agent4;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Jerry Do you know how i can write the query so that the territory, region and
broker. shows for each of the 63,000 times listed in table1.

Thanks for any help


Jerry Whittle said:
How many records are returned from the following SQL statement? I'm thinking
that it's closer to 15,000 than 63,000. In other words you have fewer actual
customers in table1 than you think.

SELECT DISTINCT [customer],
[Agency Name],
[Territory],
[Region],
[Broker],
[Market],
[Office],
[Distribution]
FROM table1 ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


ram said:
Hi Jerry, I went back and created another union query following your
suggestion.
However I'm still having a problem.
The total number of customers is 63,000 (found in table 1)
tables 2, 3, 4 has the territory region and broker for the 63,000 customers

when I try to create 1 table that shows all 63,000 customers with their
territory, region and broker it not showing all 63,000 customers

when I use union all I get back 72,000
when i use only union I get back 15,000

This is the code I'm using thanks for your time and help


SELECT table1.[customer], [Agency
Name],[Territory],[Region],[Broker],[Market],[Office],[Distribution]
FROM table1
Union
select Agent as customer,[Agency
Name],[Territory],[Region],[Broker],[Market],[office],[Distribution]
from table2
UNION
SELECT Producer as customer,[Agency
Name],[Territory],[Region],[Broker],[Market],[office],[Distribution]
FROM TABLE3;



Jerry Whittle said:
As you are using a UNION (as opposed to a UNION ALL) all the duplicates
should be removed. Since you are using the * wildcard, all fields are brought
in. If any field is slightly different, that is a different record. Could be
something as simple as a trailing or leading space in a field.

Instead of using the *, pull down the actual field names from each query
until you find the field that is causing the duplicates.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I have a union Query linked to 4 select queries. The field names are the same
in each of the four queries. I'm trying to combine the four queries into 1
table showing the customer with their corresponding territory, region and
broker. all the tables are related by a customer field.

It looks like the union query is giving me the list I want but the customer
count double and I don't know why. Can anyone tell me why the number of
records has doubled. I'm using the following code

SELECT agent1qry.*
FROM agent1qry
Union
Select agent2qry.*
FROM agent2qry
UNION
SELECT agent3qry.*
FROM agent3qry
UNION SELECT agent4qry.*
FROM agent4;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

A union query won't do it. You need to join the various tables with table1.
You probably will need left joins if there aren't matching records in the
other tables.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

ram said:
Jerry Do you know how i can write the query so that the territory, region and
broker. shows for each of the 63,000 times listed in table1.

Thanks for any help


Jerry Whittle said:
How many records are returned from the following SQL statement? I'm thinking
that it's closer to 15,000 than 63,000. In other words you have fewer actual
customers in table1 than you think.

SELECT DISTINCT [customer],
[Agency Name],
[Territory],
[Region],
[Broker],
[Market],
[Office],
[Distribution]
FROM table1 ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


ram said:
Hi Jerry, I went back and created another union query following your
suggestion.
However I'm still having a problem.
The total number of customers is 63,000 (found in table 1)
tables 2, 3, 4 has the territory region and broker for the 63,000 customers

when I try to create 1 table that shows all 63,000 customers with their
territory, region and broker it not showing all 63,000 customers

when I use union all I get back 72,000
when i use only union I get back 15,000

This is the code I'm using thanks for your time and help


SELECT table1.[customer], [Agency
Name],[Territory],[Region],[Broker],[Market],[Office],[Distribution]
FROM table1
Union
select Agent as customer,[Agency
Name],[Territory],[Region],[Broker],[Market],[office],[Distribution]
from table2
UNION
SELECT Producer as customer,[Agency
Name],[Territory],[Region],[Broker],[Market],[office],[Distribution]
FROM TABLE3;



:

As you are using a UNION (as opposed to a UNION ALL) all the duplicates
should be removed. Since you are using the * wildcard, all fields are brought
in. If any field is slightly different, that is a different record. Could be
something as simple as a trailing or leading space in a field.

Instead of using the *, pull down the actual field names from each query
until you find the field that is causing the duplicates.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I have a union Query linked to 4 select queries. The field names are the same
in each of the four queries. I'm trying to combine the four queries into 1
table showing the customer with their corresponding territory, region and
broker. all the tables are related by a customer field.

It looks like the union query is giving me the list I want but the customer
count double and I don't know why. Can anyone tell me why the number of
records has doubled. I'm using the following code

SELECT agent1qry.*
FROM agent1qry
Union
Select agent2qry.*
FROM agent2qry
UNION
SELECT agent3qry.*
FROM agent3qry
UNION SELECT agent4qry.*
FROM agent4;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

OK Thank You

Jerry Whittle said:
A union query won't do it. You need to join the various tables with table1.
You probably will need left joins if there aren't matching records in the
other tables.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

ram said:
Jerry Do you know how i can write the query so that the territory, region and
broker. shows for each of the 63,000 times listed in table1.

Thanks for any help


Jerry Whittle said:
How many records are returned from the following SQL statement? I'm thinking
that it's closer to 15,000 than 63,000. In other words you have fewer actual
customers in table1 than you think.

SELECT DISTINCT [customer],
[Agency Name],
[Territory],
[Region],
[Broker],
[Market],
[Office],
[Distribution]
FROM table1 ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hi Jerry, I went back and created another union query following your
suggestion.
However I'm still having a problem.
The total number of customers is 63,000 (found in table 1)
tables 2, 3, 4 has the territory region and broker for the 63,000 customers

when I try to create 1 table that shows all 63,000 customers with their
territory, region and broker it not showing all 63,000 customers

when I use union all I get back 72,000
when i use only union I get back 15,000

This is the code I'm using thanks for your time and help


SELECT table1.[customer], [Agency
Name],[Territory],[Region],[Broker],[Market],[Office],[Distribution]
FROM table1
Union
select Agent as customer,[Agency
Name],[Territory],[Region],[Broker],[Market],[office],[Distribution]
from table2
UNION
SELECT Producer as customer,[Agency
Name],[Territory],[Region],[Broker],[Market],[office],[Distribution]
FROM TABLE3;



:

As you are using a UNION (as opposed to a UNION ALL) all the duplicates
should be removed. Since you are using the * wildcard, all fields are brought
in. If any field is slightly different, that is a different record. Could be
something as simple as a trailing or leading space in a field.

Instead of using the *, pull down the actual field names from each query
until you find the field that is causing the duplicates.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I have a union Query linked to 4 select queries. The field names are the same
in each of the four queries. I'm trying to combine the four queries into 1
table showing the customer with their corresponding territory, region and
broker. all the tables are related by a customer field.

It looks like the union query is giving me the list I want but the customer
count double and I don't know why. Can anyone tell me why the number of
records has doubled. I'm using the following code

SELECT agent1qry.*
FROM agent1qry
Union
Select agent2qry.*
FROM agent2qry
UNION
SELECT agent3qry.*
FROM agent3qry
UNION SELECT agent4qry.*
FROM agent4;
Click to expand...
Click to expand...
Click to expand...
 

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

Union query 5
Union Query and Field Alias 7
Union Query with 3 tables 3
Query is too complex 5
Union Query of Two Queries (Part 2) 2
Union Query - Group & Sum 1
union query 4
Union Query if Yex/No 13

Top