Results 1 to 10 of 10
Thread: Opinions wanted (Excel 2K and >)

20030423, 03:58 #1
 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 MSExcel as a front end to some MSAccess database tables?
I am thinking, since I know MSExcel and its VBA Object Model, much better than MSAccess and its VBA Object Model, I was thinking that I can write a database application in Exel/VBA and simply store the data in MSAccess tables?
Or should I just dive into MSAccess 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>

20030423, 05:41 #2
 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.jkpads.com
Professional Office Developers Association

20030423, 13:40 #3
 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

20030423, 17:48 #4
 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>

20030423, 17:52 #5
 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>

20030423, 18:14 #6
 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.

20030424, 00:23 #7
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,492
 Thanks
 33
 Thanked 63 Times in 59 Posts
Re: Opinions wanted (Excel 2K and >)
Jan, where specifically does Excel fail in the statistics category?

20030424, 10:26 #8
 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.jkpads.com
Professional Office Developers Association

20030424, 10:27 #9
 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:289301) 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 = (1rsq)*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 nonchallenging 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.jkpads.com
Professional Office Developers Association

20030424, 11:11 #10
 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.