Just can't figure out this Query

  • Thread starter Thread starter Terry
  • Start date Start date
T

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!!!
 
*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
 
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!!!
.
Click to expand...
.
Click to expand...
 
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!!!
.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top