My comments are embedded below.
Vic said:
Brian,
Thank you for the response.
Is there a way to do this without a monster table? The issue is a bit more
complex in that:
1) Each monthly table will actually have multiple siblings: forecast
transactions, forecast revenue, actual transactions, actual revenue. So
rather than 12 monthly tables, there are really 48.
Would you rather have 4 tables that are easy to maintain and write queries
for, or 48 (or more, as time goes on) that aren't?
2) I can get IT to create the actual transaction and actual revenue tables
each month and just down load them. That has a major impact on my workload.
You can transform the data that they provide into the format you want using
a query. For example, suppose the supplied actual revenue table looks
something like this:
Product Family, Sales Source 1 Revenue, Sales Source 2 Revenue, ..., Sales
Source 9 Revenue
Family 1, 123, 456, ..., 789
Family 2, 234, 567, ..., 980
..
..
..
Family 28, 345, 678, 901
You could then append the data into a single table that stores actual
revenue for all months using a query whose SQL looks something like this:
PARAMETERS [Supplied Year] Long, [Supplied Month] Long;
INSERT INTO
[Single Actual Revenue Table]
([Year],
[Month],
[Product Family],
[Sales Source],
[Actual Revenue] )
SELECT *
FROM
(SELECT
[Supplied Year] AS [Year],
[Supplied Month] AS [Month],
[Supplied Actual Revenue Table].[Product Family],
"Sales Source 1" AS [Sales Source],
[Supplied Actual Revenue Table].[Sales Source 1 Revenue] AS [Actual Revenue]
FROM
[Supplied Actual Revenue Table]
UNION SELECT
[Supplied Year] AS [Year],
[Supplied Month] AS [Month],
[Supplied Actual Revenue Table].[Product Family],
"Sales Source 2" AS [Sales Source],
[Supplied Actual Revenue Table].[Sales Source 2 Revenue] AS [Actual Revenue]
FROM
[Supplied Actual Revenue Table]
..
..
..
UNION SELECT
[Supplied Year] AS [Year],
[Supplied Month] AS [Month],
[Supplied Actual Revenue Table].[Product Family],
"Sales Source 9" AS [Sales Source],
[Supplied Actual Revenue Table].[Sales Source 9 Revenue] AS [Actual Revenue]
FROM
[Supplied Actual Revenue Table]);
You would have to change this query if the sales sources changed.
3) I will need to compare actuals to forecasts to create deviation analyses.
You might do this for all years, months, product families, and sales sources
in one query whose SQL looks something like this:
SELECT
[Single Actual Revenue Table].[Year],
[Single Actual Revenue Table].[Month],
[Single Actual Revenue Table].[Product Family],
[Single Actual Revenue Table].[Sales Source],
[Single Actual Revenue Table].[Actual Revenue],
[Single Forecast Revenue Table].[Forecast Revenue],
[Single Forecast Revenue Table].[Forecast Revenue] - [Single Forecast
Revenue Table].[Forecast Revenue] AS [Deviation]
FROM
[Single Actual Revenue Table]
INNER JOIN
[Single Forecast Revenue Table]
ON
[Single Actual Revenue Table].[Year] = [Single Forecast Revenue
Table].[Year]
AND
[Single Actual Revenue Table].[Month] = [Single Forecast Revenue
Table].[Month]
AND
[Single Actual Revenue Table].[Product Family] = [Single Forecast Revenue
Table].[Product Family]
AND
[Single Actual Revenue Table].[Sales Source] = [Single Forecast Revenue
Table].[Sales Source]
This assumes you have forecasts and actuals for each combination of year,
month, product family and sales source.
4) I will need to use revenue data and transaction data to get revenue per
unit.
You could do this for all years, months, product families, and sales sources
in one query using the a similar approach to what I suggested in item 3.
I was hoping to stay with the structure of the data because it fits the way
our company operates.
Ideas?
Vic
Brian Camire said:
You might consider restructuring your data into one table like this:
Year,Month,Product Family,Sales Source,Forecast
2004,5,Family 1,Source 1,290
2004,5,Family 1,Source 2,674
.
.
.
2004,5,Family 1,Source 9,312
2004,5,Family 2,Source 1,767
2004,5,Family 2,Source 2,792
.
.
.
2004,5,Family 2,Source 9,502
.
.
.
2004,5,Family 28,Source 9,272
2004,6,Family 1,Source 1,123
.
.
.
You could then get a total monthly forecast by product family using a query
something like:
SELECT
[Your Table].[Year],
[Your Table].[Month],
[Your Table].[Product Family],
Sum([Your Table].[Forecast]) AS [Total Forecast]
FROM
[Your Table]
GROUP BY
[Your Table].[Year],
[Your Table].[Month],
[Your Table].[Product Family]
and a total quarterly forecast by product family using a query something
like:
SELECT
[Your Table].[Year],
Int(([Your Table].[Month]-1)/3) AS [Quarter],
[Your Table].[Product Family],
Sum([Your Table].[Forecast]) AS [Total Forecast]
FROM
[Your Table]
GROUP BY
[Your Table].[Year],
Int(([Your Table].[Month]-1)/3),
[Your Table].[Product Family]
and you wouldn't have to create new tables for the months in next year, or
add or remove columns if your sales sources changed.
Vic said:
I am an Access newbie, and I have a multi-part question, but let's start
simple.
I have a financial forecast DB with 12 identically structured tables*, one
corresponding to each month. Each table contains 28 records (rows),
corresponding to a different product family. There are 9 columns, and each
corresponds to a sales source. I would like to create a total monthly
forecast by product family (sum across each row) and a total monthly
forecast by sales source (sum down each column). Can anyone give me a
starting point? Is this a crosstab query?
Next step: I need to make a quarterly forecast by summing together the same
as above for three months.
I would be grateful for any help.
Thanks in advance,
Vic
*Actually, there is a 13th table. It contains the 28 product names.
