Prev: Access 2007 Excel Import Error
Next: Refresh unbound textbox
From: nitengale on 27 Apr 2010 13:15
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?
From: Daryl S on 27 Apr 2010 14:05
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];

--
Daryl S


"nitengale" wrote:

> 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?
 | 
Pages: 1
Prev: Access 2007 Excel Import Error
Next: Refresh unbound textbox