I am analysing the MVSALLEventsCombinedQuery. It is important to remember where the data came from though. Mine looks like this:Īs you can see I now have an analysis of the number of occurences of Census events recorded in my database for the specific variants of Surname that I have selected and showing the years they were recorded in. When you have finished updating the list press the OK button and you will see a new display analysing the data you have selected. Then scroll down and select one or more Surnames that you want to analyse (I am going to pick a couple of variants of the Dawes name in my example, from my database).
![ms access pivot tables ms access pivot tables](http://www.excelconsultant.net/wp-content/uploads/2015/06/pivot-tables-microsoft-excel.png)
Now click on the Surname in the PivotTable Field list and DE-select the “Select All” box at the top of the list of Surnames. You will still have a large number of columns running across the screen – with entries for all the Surnames on your database for which Census events are recorded. This will also mean that the Event Years column on the left will be reduced to years in which you have recorded census events (probably 1841, 1851, 1861 etc). The Pivot Table will be quickly redisplayed showing a summary of Census types only. Click on “Census” and press the OK button. you will see a list of the Fact Types being reported. To make it meaningful we need to put in some selection criteria.Ĭlick on “Fact Type” in the Pivot Table Field List area. Unless your RM database is very very small this will be a very large grid. What this is showing is a count of Events recorded in the RM database for each year for each surname. Your screen display will now look something like this: (but with different dates and surnames according to the contents of your own database) :: Select and drag the “Event Type” field into the Values box. Select and drag the “Event Year” field into theRow Lables box. Select and drag the Surname” field into the Column Lables box. Select and drag the “FactType” field into the Report Filter box. You will now get a screen like this.įor this example complete the following steps:
![ms access pivot tables ms access pivot tables](https://support.content.office.net/en-us/media/faf70629-c923-458b-9863-5349c6e7ea3d.jpg)
Unless you have any reason to do anything special – just accept these options by clicking the OK button.
#MS ACCESS PIVOT TABLES FULL#
You should be presented with a screen like this:Įxcel will have automatically selected the full range of the spreadsheet and suggested creating a new Worksheet for the Pivot Table. You should then see the usual screen of data, semething like this….Ĭlick the ‘Insert’ tab at the top of the screen and then click the ‘Pivot Table’ button on the ribbon. Select the MVSALLEventsCombinedQuery (or the equivalent if you named your tables differently). Then open your Access databse (which in reality is just a collection of Quries that point to the real data in your live RM database).
![ms access pivot tables ms access pivot tables](https://accessdatabasetutorial.com/wp-content/uploads/2017/02/access-crosstab-query-wizard.jpg)
This is a quick guide to show how easy it is to look at the RM data using the Access Queries that we set up in the earlier posts.įirstly – fire up Excel (I am using Excel 2007 here – but the Pivot Table feature has existed in earlier versions of EXCEL for some time). The Pivot Table feature in EXCEL is one of the most useful tools availale to quickly sort and summarise data in any database. If you have followed the guides to enable interrogation of the RootsMagic SQLite database via the Microsoft Access engine ( MS Access Engine and MS Access Engine – Event Query and MS Access Engine – Using EXCEL) then you will also be able to use the Pivot Table feature of EXCEL to analayse your genealogy data.