October 15, 2015 at 12:32 pm
I am interested in feedback from people who are using Access.
I have a little experience, but mainly to port some databases and desktop forms to SQL Server and Visual Studio.
When a student intern here wanted help with her research, she said she needed more functionality than Excel, so they came to me to set up a "database" for her.
I immediately thought Access would be good, so I created the queries to bring her Excel data into (mostly normalized) tables and tried some reports.
The charting tools are limited, and I could not get them to prompt for the date (as in the query they were based on).
The data source for the report seems to be different from the source for the chart?
It seems to be possible with events and forms to make it useful, but so far it seems to be a lot more work than just doing the charts in Excel. It does not work at all like I am used to with Visual Studio.
So my questions: What is Access best at? What are data professionals using it for?
I am using Access 2010. Are later versions more useful?
Should I tell our intern to use something else? (Someone was trying to get her to use R).
Can anyone recommend some good sources for how to do data analysis in Access?
October 15, 2015 at 1:11 pm
I believe Excel is more often used for professional data analysis than MS Access. What functionality is missing from Excel that she finds in MS Access?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 15, 2015 at 2:32 pm
Eric M Russell (10/15/2015)
I believe Excel is more often used for professional data analysis than MS Access. What functionality is missing from Excel that she finds in MS Access?
I am the one who suggested MS Access. Her adviser asked me for advice because she wanted more than what she could do in Excel.
She wants to compare class assessments before and after their Continuing Education class.
I think she wanted to run charts for different subsets of the data (by date?) without having to re-create the chart.
(I like your tagline about about Zork. Many happy hours wasted spent on Apple ][ years ago...)
October 15, 2015 at 2:43 pm
timwell (10/15/2015)
Eric M Russell (10/15/2015)
I believe Excel is more often used for professional data analysis than MS Access. What functionality is missing from Excel that she finds in MS Access?I am the one who suggested MS Access. Her adviser asked me for advice because she wanted more than what she could do in Excel.
She wants to compare class assessments before and after their Continuing Education class.
I think she wanted to run charts for different subsets of the data (by date?) without having to re-create the chart.
(I like your tagline about about Zork. Many happy hours
wastedspent on Apple ][ years ago...)
Everything you mentioned can be done in Excel. For example, if you need data for a chart you can get that from another worksheet or another Excel file.
As a general rule I prefer to use SQL Server over Access for anything data related because SQL server is better at handling data. There is a free version of SQL Server available: SQL Express which is pretty powerful considering the price.
For me, there is really one good use for MS Access: to quickly create a user interface for some non-critical application. If you don't need to quickly throw together some fancy UI then Access is probably not for you. If you don't need to store a ton of information that is accessed and manipulated by multiple parties simultaneously or aren't dealing with 1000's of rows of data then you likely don't need SQL Server. Again - from what you've said - Excel should meet your needs.
-- Itzik Ben-Gan 2001
October 15, 2015 at 2:46 pm
<snark>
timwell (10/15/2015)
So my questions: What is Access best at?
Irritating data professionals.
What are data professionals using it for?
A doorstop
</snark>
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 15, 2015 at 3:30 pm
I have a somewhat different perspective from most of the people that visit here. The fundamental problem with Excel is that it isn't a relational database, and if you need a relational database then you need a different product. Access can do that job nicely if it is a single user arrangement, and it can actually give you pretty good performance. But if you want charts and and graphs, then Excel is a better tool. Most experienced user then export the data necessary to create a chart or graph. On the other hand, if you want paper reports, Access has tools equal to any other product as far as I'm concerned, and the development time is much shorter than many tools.
In my view, Access is the best choice for creating a quick front-end to SQL Server if your users are local, and there are some pretty sophisticated tools available, especially if you use some of the third-party tools. We've been doing exactly that for over 20 years, and have deployed a number of relatively complex systems using the Access front-end / SQL Server back-end approach. On the other hand, if your users are connecting over the Internet, then you won't find Access to be of much help - unless you resort to some form of remote desktop. Also, bear in mind that most of the people who frequent this forum have SQL Sever experience, and tend to view Access as a toy. You might want to post your questions in one of the forums that has much more Access activity - www.utteraccess.com and http://windowssecrets.com/forums/forumdisplay.php/14-Databases are two that I typically recommend. There is only one commonly used desktop database left in the marketplace - even Mac users run the Windows emulator so they can run Access - which says something about the popularity of Access. If only Microsoft would realize that...
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
October 16, 2015 at 6:50 am
timwell (10/15/2015)
Eric M Russell (10/15/2015)
I believe Excel is more often used for professional data analysis than MS Access. What functionality is missing from Excel that she finds in MS Access?I am the one who suggested MS Access. Her adviser asked me for advice because she wanted more than what she could do in Excel.
She wants to compare class assessments before and after their Continuing Education class.
I think she wanted to run charts for different subsets of the data (by date?) without having to re-create the chart.
(I like your tagline about about Zork. Many happy hours
wastedspent on Apple ][ years ago...)
What you just described is like a high school homework assignment. Excel is an excellent tool for charting, this is exactly what it was made for. There are techniques for dynamically changing the range of cells for which the chart is reporting.
Google: +excel +chart +range +change
For example, take a look at this one:
http://excel.tips.net/T002376_Easily_Changing_Chart_Data_Ranges.html
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 16, 2015 at 7:02 am
timwell (10/15/2015)
...So my questions: What is Access best at? What are data professionals using it for?
I am using Access 2010. Are later versions more useful?
Should I tell our intern to use something else? (Someone was trying to get her to use R).
...
MS Access is a RAD (Rapid Application Development) tool enabling a single developer (or just someone with basic PC skills) to create a small scale an end-to-end (database, SQL, forms, reports, etc.) application. You can still use a RDMS like SQL Server as the data source using linked tables / views or stored procedure calls.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply