Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Opinions wanted (Excel 2K and >)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Loungers

    Would someone tell me the advantages vs the disadvantages in using MS-Excel as a front end to some MS-Access database tables?

    I am thinking, since I know MS-Excel and its VBA Object Model, much better than MS-Access and its VBA Object Model, I was thinking that I can write a database application in Exel/VBA and simply store the data in MS-Access tables?

    Or should I just dive into MS-Access and its VBA and learn something new? This project would be for hire, and the client is not sure what to do or which way to go?

    They want some statistical information, regression for example, and I know that Excel does a better job at that vs Access, plus they also want reports, and for that I was thinking that I will need Excel to do that, so hence the question.

    Thanks for any insight.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opinions wanted (Excel 2K and >)

    My thoughts...

    - Excel's statistical functions are at best to be distrusted (but that is of no real help here <g>).
    - If you use Excel as the front end, the users don't necessarily have to have Access on their system, since you can use ADO to exchange data
    - Access has a lot of functionality built in you would have to program in Excel.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opinions wanted (Excel 2K and >)

    Wassim,
    I work more with Excel than Access, and I've used VBA in both. If you already are familiar VBA in Excel, it's not a great jump to VBA in Access. Yes, Access is more dependent on VBA for automation, but many structures are similar, even though the commands and other features are different. The Access Help system is very complete. You should be able to feel your way around.

    Access' report system runs rings around what you can do in Excel.

    Errol

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Opinions wanted (Excel 2K and >)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Jan Karel

    Would you please give me an example of what you mean by: <<< Access has a lot of functionality built in you would have to program in Excel. >>>

    I know that Excel is a spreadsheet application, and Access is a database, I was hoping that I can use the tables in Access and do all the rest, data entry, queries, reports in Excel, JUST store the data in Access because the limits are larger than the 65536 rows Excel has.

    Thanks

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Opinions wanted (Excel 2K and >)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Errol

    I guess I will need to get some confidence and start a project in Access to get me going. This is what I did with Excel, got a project, started knowing just about nothing, and ended up with an OK product. Still don't know a whole lot about Excel mind you <img src=/S/grin.gif border=0 alt=grin width=15 height=15>.

    Now I guess its Access Time. Any neat tutorials you can share with me to?

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opinions wanted (Excel 2K and >)

    Wassim,
    I guess I'm a bit like you - I start with "there's got to be an easier way", then start poking around Excel/Access/VBA to find it. The basic look and feel that you learn in Excel VBA will help you along with Access. But for Access, you will need to understand forms more than you need for Excel. (When I figured forms out, I started using them in Excel, too!)

    I have Woody's Using Office 2000, which has a good tutorial chapter about VBA in most Office programs. My favorite is VB & VBA in a Nutshell from O'Reilly. It's more like a dictionary than a tutorial, but it's really complete for describing general VBA commands and functions.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Opinions wanted (Excel 2K and >)

    Jan, where specifically does Excel fail in the statistics category?

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opinions wanted (Excel 2K and >)

    There are many things I could mention, but I don't know Access that good. Things that come to mind are things like input validation, input masks.
    Also buidling queries in Access (especially ones that require parameters) is simpler than coding them in XL.

    ALso, having to create userforms (in XL) to input data that subsequently has to go to Access is quite an ordeal compared to doing this in Access directly.

    Maybe HansV can chip in some more?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opinions wanted (Excel 2K and >)

    Here is a post from the Newsgroups I kept for reference:

    http://groups.google.com/groups?hl=en&selm...05@mediaone.net

    LINEST() (also SLOPE(), INTERCEPT(), VAR(), STDEV(), LOGEST(), TREND(),
    FORECAST(), etc.) uses a numerically unstable algorithm. With
    challenging data (such as yours), rounding error has accumulated to the
    point that none of its calculations (slope, intercept, etc.) can be
    believed. In your case, you were lucky enough to get an impossible R^2,
    so that it was obvious that there was a problem. There may still be a
    problem even with data that give more reasonable R^2 values. These
    problems with Excel's algorithms have been well documented for years
    (cf. Sawitzki, 1994, "Report on the reliability of data analysis
    systems" Comput. Statist. Data Anal. 18:289-301) yet Microsoft continues
    to ignore them.

    Harlan Grove's matrix formulation simply recreates the same problem.

    DEVSQ(), COVAR(), and CORREL() are the only 2nd moment functions in
    Excel that are numerically reliable. For simple linear regression, use
    the following formulas instead of LINEST(), SLOPE(), INTERCEPT(), RSQ(),
    STEYX(), etc.

    slope = COVAR(y,x)/DEVSQ(x)*COUNT(y)
    intercept = AVERAGE(y) - slope*AVERAGE(x)
    rsq = CORREL(y,x)^2
    SSreg = rsq*DEVSQ(y)
    SSresid = (1-rsq)*DEVSQ(y)
    df = COUNT(y)-2
    F = SSreg/SSresid*df
    steyx = SQRT(SSresid/df)
    se1 = steyx/SQRT(DEVSQ(x))
    seb = steyx*SQRT(1/COUNT(y)+AVERAGE(x)^2/DEVSQ(x))

    This approach has the added advantage over LINEST that it allows missing
    values in the data range. However that cuts both ways, because they
    will give a wrong answer if there are data pairs where only x or y (but
    not both) are missing.

    Similarly, for univariate statistics use the following formulas instead
    of VAR(), VARP(), STDEV(), and STDEVP()

    var = DEVSQ(x)/(COUNT(x)-1)
    varp = DEVSQ(x)/COUNT(x)
    stdev = SQRT(var)
    stdevp = SQRT(varp)

    Since Microsoft has already programmed routines that would be superior
    to their unstable routines, it is puzzling why they continue to maintin
    redundant inferior code. The unstable formulas that Excel programed are
    mathematically exact (with infinite precision), so my formulas will
    agree with the Excel functions for non-challenging data sets. When they
    disagree, the dedicated Excel functions are wrong.

    There is no DEVSQA function, there is no help for VARA(), VARPA(),
    STDEVA(), or STDEVPA() other than doing those calculations manually.

    If you are wedded to using LINEST(), then test to see if

    STDEV(x) = SQRT(DEVSQ(x)/COUNT(x))
    STDEV(y) = SQRT(DEVSQ(y)/COUNT(y))
    PEARSON(y,x) = CORREL(y,x)

    If all three of these are approximately true (say to at least 12 figures
    each), then LINEST() can probably be believed for simple linear
    regression. Figuring out when LINEST() can be believed for more complex
    models is not so simple.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opinions wanted (Excel 2K and >)

    Access comes with a variety of Wizards that will at least give you something to begin with. For instance, the Report Wizard lets you create a report with grouping levels, subtotals etc. by answering a few simple questions. The end result usually needs tweaking, but the Wizard does a lot of the hard work for you. There are also Wizards for designing queries and forms, and for creating combo boxes on forms, etc.

    Access has very little in the way of statistical analysis - functions such as Average and Standard Deviation are built in, but that's about it. Excel has more features, but as mentioned by Jan Karel, they are not very dependable. If your client really needs statistical analyses, consider one of the "big shot" statistical applications (SAS, ...). All that I know of can read Excel and Access data.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •