Move to Access?

[Dan Wilson]

Good day. I have been using Excel 2002 and other versions
of Excel prior to that for many years. I feel that I have
become rather proficient in the daily use of Excel.

Lately, my needs have been causing me some page formatting
problems. Specifically, the ability to have different
cell widths on the same worksheet. In order to accomplish
this, I have been merging cells where needed.

Is it time to move on to Access? Does Access allow a more
convenient "data form" setup? Will my programming
knowledge of formulas in Excel be usable in Access?

Thanks, Danno...

 

[JulieD]

Hi Dan

Access is very good at the following things:
1) dealing with relational data ie one person attends more than one training
course and each training course can have more than one person on it)
2) dealing with more than 650000 records
3) providing filtered "answers"
4) formatting of reports (to some degree)
however 1 & 3 depend on the database designer creating a good structure.

As for the "data entry" side of it - using Access is is basically like
creating all your data entry forms as userforms in VBA ...
so if the main reason for moving to Access was layout for data entry then i
probably would create userforms in VBA and use those .. if the main reason
was layout for reporting - Access isn't a bad option, but i would firstly
investigate mail merge with word type options as IMHO the learning curve for
these options is shorter than the learning curve for Access.

as for working with formulas - there's similarities and differences - ie IF
in excel is IIF in access ... a greater requirement for Access would be vba
skills as (again IMHO) you can't do very much in Access without using code
.... oh, and there's no macro recorder option ... you can create macros but
its not as easily in excel.

my 2c worth.

Cheers
JulieD

 

[Harald Staff]

Hi Danno

My 0.02:
"Data" -as in any form of information, should be inputted in the best way
for input, stored in the best way of storage, and read/reported/exported the
best way for reading/reporting/exporting. Which may be in one single screen
layout, but usually it's not.

In spreadsheets things are often written where they are to be read
afterwards, and so this is where data is stored too. It does not have to be
this way, systems dealing with larger amounts split this between entry
forms, storage tables and report sheets. Excel can do this nicely, so can
Access.

Access is often recommended because it can handle big amounts of data and do
it fast. This is true, but the real difference between a spreadsheet and a
database is "relations" and "multiple linked tables". Which usually is a
very good thing when you deal with data. Have a look at this:
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html
http://www.databasejournal.com/sqletc/article.php/1469521

So yes, it's time to move to Access. Not primarily because the reports in
question will look better, but because you will have tons of fun setting it
up and learn really valuable things.

HTH. Best wishes Harald

 

[Duke Carey]

Dan -

My $.02 worth is that if you need a database, use a database, whether it's
Access, MSDE, or SQL Server.

If you need a spreadsheet, use Excel.

Access will generate dumb user-forms for you that allow you to
see/edit/insert a single row of data at a time. It also allows you to query
tables to get back subsets or you data, or summarized sets of your data.
And, in some cases, allows for 'better' reports than Excel. However, I think
the learning curve for Access reporting is longer than it should be. If you
need to create presentation quality output, you'll spend LOTS OF TIME futzing
with formatting of reports

You can use Excel to directl query an Access/MSDE/SQL data store and pull
filtered or summarized data directly into Excel, where you can more quickly
(IMHO) generate presentation caliber reports.

Bottom line: figure out the features you need, fiddle a little with Access,
and make your own informed choice.

Duke