Access 2007: crosstab view to insert values into a database

Question

I am stuck and need help/advise. I am pretty sure that I am not the first one to run into this problem, but I can't seem to find the answer on the web.

We are collecting all kinds of data from many factories. This is mainly forecasted values of yearly peak production, etc. This data collection is repeated every year.

We currently keep track of this data in Excel, which has the following structure:

Factory | 2010 | 2011 | 2012 | ..
----------------------------------
A       |  20  |  30  |  28  | ..
B       |      |  39  |  55  | ..

In this example factory B just starts production in the year 2011. If we collect data for an additional year, we simply add a column. If forecasted data changes, we simply enter the new values and lose the old ones. You can imagine that this way of working has its limitations: the table becomes rather sparse for missing data. Old values cannot be traced back. There is no reference to the source of the values.

To satisfy our need for a better system, I put my antique knowledge of databases to work. In Access 2007 I created the following structure:

Table: Factories

FacID |  FactoryName
---------------------
1     |  A
2     |  B

Table: Sources

SouID | Source    | SourceDate
---------------------------------
1     | DocumentX | Sep. 2009 
2     | DocumentY | Jan. 2010

Table: Parameters

ParID | FacID | SouID | ParamType      | Year | Value
------------------------------------------------------
1     | 1     | 1     | PeakProduction | 2010 | 20
2     | 1     | 1     | PeakProduction | 2011 | 30
3     | 1     | 1     | PeakProduction | 2012 | 28
4     | 2     | 1     | PeakProduction | 2011 | 39
5     | 2     | 2     | PeakProduction | 2012 | 55

For each new data collection we just add a new source document and append the Parameters table. In this way, we can always revert back to old data. Furthermore, if additional years are collected, there is no need to add additional columns to any table.

Although the actual setup is more complex, above examples is sufficient to illustrate the problem that I am running into: To enter data into the database I would like to have a single form which resembles the original Excel sheet lay-out, i.e.:

Factory | 2010 | 2011 | 2012 |
------------------------------
A       |      |      |      |
B       |      |      |      |

Of course, this form will have some drop-down menu to select the source document and parameter type ("PeakProduction" in the example).

My question: With crosstab queries it is easy to create such a view based of existing data in the database, however, entering new values is not allowed. What can I do to make this work, and how?

Should I reconsider the design of my database? Should I work with VBA? Link the Access database with Excel sheets?

Thanks!

It is almost never a good idea to replicate something from Excel in any database front-end, however, here is one idea: wiki.lessthandot.com/index.php/Crosstabs,_forms_and_updating — Fionnuala
Crosstab queries are not designed for allowing data input, usually because the view can come from multiple tables and you would have to satisfy all the indexes etc, or also the fact that you won't show all the tables fields within one crosstab query — Matt Donnan
Thank you Remou for providing the link. It demonstrates an elegant solution for my question and .... it works fine for inputting limited datasets by hand. However, copy/pasting large data sets from Excel datasheets into the Access form does not seem to be straigthforward. — Rutger

ChrisPadgham ChrisPadgham · Accepted Answer · 2012-03-01T21:45:01

Where you are dealing with 2 dimensional data that is normalised into a table it is problematic for the user to maintain using Access. My approach to this has been to use the appropriate tool for the job, which looks like Excel in this case. I have the an excel spreadsheet template for data entry. The user enters the data into that. Then in VBA I open the spreadsheet as an embedded object, retrieve the cell contents and insert it into the table. Something like below.

dim myRec as recordset
dim xlApp as Excel.application
dim xlWrksht as Excel.worksheet


set myRec=currentdb.openrecordset("NameOfTable")
set xlApp=createobject("Excel.Application")
set xlWrksht=xlApp.Open("PathOfWorksheet").Worksheets( "WorksheetNumber")

myrec.addnew
myrec.fields("NameOfFields")=xlWrkSht.cells(1,"A")
......
myRec.update

Access 2007: crosstab view to insert values into a database

1 Answers