Unanswered: crosstab (pivot) Tables in Excel vs. Access?
I have been using Access 2000 and will be upgrading to Access 2002 soon. I've heard that Access 2002 has improved options for pivot tables (crosstabs). I am interested in moving several that I have been doing in Crystal Reports into Access or Excel (it's all via ODBC to SQL and Access tables).
One of the simple things that Access 2000 won't do is allow > 1 field in the detail: So if you want a column for each month and a row for each employee and you want 2 numbers in the detail -- say number of phone calls and total minutes, Acess 2000 can't do it. Also you have to build pass through queries thus ending up with 2-3 queries each time you want to throw together an adhoc crosstab in Access 2000. (I do not know much VB much at all, so am not ready to explore what it may offer in this area due to too many pressing deadlines).
I am interestd in any feedback as to the capabiliites and limitations of Access (v. 2002) vs. Excel (v 2000). Some of what I do are routine--every day, month or quarter, but there is also a lot of adhoc analysis where I have to throw together a pivot table in short order. That is one nice thing Crystal Reports does. However, it is difficult to change the underlying tables if you want to re-use work already done (when managers see the first result they ask more questions....), and also fonts and formatting is a hassle in Crystal. Also we have fewer Crystal licenses and there is a lot less familiarity among coworkers with Crystal. Often what I do as adhoc after revision ends up being passed on to someone else to handle routinely, so this is an important consideration.