Pivot tables

[Daryl2106]

HI,



I am trying to use a pivot table to display attendance data by date (rows) and courses (columns). I would like to be able to add additional calculated fields and don't quite know how to proceed. Does any one know of a good resource for beginners to learn more about pivot tables in Access. There seems to be lots of info on pivot tables in Excel but little for Access.

Take care,

Daryl

[alansidman]

You have more flexibility using Excel for pivot tables.  Why not export your query directly to excel and do your pivot calculations there?  Somethings are just easier in Excel and Pivot tables are one of them.

Here is a one hour tutorial on excel pivot tables by Mike Alexander that will probably cover what you want to do. It is very extensive and informative.

http://www.datapigtechnologies.com/W...tTableTips.htm

[June7]

Be aware, Microsoft removed Pivot Table/Chart views from Access 2013.

Yes, pivot tables and charts are easier in Excel but much can be replicated in Access. Unfortunately, I've never found a single authoritative reference and guidance on charting in Access is rare. This is a nice introductory article http://vb123.com.au/toolbox/09_acces...07charting.htm

[Daryl2106]

Thanks June et. al.,

Before I embark on this challenge I am still trying to determine the best way to proceed. What I need is a way present (as an example) a year's (about 16 records;biweekly) attendance for a student in each of up to 8 courses. There are two important columns in each record: ClassesAttended and ClassesOffered. I`ll need to calculate biweekly averages as well as course averages over a given period. It would be nice to see the raw data as well as trends (graph?). Reports...pivot tables...pivot charts...graphs...I am hoping to choose the best one before I get going rather than trial and error.

As always thanks for your help,

Daryl

[June7]

Don't know enough about your db to be definitive. I only know I have a lot of graphs in my project. These graphs were originally done in Excel but I wanted to eliminate Excel as a component so I replicated with Access. They all also use VBA code for formatting. In one case, what would have been a single graph in Excel required 4 layered graphs in Access. None are pivot charts, all use chart control.

[Daryl2106]

Hi June,

Thanks for the reply. I know very little VBA (just starting to learn) so I think I will need to look at other options, either a report or pivot table or excel export. I have attached a screen capture of a pivot table with the data I need to display. All I am missing is averages for each course (for the attendance dates provided) and for each attendance period (for the courses provided). Thanks for your direction to date.

TC,

Daryl

[June7]

I've never had need for pivot chart/table but I can see their applicability for your data output. Replicating the table you posted might be possible with crosstab queries.

Along with Grand Total under each column, you want Average? Average is not an aggregate calc option in pivot table?