Hints for Queries by Simon Orde
Posted: 20 Jan 2003 12:03
Queries sometimes seem complicated but they are actually much simpler than they may appear. It helps if you understand a few key points:
1. Every row in a query result set (what you see in the Results tab of the Query Window) corresponds to a single record. No record ever gets more than one row.
2. All records (row) are of the same type (Individual, Family, Source, or whatever). You don't get any queries that return a combination of record types. The record type is specified when the query is created (if you click on 'New Custom Query', you will be asked to specify the type).
3. You can display whatever information you want about the record. You can even display information that is not part of the record itself, but is linked to it. For example, in an Individual Query, you could display information about each Individual's Mother-In-Law by their 3rd marriage (if they have one).
4. You don't have to display a field that represents the record itself that you are looking at (but it may help to make things clearer if you do). The field (or 'tag') that represents the record is the first field (the topmost or 'root' field in the hierarchy) in the list shown on the left in the Columns window. For Individuals, the expression show for an Individual record is 'INDI'.
From this you can see that, as things are, you could never get a query that shows different information about a person in different rows (e.g. each of their census events in a separate row). The best solution (as suggested) is to put all the census events into the same row by adding a number of columns for Census events, with increasing instance numbers.
In release 2.1, there will be a Census report, which will list census data for each person (and you could use a query to select which Individuals you wanted to look at, if you wanted to). These will be listed vertically (ie. in a normal report layout). You will be able to customize this report to include Custom events if you use custom census events.
One option which I have considered (and which will definitely not happen in 2.1, if it happens at all), would be to create a new type of query that returned information not about records as such, but about events (or attributes) - although you would still have to be able to be able to display information about the people to whom the event or attribute occurred. In this case, you would get (at most) one row for each Individual's events and attributes - but of course you would still be able to provide filters to specify exactly which events you were interested in. If I did this, you would probably be able to get the Result Set you want - with one row for each person's census events. However, to repeat, this is an idea for the future, and not currently scheduled for any release. It _might_ make it into 2.2 if there is one (which there probably will be). If there is a 2.2, it too will be a free downloadable upgrade.
1. Every row in a query result set (what you see in the Results tab of the Query Window) corresponds to a single record. No record ever gets more than one row.
2. All records (row) are of the same type (Individual, Family, Source, or whatever). You don't get any queries that return a combination of record types. The record type is specified when the query is created (if you click on 'New Custom Query', you will be asked to specify the type).
3. You can display whatever information you want about the record. You can even display information that is not part of the record itself, but is linked to it. For example, in an Individual Query, you could display information about each Individual's Mother-In-Law by their 3rd marriage (if they have one).
4. You don't have to display a field that represents the record itself that you are looking at (but it may help to make things clearer if you do). The field (or 'tag') that represents the record is the first field (the topmost or 'root' field in the hierarchy) in the list shown on the left in the Columns window. For Individuals, the expression show for an Individual record is 'INDI'.
From this you can see that, as things are, you could never get a query that shows different information about a person in different rows (e.g. each of their census events in a separate row). The best solution (as suggested) is to put all the census events into the same row by adding a number of columns for Census events, with increasing instance numbers.
In release 2.1, there will be a Census report, which will list census data for each person (and you could use a query to select which Individuals you wanted to look at, if you wanted to). These will be listed vertically (ie. in a normal report layout). You will be able to customize this report to include Custom events if you use custom census events.
One option which I have considered (and which will definitely not happen in 2.1, if it happens at all), would be to create a new type of query that returned information not about records as such, but about events (or attributes) - although you would still have to be able to be able to display information about the people to whom the event or attribute occurred. In this case, you would get (at most) one row for each Individual's events and attributes - but of course you would still be able to provide filters to specify exactly which events you were interested in. If I did this, you would probably be able to get the Result Set you want - with one row for each person's census events. However, to repeat, this is an idea for the future, and not currently scheduled for any release. It _might_ make it into 2.2 if there is one (which there probably will be). If there is a 2.2, it too will be a free downloadable upgrade.