query returning extra items

[cbrsix]

Hi,

I am fairly new to Access and am trying to design a query that returns certain information from 2 tables that have a common relationship. The problem that I am running into is that the query, when ran, returns more results than the original table has. I can't figure out why or how to fix it. Any ideas?

[pbaldy]

What's the SQL of the query? It sounds like you've neglected to join the 2 tables on the common field(s). That will produce a Cartesian product.

[cbrsix]

SELECT [Client Accounts].[Last Name], [Client Accounts].[First Name], [Client Accounts].[Middle Initial], [Client Accounts].Suffix, [Client Accounts].Address, [Client Accounts].City, [Client Accounts].State, [Client Accounts].[Zip Code], [Client Accounts].Email, [Registered Representatives].[Last Name], [Registered Representatives].[First Name]
FROM [Registered Representatives] INNER JOIN [Client Accounts] ON [Registered Representatives].[Rep Number] = [Client Accounts].[Rep Name].Value
ORDER BY [Client Accounts].[Last Name], [Client Accounts].[First Name];

[cbrsix]

I just did it all in the Design View not through the SQL View

[pbaldy]

That should return all records that have the rep field in common. Can you post the db if it's still not doing what you expect?

FYI, design view is just a user friendly interface to SQL view. Internally, Access uses the SQL.

[cbrsix]

Thanks. I figured out why it was doing that. It was an oversight on my part.

[pbaldy]

No problem, glad you got it sorted out.