Just can't figure out this Query

[Terry]

Okay, Here's the table layout.....

Table1 Fields Table2 Fields
Branch Code Agent Code
Agent Code Zip Code

Each Zip code should only correspond to one branch code.
I need to come up with the query to show all cases where a
zip code appears in more than one branch_code; using
agent_code as a relationship.

This seems so easy but my mind is just coming up blank.
Thanks!!!

 

[GreySky]

*air SQL* --qryBase
SELECT T2.[Zip Code]
FROM [Table1] AS T1 INNER JOIN [Table2] AS T2
ON T1.[Agent Code] = T2.[Agent Code]
GROUP BY T2.[Zip Code]
HAVING Count(*) > 1

This query, if I wrote it properly, should return all zip
codes with duplicating branch codes.

Was this what you wanted?

David Atkins, MCP

 

[Terry]

That's not working, I'm still being returned way too many
zip codes. I think the problem may be that there are
multiple agents assigned to a zip code within the same
branch. Those shouldn't be included in the result set.

-----Original Message-----
*air SQL* --qryBase
SELECT T2.[Zip Code]
FROM [Table1] AS T1 INNER JOIN [Table2] AS T2
ON T1.[Agent Code] = T2.[Agent Code]
GROUP BY T2.[Zip Code]
HAVING Count(*) > 1

This query, if I wrote it properly, should return all zip
codes with duplicating branch codes.

Was this what you wanted?

David Atkins, MCP

-----Original Message-----
Okay, Here's the table layout.....

Table1 Fields Table2 Fields
Branch Code Agent Code
Agent Code Zip Code

Each Zip code should only correspond to one branch code.
I need to come up with the query to show all cases where a
zip code appears in more than one branch_code; using
agent_code as a relationship.

This seems so easy but my mind is just coming up blank.
Thanks!!!
.

.

 

[John Spencer (MVP)]

Try a nested query.

QryOne:
SELECT DISTINCT T1.ZIPCODE, T2.BranchCode
FROM Table1 as T1 INNER JOIN Table2 as T2
ON T1.[Agent Code] = T2.[Agent Code]

Then:
SELECT Q1.ZipCode, Count(Q1.BranchCode) as DupeCount
FROM QryOne as Q1
GROUP BY Q1.ZipCode
HAVING Count(Q1.BranchCode) > 1

That's not working, I'm still being returned way too many
zip codes. I think the problem may be that there are
multiple agents assigned to a zip code within the same
branch. Those shouldn't be included in the result set.

-----Original Message-----
*air SQL* --qryBase
SELECT T2.[Zip Code]
FROM [Table1] AS T1 INNER JOIN [Table2] AS T2
ON T1.[Agent Code] = T2.[Agent Code]
GROUP BY T2.[Zip Code]
HAVING Count(*) > 1

This query, if I wrote it properly, should return all zip
codes with duplicating branch codes.

Was this what you wanted?

David Atkins, MCP

-----Original Message-----
Okay, Here's the table layout.....

Table1 Fields Table2 Fields
Branch Code Agent Code
Agent Code Zip Code

Each Zip code should only correspond to one branch code.
I need to come up with the query to show all cases where a
zip code appears in more than one branch_code; using
agent_code as a relationship.

This seems so easy but my mind is just coming up blank.
Thanks!!!
.

.