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