Converting Excel Workbook with 36 colmns per worksheet to Access

  • Thread starter Thread starter KenV
  • Start date Start date
K

KenV

I am building an Access db - accounting application that requires data for
each month captured separately. In Excel I have 36 columns representing
Month 1 to Month 36 and it is very easy to enter data into this sheet. Can
an Access Table/Form be created that would allow input in a datasheet view
similar to Excel for the 36 months. It has been suggested to me that I
should create one field for PeriodEndDt and another for Amount. That would
require dataentry in rows.
 
You were given a very good suggestion. This is how databases work.

Here's something to ponder: What happens when the requiement changes to
keeping 48 months worth of data? You would need to modifiy the table as you
would a spreadsheet. However unlike the spreadsheet, you would probably need
to revise every query, form, or report based on that table! If you build
"down" instead of "across" as was suggested, your queries, forms, and report
would all probably work just fine if you had to change the number of months.
 
Access is a relational database. Excel is a spreadsheet. What works in
Excel usually requires extensive work-arounds in Access.

Access is optimized for well-normalized data. Spreadsheets are rarely
well-normalized.

This is a "pay now or pay later" situation. If you pay now (learn
Access/normalization), you'll get good use of Access. If you choose not to
normalize your data, you WILL pay later, in the form of having to come up
with work-arounds and having to be continuously updating/upgrading the
design of your database.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP
 
Might I voluntier you are asking for help in how to "pivot" your spreadsheet
into a skinny Access Table? If so...
Do you know VBA? If so, this may help you along.

In this example, table AFlows has the following fields:
IDCase = a Foreign Key to another table that "names the spreadsheet"
IDLineitem = A foreign Key to another table that id's the Row name or
number.
Year = This is my column date. You would use PeriodEndDt.
Value = This is the cell value to store. You would use Amount.


Early in the process, define the workbook.
Open the Database
set dbPW = dao.OpenDatabase(..FilePathSpec..)
Open the Recordset of your Database Table to hold your data
Dim grsAFlows as dao.recordset 'Sorry, I prefer DAO to ADO.
Set grsAFlows = dbPW.OpenRecordset("Select * from AFlows;")

Set the Range of the area to load.
Set grngLFlows = .Names("LFlows").RefersToRange

For each row in the range,
get the idLine from the other table.
Call L40_WriteFlows below, passing the current LineID and Row number of
the range.
Next row in range.

Close all recordsets, close the database.


Sub L40_WriteFlows(idLine As Integer, iRow As Integer)
'for each column in the range grngFlows, on the passed row,
'add a flow row, write the Year, IDLine, and value into the row.
Dim i As Integer
Dim ni As Integer
ni = grngLFlowYear.Columns.Count
'grngFlows is a global variable of the Range to load.
'grsAFlows is a global Open Recordset for Edit.

If idLine > 0 Then
For i = 1 To ni

With grsAFlows
.AddNew
!IDCase = gIDCase
!IDLineitem = idLine
!Year = CSng(grngLFlowYear.Cells(1, i))
!Value = grngLFlows.Cells(iRow, i)
.Update
End With
Next
End If
End Sub


I left out a lot of steps, but if you have never done this before, I hope I
saved you some time.

Use a crosstab query to retrieve data in the familiar format of months as
column headers.

Also, do a VBA help lookup for the method range.CopyFromRecordset.
It plops a query result into a spreadsheet quickly.
You'll like it!
 
Back
Top