query problem

[santoni]

I really need help me with this. I'm sure it's really easy for most. I
really appreciate any help. Thanks!

I have tables Employee and Sales. In Employees the yes/no field
IsManager denotes Manager. Sales has Year, Region, EmplyeeID, Sales
and other results of the sale.

I want to compare sales for several years between 4 salesmen. One of
them is also the Manager. The trouble is that all have worked during
different time periods from each other in this Dept. except for the
Manager, who has been here all years and worked with everyone. Plus
the sales are in different regions. A query results in this data set
that has the correct information but some not pertinent data as well.

Now the data look like this but how can I get the reduced data set(s)
I've shown farther down below? I think I need to specify criteria
within some fileds that set certain conditions to be matched in other
fields.
Year Region Salesman Sales Position
1997 2 Cabstone 6259 Manager
1998 7 Cabstone 1018 Manager
1999 11 Cabstone 2749 Manager
1999 11 Jumper 2927 Salesman
2000 16 Cabstone 4459 Manager
2000 16 Jumper 4984 Salesman
2000 17 Cabstone 5717 Manager
2001 32 Cabstone 4423 Manager
2002 42 Cabstone 3495 Manager
2002 43 Cabstone 2583 Manager
2003 58 Cabstone 4547 Manager
2003 58 Dunsten 5026 Salesman
2003 59 Cabstone 3432 Manager
2004 62 Cabstone 5487 Manager
2004 62 Dunsten 5067 Salesman
2004 62 Sevenrude 4743 Salesman
2004 63 Cabstone 3236 Manager
2005 71 Cabstone 3285 Manager
2005 71 Dunsten 3516 Salesman
2005 71 Sevenrude 3753 Salesman
2006 79 Cabstone 3003 Manager
2006 79 Dunsten 2807 Salesman
2006 79 Jumper 2542 Salesman
2006 79 Sevenrude 3749 Salesman
2006 80 Cabstone 2668 Manager
2006 80 Sevenrude 3124 Salesman

I think I'd like to end up with these 3 comparisons:
Jumper vs Cabstone
1999 11 Cabstone 2749 Manager
1999 11 Jumper 2927 Salesman
2000 16 Cabstone 4460 Manager
2000 16 Jumper 4985 Salesman
2006 79 Cabstone 3003 Manager
2006 79 Jumper 2543 Salesman

Dunsten vs Cabstone
2003 58 Cabstone 4547 Manager
2003 58 Dunsten 5027 Salesman
2004 62 Cabstone 5487 Manager
2004 62 Dunsten 5068 Salesman
2005 71 Cabstone 3286 Manager
2005 71 Dunsten 3517 Salesman
2006 79 Cabstone 3003 Manager
2006 79 Dunsten 2808 Salesman

Sevenrude vs Cabstone
2004 62 Cabstone 5487 Manager
2004 62 Sevenrude 4744 Salesman
2005 71 Cabstone 3286 Manager
2005 71 Sevenrude 3754 Salesman
2006 79 Cabstone 3003 Manager
2006 79 Sevenrude 3749 Salesman
2006 80 Cabstone 2668 Manager
2006 80 Sevenrude 3125 Salesman

[hbean]

I think if you specify the names of the salesmen you want to compare
in the criteria of your query, you should be able to get the results
you want - i.e., "Sevenrude" or "Cabstone" in the criteria field of
the salesman field should give you the last set of results. If you
wanted to get fancy, you could use parameters, [person one] or [person
two] - that way you wouldn't have to change the query every time you
wanted a different comparison. Or if Cabstone is going to be
consistent across your results, you could do a combination of the two.

HTH.

[santoni]

On Mar 14, 5:09 pm, "hbean" <[e-mail]:

I think if you specify the names of the salesmen you want to compare
in the criteria of your query, you should be able to get the results
you want - i.e., "Sevenrude" or "Cabstone" in the criteria field of
the salesman field should give you the last set of results. If you
wanted to get fancy, you could use parameters, [person one] or [person
two] - that way you wouldn't have to change the query every time you
wanted a different comparison. Or if Cabstone is going to be
consistent across your results, you could do a combination of the two.

HTH.

hbean,
Yes. I se that Thanks. But I don't think I was clear that I want to
get the average (totals query) for the salesman & for the Manager for
their respective sales in the same years & same regions worked. Right
now the Manager average is across all of his years.

Thanks for your help.

santoni

[hbean]

On Mar 15, 5:00 pm, "santoni" <[e-mail]:

On Mar 14, 5:09 pm, "hbean" <[e-mail]:

I think if you specify the names of the salesmen you want to compare
in the criteria of your query, you should be able to get the results
you want - i.e., "Sevenrude" or "Cabstone" in the criteria field of
the salesman field should give you the last set of results. If you
wanted to get fancy, you could use parameters, [person one] or [person
two] - that way you wouldn't have to change the query every time you
wanted a different comparison. Or if Cabstone is going to be
consistent across your results, you could do a combination of the two.

HTH.

hbean,
Yes. I se that Thanks. But I don't think I was clear that I want to
get the average (totals query) for the salesman & for the Manager for
their respective sales in the same years & same regions worked. Right
now the Manager average is across all of his years.

Thanks for your help.

santoni

Okay, here's what worked for me:

Set up your query as a totals query. Include your four fields. For
the "Salesman" field, under the "Total", change it to "Where" - and
then in the criteria, you can either list the two people you want to
compare or use parameters. Don't display this field. For the "Sales"
field, change the "Total" to "Avg". - This actually yields more than
what you want, because it also gives you the years just with Capstone
alone, but you can set your criteria for the specific years if you
need to.

HTH.