If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Converting Excel Workbook with 36 colmns per worksheet to Access
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. |
#2
|
|||
|
|||
Converting Excel Workbook with 36 colmns per worksheet to Access
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. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "KenV" wrote: 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. |
#3
|
|||
|
|||
Converting Excel Workbook with 36 colmns per worksheet to Access
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 "KenV" wrote in message ... 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. |
#4
|
|||
|
|||
Converting Excel Workbook with 36 colmns per worksheet to Access
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! -- Stephen Rasey WiserWays, LLC Houston, TX "KenV" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|