Union Queries & Group By

[Guest]

Hello Everyone,
Ok, so here's the deal. We have 5 teams that work here and some customers
overlap depending on the required product. Team 1 can have Customer 1 because
he orders Product Type 1 but Team 2 can have the same customer because he
also orders Product Type 2. So, I am trying to combine the spreadsheets that
currently have our customer assignments but am having a little trouble. I am
doing this in Access so that evertime a spreadsheet (Linked in the database)
is updated it updates the 'All Departments' list.

On to the issue:

Customer 1 buys Product 1 and Product 2 so he is assigned to Team 1 and Team
2. My first step was to design a Query to result in showing all of Team 1's
assignments and those of Team 2's that match by Acct#. I also have a table to
show all of Team 2's customers since not all of them overlap. When I designed
a Union Query the rows show up as duplicates (see below) for those that are
overlapping:
Title: Acct# Customer CSR TI CSR
Row 1: 3524 Customer 1 Team 1 Team 2
Row 2: 3524 Customer 1 Team 2

I want to get rid of the second row since the first one has the info I need.
Unfortunately the 'Customer' field does not always match. It depends on how
the teams enter their customer's in but the Acct# field always matches. As
you can see below, there are 5 teams. Team 5 is the one that will overlap
with any of the other teams depending on the customer and location / product.
Please help!

SELECT [Acct#], [Customer], [CSR], [TI CSR] FROM TEAM1
UNION ALL SELECT [Acct#], [Customer], [CSR], [TI CSR] FROM TEAM2
UNION ALL SELECT [Acct#], [Customer], [CSR], [TI CSR] FROM TEAM3
UNION ALL SELECT [Acct#], [Customer], [CSR], [TI CSR] FROM TEAM4
UNION ALL SELECT [Acct#], [Customer], [CSR], [TI CSR] FROM TEAM5

 

[Ken Snell \(MVP\)]

If you use UNION instead of UNION ALL, it will remove duplicate records from
the final recordset.

 

[Guest]

Thank you for the quick response. Unfortunately that did not do it. It still
duplicates rows. I tried to group by [Acct#] but kept getting all kinds of
errors. Do you know how I would incorporate that with my SQL Statement? Maybe
i am just inputing it wrong.

SELECT [Acct#], [Customer], [CSR], [TI CSR] FROM BR35
UNION SELECT [Acct#], [Customer], [CSR], [TI CSR] FROM INTERNATIONALQ
UNION SELECT [Acct#], [Customer], [CSR], [TI CSR] FROM MBUQ
UNION SELECT [Acct#], [Customer], [CSR], [TI CSR] FROM POPQ
UNION SELECT [Acct#], [Customer], [CSR], [TI CSR] FROM TIQ;

Thanx again!

 

[Ken Snell \(MVP\)]

How will you decide which customer name to use for the single record, if the
customer name often is different between the teams?

Setting aside the above question, perhaps a query like this will work (based
on your original statement that just the last query contains overlapping
records):

SELECT [Acct#], [Customer], [CSR], [TI CSR]
FROM BR35
UNION
SELECT [Acct#], [Customer], [CSR], [TI CSR]
FROM INTERNATIONALQ
UNION
SELECT [Acct#], [Customer], [CSR], [TI CSR]
FROM MBUQ
UNION
SELECT [Acct#], [Customer], [CSR], [TI CSR]
FROM POPQ
UNION
(SELECT TIQ.[Acct#], TIQ.[Customer], TIQ.[CSR], TIQ.[TI CSR]
FROM TIQ
LEFT JOIN
(SELECT [Acct#]
FROM BR35
UNION
SELECT [Acct#]
FROM INTERNATIONALQ
UNION
SELECT [Acct#]
FROM MBUQ
UNION
SELECT [Acct#]
FROM POPQ) AS TAcct
ON TIQ.[Acct#] = TAcct.[Acct#]
WHERE TAcct.[Acct#] IS NULL);


If more than just the last table (TIQ) contains overlapping records, then a
somewhat different approach will be needed, and the answer to my first
question will be very important.
--

Ken Snell
<MS ACCESS MVP>

Thank you for the quick response. Unfortunately that did not do it. It
still
duplicates rows. I tried to group by [Acct#] but kept getting all kinds of
errors. Do you know how I would incorporate that with my SQL Statement?
Maybe
i am just inputing it wrong.

SELECT [Acct#], [Customer], [CSR], [TI CSR] FROM BR35
UNION SELECT [Acct#], [Customer], [CSR], [TI CSR] FROM INTERNATIONALQ
UNION SELECT [Acct#], [Customer], [CSR], [TI CSR] FROM MBUQ
UNION SELECT [Acct#], [Customer], [CSR], [TI CSR] FROM POPQ
UNION SELECT [Acct#], [Customer], [CSR], [TI CSR] FROM TIQ;

Thanx again!

If you use UNION instead of UNION ALL, it will remove duplicate records
from
the final recordset.

 

[Guest]

That worked!!! You are awesome. )

To answer your question, the customer name was technically the same for each
team but would maybe miss the 'inc' at the end of the name. Such as Team 1
would have Morreggia Inc but Team 2 would just have Morreggia. *shrug* That
is why I decided I should go with the Acct# since that was never different.

Thank you again for your awesome help!

How will you decide which customer name to use for the single record, if the
customer name often is different between the teams?

Setting aside the above question, perhaps a query like this will work (based
on your original statement that just the last query contains overlapping
records):

SELECT [Acct#], [Customer], [CSR], [TI CSR]
FROM BR35
UNION
SELECT [Acct#], [Customer], [CSR], [TI CSR]
FROM INTERNATIONALQ
UNION
SELECT [Acct#], [Customer], [CSR], [TI CSR]
FROM MBUQ
UNION
SELECT [Acct#], [Customer], [CSR], [TI CSR]
FROM POPQ
UNION
(SELECT TIQ.[Acct#], TIQ.[Customer], TIQ.[CSR], TIQ.[TI CSR]
FROM TIQ
LEFT JOIN
(SELECT [Acct#]
FROM BR35
UNION
SELECT [Acct#]
FROM INTERNATIONALQ
UNION
SELECT [Acct#]
FROM MBUQ
UNION
SELECT [Acct#]
FROM POPQ) AS TAcct
ON TIQ.[Acct#] = TAcct.[Acct#]
WHERE TAcct.[Acct#] IS NULL);


If more than just the last table (TIQ) contains overlapping records, then a
somewhat different approach will be needed, and the answer to my first
question will be very important.
--

Ken Snell
<MS ACCESS MVP>

Thank you for the quick response. Unfortunately that did not do it. It
still
duplicates rows. I tried to group by [Acct#] but kept getting all kinds of
errors. Do you know how I would incorporate that with my SQL Statement?
Maybe
i am just inputing it wrong.

SELECT [Acct#], [Customer], [CSR], [TI CSR] FROM BR35
UNION SELECT [Acct#], [Customer], [CSR], [TI CSR] FROM INTERNATIONALQ
UNION SELECT [Acct#], [Customer], [CSR], [TI CSR] FROM MBUQ
UNION SELECT [Acct#], [Customer], [CSR], [TI CSR] FROM POPQ
UNION SELECT [Acct#], [Customer], [CSR], [TI CSR] FROM TIQ;

Thanx again!

If you use UNION instead of UNION ALL, it will remove duplicate records
from
the final recordset.

 

[Ken Snell \(MVP\)]

That worked!!!

You're welcome.