G
Guest
I have a report based on a crosstab query. It queries the results of ad sales by sales reps. Sales Reps is a row heading and the Issue is a column heading. The other row headings are Advertisers and Magazine ( My Issue field is a typical date field and we enter data as 1/1/04, 2/1/04, etc. Then the expression reformats the data like: Format ([Issue],â€mmmâ€) – so that the row headings are January, February, March on the report). At the bottom of each sales reps page, I calculate the total of sales of each month under each column.
My dilemma is this
Each sales rep has a budget. I’ve entered the budget figures in a different table called Budgets, with the following fields: Sales Rep, Magazine, Issue, Budget Amount. I’d like to have a DLookUp field at the bottom of the report that will show the January budget for that sales rep under the January actual sales figures
Right now, I can do it, but w/o the criteria and I’m not sure how to write the criteria. What I’ve written so far is: =DLookUp("[BUDGET AMOUNT]","Budgets"), but that only pulls up the first amount that’s registered in the Budgets’ table. It needs to look it up based on the Sales Rep, Magazine & Issue all being the same as that page of the report. (plus is the fact that the format is being changed in the crosstab query to January instead of 1/1/04 a factor?)
Can someone tell me how to write the criteria? Or, is this something that can be done
My dilemma is this
Each sales rep has a budget. I’ve entered the budget figures in a different table called Budgets, with the following fields: Sales Rep, Magazine, Issue, Budget Amount. I’d like to have a DLookUp field at the bottom of the report that will show the January budget for that sales rep under the January actual sales figures
Right now, I can do it, but w/o the criteria and I’m not sure how to write the criteria. What I’ve written so far is: =DLookUp("[BUDGET AMOUNT]","Budgets"), but that only pulls up the first amount that’s registered in the Budgets’ table. It needs to look it up based on the Sales Rep, Magazine & Issue all being the same as that page of the report. (plus is the fact that the format is being changed in the crosstab query to January instead of 1/1/04 a factor?)
Can someone tell me how to write the criteria? Or, is this something that can be done