Create Speadsheet Type Form Showing Historical And New Data

[DevelopV]

I am using Access 2010

I need to create a production forecast form based on previous sales history.
The history is based a sales and grouped by month & year

So on the form, which needs to be a continuous form, I want products to show as rows and months as columns
The sales history per month needs to be displayed as well as a field allowing to user to enter the production forecast.

Is this possible and if yes, how would I do it?
I have looked at using a crosstab query, but these are not updatable

Many thanks in advance

 

[lameid]

A lot of different thoughts come to mind and all are somewhat involved...

Basically, need more information to target the right solution. Some questions that will help get there but not necessarily the whole story...
I guess the most important question is what do you intend to do with the data that is being input? Is it ok to violate normaliztion rules and store forecast that way? Is the initial forcast determined by a query and you want to be able to type over it (this would perhaps be a crosstab and an append or update based on it) or do you want to display the historic data (likely subform) and enter the other data?

 

[DevelopV]

Hi lameid

Thanks and sorry for late reply.

I have the following fields in the table "tblProductionForecastData":
ProductId
Quantity
MonthYear

I want to show the data in a spreadsheet format with:
ProductId as row
MonthYear as column
Quantity as data.

Similar to crosstab query, BUT:
1. I want the actual quantity shown. I don't want the sum, min, max etc
2. I need to be able to edit the quantity
3. add and delete records

Many thanks

 

[SkipVought]

You do realize that a MonthYear string is practically useless for 1) sorting, collating or analyzing and 2) doing any kind of mathematical calculation that one can do with actual dates. I'd strongly recommend using a full date stored as the first of the month.

You can actually report from your table using a cross tab SUM, if your process stores ONE row per date. So each ProductID would end up having 12 rows per year.

 

[lameid]

It seems like maybe what you want to do is export a crosstab query to Excel as Skip suggests.

 

[lameid]

Didn't mean to post quite that fast.

In addition to Skips one row per date thought, you could also add grouping to anything that is unique, like the primary key. Just uncheck the show box in the query for this unique value you don't want to see and you should have the data you want regardless of repeated reporting values. Also, when using crosstabs this way, it is a good idea to specify the column headings so that the output doesn't change based on missing items. On the other hand, if you are exporting directly to Excel, it does not matter. If using within Access, I would favor a maketable query and code to accommodate varying field names if necessary.