reporting excel vs access

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

0
Aivars
3/29/2007 7:25:25 PM
access.reports 4434 articles. 0 followers. Follow

5 Replies
702 Views

Similar Articles

[PageSpeed] 33

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.

-- 
Duane Hookom
MS Access MVP


"Aivars" <aivarse@apollo.lv> wrote in message 
news:1175196325.253274.174830@e65g2000hsc.googlegroups.com...
> 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
> 


0
Duane
3/30/2007 4:37:19 AM
On Mar 29, 8:25 pm, "Aivars" <aiva...@apollo.lv> wrote:
> 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.

--


0
Jamie
3/30/2007 9:01:32 AM
Jamie,
You are a guru to me both in Excel and Access:-)

aivars


Jamie Collins wrote:
> On Mar 29, 8:25 pm, "Aivars" <aiva...@apollo.lv> wrote:
> > 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.
>
> --

0
Aivars
3/30/2007 8:00:31 PM
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


Aivars wrote:
> Jamie,
> You are a guru to me both in Excel and Access:-)
>
> aivars
>
>
> Jamie Collins wrote:
> > On Mar 29, 8:25 pm, "Aivars" <aiva...@apollo.lv> wrote:
> > > 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.
> >
> > --

0
Aivars
3/30/2007 8:23:02 PM
On Mar 30, 9:00 pm, "Aivars" <aiva...@apollo.lv> wrote:
> 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

[Quote]

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.

--



0
Jamie
4/2/2007 11:17:31 AM
Reply:

Similar Artilces:

[Access 2007] How to edit custom menubar created in Access2003?
Hello, This is my first post to this server, Hello everyone. We're working on a database created by my collegue in MS Access 2003. Since some time we've moved to MS Access 2007. Now we find problems editing the menubar. Each time we want to remove/add/alter a menu item my collegue goes to his MS Access 2003 and changes a menu. In 2007 the full menubar is visible under Add-Ins ribbon menu. Normally there should be a system table USysRibbon, but it is not there. There are only MSys* objects. How can we change the menubar directly in MS Access 2007? Is that possible at al...

Access 2003 resets form variables during debugging while the form is open
Hello, I am using MS Access 2003. (FWIW, this is about an ADP project, not MDB.) A form has a variable that is initialized (by some function call) in Form_Load() and then stays unchanged for the form's entire lifetime. (In a language more advanced than VBA, it would be a constant, not a variable; unfortunately, in VBA I cannot initialize a constant by non- constant expression). Specifically, in my case it looks like this: Private sTempFileName as String ... Private Sub Form_Load() ... sTempFileName = GenerateTempFileName(...) ... End Sub Priv...

How to link with an Access DB
Hi, I have an Acces DB with many tables. I need to choose the name of a customer in a cell of Excel. For example, in acces I have this tables: Table1 Id Name Last Name City I need to choose the last name from a drop down menu in a spreadsheet and then in other cell I need to put all the data regarding the last name that I choose. I hope to be exaustive, and sorry for my english. :-) Many Thanks Stefano ...

Access 97 can't resize database window
My database window with the listings of forms tables etc was adjusted to a smaller width, but resizing it is completely disabled and renders Access 2007 utterly useless for me. Is there anyway to 'reset' the window? ...

How do I create a sample in Excel?
I am trying to create a sample, needing every 20th record out of around 3000 records. Mary, You need a VBA procedure. Sub AAA() Dim RowNdx As Long Dim DestRng As Range Set DestRng = Worksheets("Sheet2").Range("A1") For RowNdx = 1 To 3000 Step 20 Rows(RowNdx).Copy Destination:=DestRng Set DestRng = DestRng(2, 1) Next RowNdx End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mary" <Mary@discussions.microsoft.com> wrote in message news:ADE0AD7F-B459-41AE-8B2...

Date display in Excel
Format column of cells as Date, display as mm/dd/yy. Date entered into cell, shows up correctly in the text entry field at the top of the screen, but the data on the worksheet displays as "33747", or similar number. Only happening on one workbook. Try tools|options|View tab|uncheck Formulas. Clark wrote: > > Format column of cells as Date, display as mm/dd/yy. Date > entered into cell, shows up correctly in the text entry > field at the top of the screen, but the data on the > worksheet displays as "33747", or similar number. Only > happening on one...

Cannot open Excel attachment from e-mail when Excel is already ope
This is driving me crazy. If I already have Excel open (with or with out a spreadsheet open) I cannot open an Excel spreadsheet attached to an e-mail. If I close Excel and retry it will open Excel and the spreadsheet. Any ideas? I hate having to close all my open spreadsheets when I want to open a spreadsheet that's been e-mailed to me! One of these usually works with a similar problem starting workbooks by double clicking them in Windows Explorer. Maybe one will work for you and your email problem. Tools|Options|General|Ignore other applications (uncheck it) --- or --- Close E...

Excel and some disappeared sheets
Anybody has the same or similar experiences : - I worked with excel file / closed as usually - new day opened and unfortunatelly some very important sheets disappeared / why ? Don=B4t know - I had about 7-9 sheets just now only 3 ones are there but not so important as another ones - the file has original size / I quickly looked into file by normal text editor only for reading and all relevant data are there, however if I open file by Excel, NO original sheets or data are there. - this file is VERY IMPORTANT for me - why happened ? Don=B4t know since I ask anybody for help Thanks in advan...

Excel 97 Win XP calc problem?
I have a machine running office 97 and Win XP. I have seen posts stating it runs fine, but I don't know how man people use spreadsheets that require a hard calculation: Excel will not force a calculation. i.e. ctrl-alt-f9 does not cause full workbook calculation as required. Meaning cells do not update and it doesn't calculate unless you clic in the cell to edit and hit enter. neither does the "Calc Now" button. Auto or manual calculation mode has no effect. Anybody else have this problem -- Message posted from http://www.ExcelForum.com Make sure that both the SR1 a...

users with restricted access
We have some users that we have directed to only get their information from a report that has been set up. Because of that, I set up a parameter query to make the information more easy to see. The parameter query prompts for last name or broker #, is there a way, when the last name is entered to include Jr's & Sr's? Or should this be another field in the table to make the last name field more accurate? ...

office 2000 [excel] cannot open previously OK file. Help
When trying to open a Microsoft Excel Work Sheet that I had accessed modified and saved to only yesterday [Office 2000] a window pops up with: "name.xls cannot be accessed. The file may be resd-only, or you may be trying toaccess a read only location. Or, the server the document is stored on may not be responding." Older files stored on the same mediums ;HD, Flash memory or 3.5in. diskette are accessed with no problem. This is on a network drive? ...

Access 97/2000/2003 comparisons
I have a rather large application that uses an access 97 database (DAO). We also have a version that works with access 2000, Oracle, msde, and SQLServer (ADO). We would like to retire the DAO version of the product, but there are places where the ADO version is much slower compared to where we run DAO seeks (very noticeable when looping). So, I have a few questions. 1. Are there any tips/tricks to speed up ADO queries to compare with DAO seeks? 2. Would there be any benifit in using access 2003 over previous versions of the software besides the added features (xml support, etc). 3...

Using later version of microsoft access
Hi, I've got access 2000 on my computer. When I go to open a database someone sent me I get an error message : this database is in an unrecognized format. The database may have been created with a later version of microsoft access. Is there any way I can open and use this file (short of upgrading to later version of access)? If you do not have Access 2002 or 2003, ask the person to save it in Access 2000 format for you. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at m...

Starting an Office Program (word, excel) in Outlook to send email
I'm taking an Outlook 2002 class. The latest assignment requires I open a Microsoft app in Outlook to send an email. Troubleshooting so far: Have tried to install some addl features from CD, restarted the pc (O/S WINXP) but still get error "Cannot complete action". Get no reason why Outlook cannot perform this action and the HELP hasn't only points me to inserting an object or some variation thereof. My Outlook is configured to use with AOL 9.0. Steps (abbreviated) given in textbook to perform: Actions New mail using Microsoft Office Also tried to perform from Wo...

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

Reporting IRA distributions
I have an IRA account with corresponding cash account. I am trying to get the distribution from the IRA to show up as "Retirement Income" in the Tax-Related Transactions report. How to do it? The distributions are handled as a transfer from the IRA Investment cash acct to a checking account. Thanks for any help. Money 99, BTW. Do I need to upgrade to a later version? ...

Customizing report in MS2005 causes data to vanish
While customizing a Report by Category, I clicked on the Details and Amount tabs and all my data vanished in the report. What'smore I cannot seem to restore the data in the report...? Bug? Or is there a workaround? Thanks. BTW: A feature(s) request: It would be great to be able to get category data grouped quarterly (not just monthly) It would be great to see a horizontal report with categories displayed monthly and quarterly (listed across the top) Finally, sorting a report by perhaps two or three criteria would be great (as I see it, it is only one creterion currently) Thanks ...

no access to send internet email
Hi, Exchange 2003. Can a mail enabled user be restricted to send internal email only and not internet email? Nich Hello, > Exchange 2003. Can a mail enabled user be restricted to send internal email > only and not internet email? yes, that is possible. There are multiple ways to archive this goal. Depents on your infrastructure,too. If you have mailing to external over an SMTP Connector, try this: First look into your SMTP Connector, you will find Delivery Restrictions there. It is self explaining. If you have trouble with that or you won't find it, then i will need additi...

is there a way to program my Excel file to do a loop?
Hi all, If I want B10 to B17 all follow the change of the same number(copy cell), let's say I put it in A1, and C10 follows the change of A2(copy cell), and C11 follows the change of A3(copy cell), and C12 follows the change of A4(copy cell), then I have 4 variables in my calculations: A1, A2, A3, A4. I want to loop each of the variables in a different set, then I hope the whole worksheet will be able to refresh following the change of A1, A2, A3, A4, and then I want to find the very set of A1, A2, A3, A4 that gives the smallest value of D10, how do I program the whole procedure...

Excel 2007 PC damages Mac Excel 2008 Files
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel When I open a Mac Excel 2008 file on Excel 2007 and I can work with the file, however when the file is returned to the Mac, it no longer works correctly, leading to a crash as soon as I try to save or eventually it might change the file to look like a bunch of letters and characters. Here is the error report: <br><br>Microsoft Error Reporting log version: 2.0 <br><br>Error Signature: <br> Exception: EXC_BAD_ACCESS <br> Date/Time: 2010-01-15 17:05:56 -0600 <br> Application N...

Excel. I am having a problem with hidden text
As I type text in some cells, it does not always dispaly if it exceeds the cell length. I wish to keep the cell lenghts for the entire document, but do wish for teh text in that particular row to be displayed. How about if you select that cell, then format|cells|alignment tab|check wrap text And with that row selected format|row|autofit SHR77 wrote: > > As I type text in some cells, it does not always dispaly if it exceeds the > cell length. I wish to keep the cell lenghts for the entire document, but do > wish for teh text in that particular row to be displayed. -- Da...

Excel Grid Lines in Outlook Preview Pane
Is there any way to send a spreadsheet to a mail recipient in the body of the message and not have the grid lines show up in the Outlook preview pane when the recipient views the e-mail? If you open the e-mail in Outlook, the grid lines disappear, but in the preview pane they are there. Using Excel and Outlook 2003. Uzytkownik "Kimberly" <Blackrose73@gmail.com> napisal w wiadomosci news:1143658600.180739.308310@v46g2000cwv.googlegroups.com... > Is there any way to send a spreadsheet to a mail recipient in the body > of the message and not have the grid lines show up ...

Report Can Grow not aligned horizontally
I have a report where I have 1 row of text, memo, number, and date fields. The memo field can contain up to 4-5 lines of wrapped text. I have set all teh rows to "can grow". However, on my report, the conditional formatting doesn't grow. For instance, the fill (which I have set to a gray color) in the memo field is about 3 lines deep when there is a lot of text in the memo field, but the text and number fields associated with that record are actually only 1 line. This essentially looks like a mess, small gray filled boxes for the text fields and a large gray ...

Custom reports #3
Hi, I want to pull a report on how many customers I am getting every hour of the day. Does anyone out there know how to do that? I can try writing a query if someone can tell me which table I can find this information in (timestamp, transaction etc). Any help is appreciated. Regards JD This info is given at the end of the Z report. If you want to use it in a query open the Z report recipt file using the Notepad and see the fields that are used... Report.Hour.HourSales etc... Hope this helps... Carlos "JD" wrote: > Hi, > > I want to pull a report on how many cust...

Excel error 1706
Whenever I try to start excel on my computer it displays an error message and it shuts itself down, any other program from the office pack works properly, except for the one I mentioned. I reinstalled it and it does the exact same thing. Thanks in advance. ...