Spreadsheets vs Databases
By far the most popular
article on the I Get It! Development website is the one concerning the
differences between databases
and spreadsheets. Not only has it been accessed more times than any of our
other articles, it has generated the most email; from places as far away as
Kazakhstan and as near to us in the Silicon Valley as Cupertino.
Not
everyone in my group knows Access and we don’t want to have to learn.
Unlike Excel,
Access databases are rarely interacted with ‘in the raw’. Most users of a
database do not know how to use Access or any other database.
A developer like I Get It! Development creates an application that uses an Access or SQL Server
database behind the scenes. A typical user clicks on a desktop shortcut and
is presented with a menu of items like Input Transactions, Edit Employee
Data, View Spending Reports By Department, etc.
Two groups accessing the same backend database can even have
different front-end interfaces. For instance, one group may have only input
forms (to enter transactional data) and one group has only reports (to view
consolidated period-end results). If you stop and think about it, you
probably already use SQL Server, Oracle, Sybase or another commercial
database in your daily work like the general ledger or the HR database—but
you access it through a professionally written application that hides the
database tables and queries from you as it should!
I know a little bit about queries but I don’t want to have to keep writing them all
the time.
A common misconception about queries is that they represent results—like an
Excel spreadsheet.
Queries are not results, they are instructions! Once a query is written, you never
have to alter it again. Imagine that you want to see the
total transactions for a given department quarter-to-date. You write a query
that presents all of the information you need and you include a criteria
that uses the =Date() function. Each time the query runs it goes through the
same set of instructions, one of which is “get the current date from the
Date() function.” On any given day, you will get different results based on
the latest data that has been loaded into the database.
Are You Ready For Office 365?
Office 365 is
- Email and calendaring powered by Microsoft Exchange
- Office Web Apps - the office productivity apps you already know, tailored for the
web
- Websites powered by Microsoft SharePoint both for internal-facing intranet and customer-facing
website
- Instant Messaging and Online Meetings powered by Microsoft Lync
- All with a financially-backed 99.9% uptime guarantee
Sign up for a free trial:
Your information will not be shared with any other party. You must provide
a valid email in order to recieve your login information and starting information
kit.
Watch
this video to see how Office 365 can help your business.
In Access, other
people who don’t know anything about queries can also run your queries with
a button click! Again, stop and think about your other interactions with
databases such as the ones that run the ATM network. Each time you request
your balance a set of instructions is run: Look at the balance from the last
statement, add all the deposits since then, subtract all the withdrawals
since then, display the total. Same query every time, very different
results.
The manager/executives I report to only like Excel!
No problem at all! The data should be stored in a database, that’s definitely true. For
data integrity and data security a spreadsheet is simply not an option. But
for PivotTable reports, working out what-if scenarios, creating graphs and
charts, or doing period-over-period comparisons of course you’re going to
need a spreadsheet. All Access queries and reports can be exported to Excel
with the click of a button. Each object has a Tools > Office Links > Analyze
it in Excel button that instantly moves Access data cleanly into Excel.
Don’t settle on one tool, use each the way it was designed: store data in a
database, work on it in a worksheet.
|
|