Using a field as a variable in a query

[Shadow2K9]

I have a query I want to write that - instead of asking the user for input - looks up the value of the variable in a table of records. Specifically, I want to run an append query that looks at weekly sales by salesman and their customers. I have a seperate salesmen talbe that keeps track of our 15 salesmen but I want the query to loop through the salesman as it performs the query; running the query once for each salesmen. Example is below with questionable field in red.

INSERT INTO [Top 10 Report]

( SalesmanName, Salesman, Acct, Name, Period, Week, WeekSales10, WeekPlan10, WeekSales09, PeriodSales10, PeriodPlan10, PeriodSales09, YTDSales10, YTDPlan10, YTDSales09 )

SELECT

Salesman.SalesmanName, [Top 10 by Week].Salesman, [Top 10 by Week].Acct, [Top 10 by Week].Name, [Top 10 by Week].Period, [Top 10 by Week].Week, [Top 10 by Week].WeekSales10, [Top 10 by Week].WeekPlan10, [Top 10 by Week].WeekSales09, [Top 10 by Period].PeriodSales10, [Top 10 by Period].PeriodPlan10, [Top 10 by Period].PeriodSales09, [Top 10 by YTD].YTDSales10, [Top 10 by YTD].YTDPlan10, [Top 10 by YTD].YTDSales09

FROM

Salesman INNER JOIN (([Top 10 by Week] INNER JOIN [Top 10 by Period] ON ([Top 10 by Week].Salesman = [Top 10 by Period].Salesman) AND ([Top 10 by Week].Acct = [Top 10 by Period].Acct)) INNER JOIN [Top 10 by YTD] ON ([Top 10 by Week].Salesman = [Top 10 by YTD].Salesman) AND ([Top 10 by Week].Acct = [Top 10 by YTD].Acct)) ON Salesman.SalesmanNum = [Top 10 by Week].Salesman

WHERE

((([Top 10 by Week].Salesman)=[Salesman]![SalesmanNum]));

[llkhoutx]

I suspect

((([Top 10 by Week].Salesman)=[Salesman]![SalesmanNum])); 

, i.e. Salesman and SalesmanNum are probably different data types.

Note that:
1. String variables are enclosed with quotes or tic marks;
2. Date variables are enclosed with pound signs; and
3. Integer variables are not enclosed.