MS Access Engine – Event Query #msaccess #marriage #sharedevent

Installing the ODBC driver and setting up the link to the RootsMagic SQLite databse is decribed in the the “MS Access Engine” page. This will allow you to look at the contents of your RM database Tables individually. In fact it will also allow amendment of data unless you set the Access tables to be read -only.

Aside from the obvious shortcoming that looking at one Table is not that helpful or exciting it becomes clear that there is another problem in that you will find that you cannot Sort or Select on all the fields in each table – this soon becomes a seriously limiting feature. For example – if you open the “NameTable” you will see that when you click the little sort icon (a triangle in AccessV7) at the top of each column for most columns you will find the sort functions greyed out. So you are allowed to sort on, say, the OwnerID abut not the Surname, given Name and most of the other fields you would want to use!.

In practice you will need to set up Access Queries (which are in effect SQL queries) to bring together the contents of more than one table and, while settign up these queries, we can also redefine the fields so that sorting is possible on all of them.

Initially I wanted to set up a query that produced a data stream of all the event types for an individual. In practice this requires 4 (perhaps 5) different queries:

• the first to generate records for ‘normal’ events
• the second to generate records for where the individual is recorde at a Shared Event (by default as a Witness)
• the third to generate records for all Males who are married
• the fourth to generate records for all Females who are married
• the fifth(optional if you want to generate birth or other events for which an individual event record may not exist (I have nto done this myself).

Subsequently we link the output from all four of these Queires into a single query – and this is what we can use in reporting or to look at the data via Excel or any other external tool which can open and use Access databases.

It is important that the format of the output of each individual query is identical to the others; ie same number of fields with same field names and types and length – this is to ensure that the combines Query holds records of an indentical format – even though they are referring to different types of Events in the life of an individual.

If you go to the Query Builder within Access you can pull together the NameTable, EventTable, FactTypeTable and PlaceTable. As you can see you will need to pull in the PlaceTable twice – the scond time the systemw ill automatically give it an alias name. The reason you need to pull in the PlaceTable twice is that it holds both the Address Details and the Place Details – so you need to point to it from two different fields and Access demands that it called in twice and given different names. When you have pulled them in to the Query you need to set up the links shown in the diagram below: if Access generates automatic links that are different from the ones shown below then just right-click to selec tthem and press the Delete button. It is important you only have the links shown below.

Now below this you have the Access field definition; these are the fields that will be output form the Query; so we only need to select the fields that we want to use – not all of the fields in all the tables. However, as you will probably want to use this query for different sorts of reports it is worth including all the obvious fields so that you dont have to amend it in future.

You could simply double click the fields in each table that you wanted to add to the Query output but this will still leave us with the problem of not being able to sort on the fields. To solve this we create a little function in each field. This little function in effect creates a new field that has been stripped out of the Table. When this is done you will find that it has full sort/filter properties. The format of this function is, for example:

Surname: Left([NameTable]![Surname],50)

The creates a new field called ‘Surname’ but it creates it by taking the first 50 characters from the real Surname field in the NameTable. Now you do not have to strip off 50 characters – you can use less or more; but I found 50 to be generous and I used the same length on most of the fields.

So to summarise – if you the field in the oringal Table can be sorted on then just double click to add it to the Query output; it it does not write the little formula above to create a bummy field. Shown in the two screen shots below are the fields I selected for this view:

Now there are a couple of things you need to note. In the left hand field I created a new field called EventType and forced it, using the formula, to enter an “E” in the field. So every record that comes out of this query has an E in the first field. Each of the other three queries has a different value; “M” for male marriage events.”F” for female marriage events and “W” for Witnessed events (although you may want to use and S to indicate Shared events). This is so that when the output of all foru queries are coming through in a single stream I can see within the report generator how they have been generated. Without this indicator they would all look the same.

The other thing is the definition of the EventYear field. Here I changed the little formula to just pick out the 4 digits of the year from Date in the event table. Now this means that any Day or Month information is stripped off. This I found is good enough for most reporting requirements and means you dont need compicated formulas to cope with when the month and day data is missing (quite often) or when the dates are estimated etc etc. There are other topics on this Wiki which go over the format of the date fields. The formula I use is :

EventYear: Mid([EventTable]![Date],4,4)

Now if you save this query (I called mine MVSEventswithPlaces) and run it it should produce a grid view showing all the Event records in your databse. you should also find that yuo can sort and select/filter on every column.

So you can already start to produce meaningful reports from it; however you are not seeing the events which the person is “Sharing” with someone else, nor will you have any Marriage events. So we need to creat three more identical queries as shown below:

The above I call MVSFamilyEventswithPlacesFather.

The above I call MVSFamilyEventswithPlacesMother. Note it is nearly identical to the Father query so it is best to copy the Father Query and Paste it in under the new name then you only need to change the link betwwen the NameTable to the FamilyTable from the FatherID field to the MotherID field.

The last one above I call MVSWitnessEventswithPlaces.

It is important that you create identical list of fields/types/lengths in the output columns though.

You should be able to save and run each of these in turn and confirm and see the output they create in an Access Grid.

There is now one final thins to do; that is to create an overall Query that brings together the output of all four of these Queries. To do this we need to write a little SQL Statement.

In Access go to the Create Query View but htis time close the select Tabkle dialog and Right Click on the name of the Tab. This will give you an option to select SQL View. Take this option and then copy and paste the following into the page:

SELECT *
FROM MVSEventswithPlaces
UNION ALL
Select *
FROM MVSFamilyEventswithPlacesFather
UNION ALL
SELECT *
FROM MVSFamilyEventswithPlacesMother
UNION ALL SELECT *
FROM MVSWitnessEventswithPlaces;

Then save this Query as MVSALLEventsCombined.

NB – This assumes you have named your Queries the same as I named them – if not – then use the names you decided to use in theSQL statement.

Now if you run the MVSALLEventsCombined Query you will have a grid diaplyed which shows all the four different types of events in a single view. You can sort/select as required.

WARNING – Unless you have turned it off these Queries give you WRITE ACCESS to the RM database. Do not change any data through these Grids unless you understand it and know the consequences of changing it.

Prev: MS Access Engine Next: MS Access Engine – Using EXCEL→

Discussions & comments from Wikispaces site