New query incomplete census's

jsphillips · Post by **jsphillips** » 15 Nov 2011 08:00

Could some kind soul help me to write a new query for Census data.
We already have many such queries, but I would like a query to ONLY show the missing data. That is if they qualify they should only show the missing year(s).
To design there would be across the columns :-
Date..Name...Birth date.. Marriage Date...Death date...missing year(s).
This would prevent names coming up that do not qualify at all and names where the census's are complete.
This would then equate to a hit list to complete and contain significantly less names and paper usage !!
Thanks hopefully
John[smile]

ID:5562

Post by **tatewise** » 15 Nov 2011 09:29

Have you tried the Knowledge Base where in FHUG Downloads you will find Query:Individual ~ Kinsfolk Census Queries at
http://www.fhug.org.uk/wiki/doku.php?id ... us_queries
The Kinsfolk Census To Do Table only lists Individuals who were alive at any UK Census and are missing at least one Census Fact.
As you will see from the screenshot, it has a column for each Census Year that contains n/a for not alive, or the Date of a Census Fact, or left blank for a missing Census Fact.

jsphillips · Post by **jsphillips** » 16 Nov 2011 10:56

Fine thanks Tatewise
Yes I have seen, but I need to alter it so that the N/a and their related names do not appear and I do not need to see the years that have been completed.
The list required will only show names and years that qualify for 'missing'. This produces just a 'hit list'
Is there a way of
modifying the Kinsfolk list please ??
Thanks
John

Post by **tatewise** » 16 Nov 2011 11:26

If you look carefully, you should see that the Kinsfolk Census To Do Table does only show Names that qualify for 'missing' Census years, and therefore is just a 'hit list'.

In the Result Set for each Individual row there will always be at least one blank cell under a Census Year column.
It is these blank cells that are your required To Do 'hit list'.

Individuals are NOT listed if every cell on their row is either n/a or has a Census Event.

jsphillips · Post by **jsphillips** » 17 Nov 2011 13:30

Yes thanks
I have printed it out but it does NOT omit the fully complete lines in the following circumstances
For someone who was born after 1901 but previous to 1911 it lists all census from 1841 to 1901 as 'n/a' and it lists the 1911 as '1911' because I have that data.
Therefore it is complete as we do not have access to 1921 census.
Accordingly no further census data available and therefore not required on this hot list.
How do I adapt so that all lines when columns when complete as above do not show up ??
I will send you my report if you wish showing this
Thanks
John

jsphillips · Post by **jsphillips** » 17 Nov 2011 13:36

Sorry but your last comment replied if all events in cells are n/a or has an census event.This is not correct in my case. I can send a typical line showing the relevant B M D data and the result therefrom. It only appears as in my previous email. Could it be that it is only referable in the case aforementioned.
Thanks again.
John

PeterR · Post by **PeterR** » 17 Nov 2011 14:31

I suspect that '1911' may be appearing in the 1911 Census column because '1911' has been recorded as the date of the census event for that individual. The superfluous row should be excluded if the recorded census date was '2 APR 1911'.

Post by **tatewise** » 17 Nov 2011 20:17

Peter is absolutely correct, and my earlier explanation was incomplete.
The To Do Query checks that the Date recorded against each Census Year Event is the exact date of the Census.
So if your 1911 Census Event is complete, its Date should be 2 Apr 1911, then that Individual will be excluded from the list.

The To Do Query allows you to enter a partial date (such as just the year) to indicate that only partial information has been recorded.
One scenario could be that you've found a probable Census Record online for that year, but have not downloaded the Census Image and used Ancestral Sources to record all the details.
Thus the Individual is retained on the To Do list until all is complete, and the partial date shows partial data has been found.

jsphillips · Post by **jsphillips** » 18 Nov 2011 09:27

Thank you PeterR and Tatewise
For all census events I have recorded the datas '1911' or '1901' or '1891' etc.
I was not aware that I had to record the exact census date for the query to work.
There are obviously 2 ways for me to make this work
(1) alter ALL census dates to actual dates
(2) to alter query so that a plain year date would work.

Since I have some 1300 relations to change on census's this will be some lenghty task !!
Is there some way of altering the query so that it would work with whole years ??
I accept if not I will have to go with option 1.
Thanks again for a quick and detailed response.
What a great program and wonderful support.
I am trying to get U3A members to buy into this program it is a pity that there is not a cheap cut down version for say a 'tenner' which would only hold say 30 persons and then an upgrade price to full version
Best regards
John

jsphillips · Post by **jsphillips** » 18 Nov 2011 09:35

Sorry mised off the bit about downloading image. I download the data from find my past only in summary form not the actual image. I do not intend to use Ancestral Sources!!
I scan all summary sheets as jpg images into a folder on my pictures folder as Census copies with an appropiate name as 'L Corrick census 1901'
Where I have downloaded the actual image (not Summary) I scan this into a separate folder.
All actual BMD certificates are stored in a special folder scanned and then stored on computer under various folders names.
Surely I do not have to use AS for the query to work !!
Thanks again
John

jsphillips · Post by **jsphillips** » 18 Nov 2011 09:51

I have now entered on 1 person(as a trial) the date (the only allowable column) date born say 1906 died 1912. all n/a appears and under 1911 census column 2 Apr 1911. This still shows as to do !! do I have to enter source data as well?? I use the census fact,
the date, age, and place. I do not use source or citation details as I have these on my scan files. If all of this does not allow the query to work as it is incomplete can you tell me how to add lines which will allow the query to not show the individual if all census boxes, partial dates and address shown.
Thanks
John

jsphillips · Post by **jsphillips** » 18 Nov 2011 09:55

I apologise most profusely ignore my last email, it appears I had not refreshed the data. Now when I enter correct actualk date of census it all disapears correctly.
John

Post by **nsw** » 18 Nov 2011 18:35

You don't have to use AS at all, however just to clarify that AS doesn't require you to link images. But it does make it much faster to enter your census data than entering into directly into FH. That's why so many people use it!

gerrynuk · Post by **gerrynuk** » 18 Nov 2011 21:28

jsphillips said:

Surely I do not have to use AS for the query to work !!

AS is a standalone program which is invaluable for entering Census data and is a breeze to use. Once the data is entered, however, it takes no part in creating and running queries.

Although you have many census entries already entered it would be worthwhile entering all new census data with AS and, if you have the time later, re-entering the old data with AS. That way there will be a completely consistent recording and presentation of the data.

Don't forget that AS now also caters for Baptisms and we have been promised a new version that will add Marriages as well, with the promise of Burials and possibly more at a future date.

(Just for the record I have no financial or family interest in Nick Walker's program but am just a very satisfied user.)

Gerry

Post by **tatewise** » 18 Nov 2011 21:44

The reason I chose to use specific UK Census Dates is to differentiate partial Census Events research from completed Census Events entries.
Also it avoids any confusion with Census Events from other countries.
Furthermore, Ancestral Sources automatically fills in the specific Census Dates, and I strongly recommend you give AS a try.

If you prefer to stick with Census Event Dates that are always just the year then alter each Rows Filter as follows.
On each row, replace the last DateAt(yyyy,m,d) with DateAt(yyyy,0,0)
e.g. DateAt(1841,6,6) becomes DateAt(1841,0,0)
and DateAt(1911,4,2) becomes DateAt(1911,0,0)

If you want to allow Census Event Dates to be any date within the Census Year then make the following change.
In each Row Filter, replace the last (%INDI.CENS[year=yyyy].DATE% = DateAt(yyyy,m,d)) with Exists(%INDI.CENS[year=yyyy].DATE%)
e.g. (%INDI.CENS[year=1841].DATE% = DateAt(1841,6,6)) becomes Exists(%INDI.CENS[year=1841].DATE%)
and (%INDI.CENS[year=1911].DATE% = DateAt(1911,4,2)) becomes Exists(%INDI.CENS[year=1911].DATE%)

jsphillips · Post by **jsphillips** » 18 Nov 2011 22:20

Thank you all for your invaluable help
I have decided not to use AS but to record the correct full Census Date ie for 1901 ..31st March 1901.
What a great wealth of help available.
Thank you again Tatewise I will set up and save another custom query by altering the original Kinsfolk to do and setting up as 'whole year entry' as a 'standby' in case of new U3A members that might wish to use the whole year entry.

Family Historian User Group