Union Query

[ram]

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;

 

[Jerry Whittle]

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.

 

[ram]

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]

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.

 

[ram]

it returns 6,935

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;

 

[ram]

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

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;

 

[Jerry Whittle]

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.

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

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;

 

[ram]

OK Thank You

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.

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

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;