[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 633
  • Last Modified:

Using Access to extend excel

All:

This is a more theoretical question. I work for a P&L group where we use extensively excel.
Our objective is to reconciliate the traders data into a comprehensive profit and loss statement. For this purpose, some of the feature we use are sumif functions, vlookups, hlookups and if functions. I have heard that Access can be more poweful than excel on many instances. Could you guys share some ideas on what types of access features could nicely complement or replace excel.

Thanks,
Nickel
0
nickel2001
Asked:
nickel2001
  • 5
  • 5
  • 2
  • +1
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Well, Excel and Access are two different programs. You can certainly use Access to automate Excel (or the other way around), but if you have heavy duty calculations or require the use of the functions that you mention (which are specific to Excel) you should leave those in Excel. Excel was designed with number crunching in mind, therefore it's going to do a much better job than Access. Many people build Access databases to store, for example, Customer or Client data and then use Excel to perform financial calculations form those customers or clients.

0
 
nickel2001Author Commented:
Sounds good. This makes sense. Do you know of any examples where storing data in access is better suited than storing data in excel?
My firm does not use access and I am investigating possible usage.
For example: someone told me that he uses access to compare datasets (that should match) and return differences. This would seem a perfect feature for access, but not necessarely a strenght in Excel.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
That's kind of hard to provide. Anything you can store in Excel you can store in Access but, as I mentioned earlier, there are some types of data which might be better stored in Excel than in Access. In general, anytime you need to "persist" different datasets you would be better off using Access.

Let's say you have a Customer database, and you want to store financial information specific to each Customer - perhaps you want to take a "snapshot" of a customer's financial picture on the first of each month. In Excel, you would have to jump through some hoops in order to easily store a particular customer's monthly "snapshot" ... you could certainly use a different workbook for each one, but you would then be hard pressed to compare my account with your account, if you needed to do so.

If, instead, you stored this in Access, you would store the customer info in one table and the financial info in another table. Each "row" of financial info would be "related" back to a specific customer ... and you could have an unlimited (basically) number of rows for each customer. Therefore, for a single record in the Customer table you could have hundreds or thousands of records in the financial table.

This is a very, very simplistic example. A "real" Access application has dozens of tables, forms, reports, etc ... but hopefully this gives you some idea.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Jeffrey CoachmanCommented:
nickel2001,

LSMConsulting, has some "Excel-lent" advice here.

You should also consider the "Structure" of your Excel data. The great thing about Excel is that you can put whatever you want, wherever you want. For example "Price" is in cell A1, "Quantity" is in Cell A2, but "Total" could be in cell B4. Access is not designed to work this way.

In Excel blank cells are treated as Zeros, In Access a Blank is a "Null" (You can do math with a Zero, but not with a Null)

When you import Excel to Access, calculated values come in as static values.

Any Access person can probably tell you a horror story about a request like this : "Hey, I heard Access can do this better than Excel, can I convert my Excel sheet to Access?"

Then you open up the Excel sheet, and they have: Blank Rows and Columns, Subtotals, Merged Cells, Linked Values, Embedded Pivot Tables, Numbers Formatted as text, Text Formatted a numbers and "Strange" date Formats ....ect

Yikes!

I have done this quite a few times. Here are some general rules I use for converting (or Linking) Excel to Access:
1.      Lists (tables) consists of two parts: Fields and Records
2.      Records are what you are keeping track of
3.      Records are in the Rows
4.      Fields are Descriptors or Attributes of each Record
5.      Fields are in the Columns
6.      1 Row, 1 Record
7.      Field Names in Row 1
8.      Field Names should be BOLD
9.      Avoid Blank Cells
10.      Do Not Insert “Blank” Rows or Columns
11.      No Miscellaneous Data or Calculations "floating around"
12.      1 List Per Sheet

Many Excel users just starting out, think they will eventually "Outgrow" Excel, and have to upgrade to Access.
Not so.

Also, the learning curve, for new users, is steeper with Access than with Excel.

I hope this helps
0
 
[ fanpages ]IT Services ConsultantCommented:
Re: Excel vs Access

Both have their own pros & cons, but ultimately the decision to develop in either comes down to:

1) Security Model required (if any)
2) Quantity of data to be stored (rows x columns) rather than actual byte capacities
3) Speed of development (i.e. costs in short term versus long term)
4) Ease of deployment (into user environment)
5) Stability of product
6) Multi-user usage/capabilities
7) Impact (costs) of Downtime due to failure
8) Familiarity with product/development environment
9) Speed of execution
10) User interface

If the speed of execution, stability, storage capabilities and security of your data are all primary concerns, then MS-Access is the route to take, and certainly when MS-Excel worksheets retain data from months/years ago, then archiving these in Access tables would reduce the overall size of the Excel workbook (thus resulting in faster performance).

Also, the multi-user facilities that MS-Access provides far outweigh the "shared" workbook option in MS-Excel (and Access ultimately proves the more stable platform in this respect).

BFN,

fp.
0
 
nickel2001Author Commented:
Guys:

Thanks a lot for all your great answers. I'd like to give you a more concrete example of what we currently do with excel.

We currently use excel for daily reconciliation of Traders’ Profit and Loss. Reconciliations consist of: Investigating and resolving differences between the Trading system and the accounting system

Would access be more efficient than excel to find unmatched records and to find missing data between the two data sources.

How would you set this up in access?
Thanks,
Nickel

0
 
nickel2001Author Commented:
Increasing question point value
0
 
[ fanpages ]IT Services ConsultantCommented:
Hi again,

You may use Excel Database Queries to import data from external data repositories, or use SQL statements to interrogate external data supported by (ActiveX Data Objects / ADO) Connection Strings.

An Excel worksheet can be used as a database, and SQL statements can be applied to retrieve data based on WHERE clause criteria.

In essence, an Access data table & an Excel worksheet are both tabular (row by column) storage mediums.

The advantage of Access over Excel is that Access can store in excess of 65,536 rows of data per 'table' (i.e. per worksheet).

I am presuming that you will probably have more rows of data than this limit over time in an Excel worksheet, so Access is the way forward (out of the two tools you mentioned).

Access would be more efficient in actually querying the data, but there will be an overhead imposed by opening a connection in MS-Excel to the Access database file initially.  (The connection could remain open thereafter, of course, so that subsequent queries do not have the same overhead; it is usual to open database connections when a workbook file is opened, and close them again during the Workbook_Close() event).

If using ADO-based SQL statements on Excel worksheets or Access tables, then the syntax for finding unmatched records & missing data will be the same (as opposed to the Excel VLOOKUP, COUNTIF, and similar lookup functions, if the native Excel worksheet facilities are being used).

The speed of execution will be negligible, to be honest.  The speed of development will be slightly faster in MS-Excel worksheets, but you will be limited to Excel's storage capabilities.

BFN,

fp.

0
 
nickel2001Author Commented:
great answer. Just to make sure I get it, are you saying to continue using excel and exporting all the data from excel into access tables for reconciliation using SQL queries?

Thanks,
Nickel
0
 
[ fanpages ]IT Services ConsultantCommented:
Hi Nickel,

If the quantity of rows of data will exceed 65,536 then, yes, you will need to export data into Access to perform SQL queries.

I'm guessing you will export data daily, weekly, monthly, etc and then remove the current content from your worksheets (to make way for new data) so that the physical row limit is not exceeded.

However, if you will only ever have fewer than 65,537 rows, then you can use SQL statements directly within your Excel worksheets.

For example, if your file is called "c:\P&L.xls" and you have a worksheet called [Sales], then you can use an ADO connection string directly in MS-Excel (within c:\P&L.xls) such as:

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\P&L.xls; Extended Properties=Excel 8.0;"

Your SQL statement would then be of the form:

strSQL = "SELECT * FROM [Sales$]"
or
strSQL = "SELECT * FROM [Sales$SheetLevelNamedRange]"
or
strSQL = "SELECT * FROM [Sales$A1:D10]"

By default the Jet provider assumes that the first row contains field names, if this is not true, then...

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\P&L.xls; Extended Properties=Excel 8.0; HDR=No;"


You could, of course, use VBA in Access to perform this function, i.e. "pulling" data from your MS-Excel worksheet(s), or use Excel to "push" data in Access by writing insert queries based on select statements from your worksheets.


BFN,

fp.

0
 
[ fanpages ]IT Services ConsultantCommented:
PS. XL-Dennis, [ http://www.experts-exchange.com/M_1223902.html ], a contributor to Experts Exchange, has a useful "HOWTO" document on this subject:

http://www.excelkb.com/article.aspx?id=10090&cNode=1I7C3V

Hope this helps.

BFN,

fp.



0
 
nickel2001Author Commented:
Thanks for all your help. Your're answers were great.
I presented some of these ideas to colleagues.
I think we are going to move ahead with this.

Thanks,
Nickel
0
 
[ fanpages ]IT Services ConsultantCommented:
Glad we could help out, Nickel.

Thanks for the points/grading.

Happy codin'.

BFN,

fp.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 5
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now