RE: Union Query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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.

"ram" wrote:

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;

.

Relevant Pages

  • RE: Union Query
    ... customers in table1 than you think. ... Jerry Whittle, Microsoft Access MVP ... The total number of customers is 63,000 ... when i use only union I get back 15,000 ...
    (microsoft.public.access.queries)
  • RE: Union Query
    ... Jerry Whittle, Microsoft Access MVP ... The total number of customers is 63,000 ... when i use only union I get back 15,000 ...
    (microsoft.public.access.queries)
  • RE: Union Query
    ... customers in table1 than you think. ... Jerry Whittle, Microsoft Access MVP ... The total number of customers is 63,000 ... when i use only union I get back 15,000 ...
    (microsoft.public.access.queries)
  • Establishing Precedence In ORDERBY Condition Causing Problems.
    ... from Customers withunion select distinct user_name ... from CustomerPhotos withunion select distinct email_address ... Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID ... Left Join SavedSearches tab6 on (drv.emailAddress = ...
    (comp.databases.ms-sqlserver)
  • Re: When I change the querystring to use a different table, the fieldnames dont update
    ... query rather than try to use Querystring to modify an existing one. ... p rows and q columns in table1 and r rows and s columns table2, ... The first table is a list of customers. ... I want to store the values from a "standard customer" record in table2 ...
    (microsoft.public.word.mailmerge.fields)