relationships

[Tim]

Hi,

I have three tables I need help with. First is an
employee table, second is a sales person table and third
is a corporate office sales people table. When I run a
query to compare the corporate sales people to the whole
sales team I only return a result that has the same number
of lines as the corporate sales people. For example, if I
query to see all the sales people who had more sales than
a corporate sales person, that would be all the corporate
sales people individually compared to all the sales people
nationwide including the corporate sales people, I get a
result that is only 21 rows of data. We have 21 corporate
sales people and over 200 sales people nationwide. The
result of the query is one corporate sales person compared
to one national sales person.

What I would like to see is a query that returns:

# Corp Name Sales # Nat Name Sales
123 John Smith 100,000 234 Jane Doe 125,000
123 John Smith 100,000 456 Jane Smith 130,000
123 John Smith 100,000 345 John Doe 150,000
456 Jane Smith 130,000 345 John Doe 150,000
456 Jane Smith 130,000 567 Joe Blow 175,000


I hope this makes sense. I think it might be the
relationships between the tables that need to be changed
but I am not sure. Please advise.

Regards, Tim

 

[Tim]

Tom,

Thank you for your help. You were right. I made the
changes you suggested and got what I needed. Thanks again.

Regards, Tim

-----Original Message-----
Dear Tim:

I'm not sure, but I think you're saying there is NO relationship
between these tables. That is, you don't want to consider that the
table of employees contains the persons in the table of sales persons.

In this case you would not JOIN on any relationships in the query.
This then creates a cross-product of the two tables. For 21 corporate
sales persons and 200 sales persons, you would then start with 4200
combinations of the two. Your criteria can then reduce this number.

If you are working in the query design grid, delete the lines
connecting these table to create a cross-product. If you are working
in the SQL, replace INNER JOIN (or LEFT JOIN, etc) with a comma and
drop the ON clause.

Then set up the criteria and sorting you desire.

There's not enough information in your post for me to take this and
farther at this point.