Expression with a cell reference

[nitengale]

Table 1 has Month and total forecasted sales by month.
Table 2 has historical sales broken down by a certain criteria.

I'm looking to create a query where I can add the following calculations:

1. Table 2 line 1 divided by grand total table 2 to give me a % (same
calculation for each criteria in this table which should total 100%).
2. I then want to take that resulting % and multiply by table 1 for each
month to give me forecasted dollars by each criteria.

Any suggestions on the easiest way to do this?

 

[Daryl S]

Nitengale -

The first query will look like this (substitute your table and field names,
assumes you are grouping by some keyvalue):

SELECT [keyvalue], [SalesAmt], [SalesAmt]/DSum("[Table2]","[SalesAmt]") As
SalesPercent
FROM [Table2]

Save this query.

The second query would look like this (again, substitute correct table,
query, and field names):

SELECT [keyvalue], [Month], [Total Forcasted] * [SalesPercent] AS ForecastAmt
FROM [Query1], [Table1];