reporting excel vs access

[Aivars]

Hello,
On this web site I found the following article:
http://www.excel-vba.com/article-excel-access.htm
I have recently made a move from Excel to Access and in a way I kind
of agree with Peter. And I just wonder - there is no doubt I have have
more control over data in Access than I had in Excel. But reporting...
well, it might be I am not yet experienced enough with Access
reporting possibilities but I really still find reporting (printed
reports) easier done in Excel.
i usually write an ADO function to retrieve the data from Access
database and just put this function into any cell in excel cell and
it's done! I can put this function in any cell, not rigid report
boundaries of Access.
Please this is not a rant against Access reporting tools - when I
discovered the power of SQL, I decided I will never move back to Excel
(in spite of powerful SUMPRODUCT and INDEX/MATCH functions which i
know very well).
I am just interested what Access Gurus think of this article and
wether Access reports can beat Excel free-form reporting.

I am using Excel+Access 2007


Thanks
Aivars

 

[Duane Hookom]

SUMPRODUCT looks very much like a crosstab query. INDEX/MATCH seems like a
very kludgy way of mimicking relation databases/queries which Access does
with drag and drop ease.

Excel does a nice job with crunching and reporting numbers. I like the
pivots and graphs. However, try reporting relational data with Excel. Access
is so much better a sorting, grouping, filtering, and formatting. You can
right code in Access in the On Format or On Print events of a report
section. With Access, not everything has to fit in a grid.

Access and Excel are different tools for different requirements. They
sometimes provide solutions for the same requirements but you can't say that
one is better than the other in all or even most cases. Take a look at the
sample calendar reports at http://www.access.hookom.net/Samples.htm (there
are screen shots) and see if you can create any of these with about a dozen
lines of code or less.

You may be comfortable creating an ADO function but I would expect that
better than 90% of the readers in this news group have never written any
code in Excel and maybe don't know ADO from DAO from YODA.

Not being able to share an Excel file simultaneously is a huge drawback.

 

[Jamie Collins]

I just wonder - there is no doubt I have have
more control over data in Access than I had in Excel. But reporting...
well, it might be I am not yet experienced enough with Access
reporting possibilities but I really still find reporting (printed
reports) easier done in Excel.

Do you mean as from the perspective of report designer or of report
consumer? For a designer, the Access report writer is fine (for me,
probably the best part of the Access UI) but from an end user's
perspective they are frustratingly 'fixed' e.g. can't change the order
of columns of data. You can always export the results to Excel,
though ;-)

I am just interested what Access Gurus think

Oh sorry, disregard my comments, then <g>.

Jamie.

--

 

[Aivars]

Jamie,
You are a guru to me both in Excel and Access

aivars

 

[Aivars]

OK, here it goes.
I am preparing consolidated financial accounts for two companies.
Mother + 100% daughter. All data manipulations I do in Access with
queries (deals between companies - loans, income, expenses, currency
differences etc). I have made a number of queries for each of these
things.
Then in EXCEL I just write a couple ADO functions (with arguments)
selecting all necessary data from these queries ( can do free SQL code
in EXCEL ADo function too), connect these functions to free cells in
Excel, format and I am done!
Unfortunately, I was not able to produce the same in Access reports at
the same time - it was easy in Excel.
But, I repeat, I am not Access experienced enough.


aivars

 

[Jamie Collins]

Jamie,
You are a guru to me both in Excel and Access

Thank you: I've never been described as an Access guru before! I can
retire now.

Hang on a minute...

http://www.mvps.org/access/netiquette.htm

Netiquette

Look for Smileys , grins <g>, and other "Emoticons".
When you see one, the preceding statement was not meant to be really
serious, don't take it as such.

[Unquote]

Rats <g>!

Jamie.

--