LO Base 6 on Windows 7
How do I write a query on data that looks like this:
owner family testid matchid name people
Thomas Joseph F175DC9D C3B714B5 Paula 100
Aaron Robert 4099BB0D C3B714B5 Paula 50
Nelson Joseph 68CF0FB9 C3B714B5 Paula 75
Larry Sam 6AE091D1 C3B714B5 Paula 76
Samuel Joseph F175DC9D D3B714B1 Rita 99
Nelson Joseph A8CF0FB9 D3B714B1 Rita 30
Aaron Robert 4099BB0D F3B714B1 Joe 150
Cheryl Joseph 68CF0FB9 F3B714B0 Joe 97
Cherie Robert 4099BB0D f3B714B0 Joe 55
That will group records by matchid where owner and family are different (in the above, all Paulas and Joes)? I want to exclude the Ritas where the owners are different but the family is the same. This is a Firebird embedded DB. There is an ideal additional criteria – I would like the result to be sorted, in descending order, by the number of records that have matching “matchid”.
Yes, SELECT would be better. The results should be:
owner family testid matchid name admin people
Thomas Joseph F175DC9D C3B714B5 Paula 100
Aaron Robert 4099BB0D C3B714B5 Paula 50
Nelson Joseph 68CF0FB9 C3B714B5 Paula 75
Larry Sam 6AE091D1 C3B714B5 Paula 76
Aaron Robert 4099BB0D F3B714B1 Joe 150
Cheryl Joseph 68CF0FB9 F3B714B0 Joe 97
Cherie Robert 4099BB0D f3B714B0 Joe 55
This is a genealogy project where I have combined the lists of DNA matches from 6 people (“owner” and “testid”) from 4 different family branches (“family”). “People” is the number of people each match (“matchid” = “name” + “admin”) has in their online trees. I want to identify the largest number of matchids in the most families (e.g. matchid X has 6 families, matchid Y has 5 families, etc.). I want to eliminate (not see, filter out) those matchids that have only one family in common, e.g.,
owner family testid matchid name admin people
Samuel Joseph F175DC9D D3B714B1 Rita 99
Nelson Joseph A8CF0FB9 D3B714B1 Rita Dick 30
I wasn’t able to do this in Calc, so I’m now trying Base
Sorry for confusing you. I’m struggling myself in trying to explain.
The data is from a DNA service. Each person who submits a test is assigned a unique ID – “testid” . “Name” is the test person’s name that the person has assigned, which is sometimes blank or gibberish. Sometimes a person controls several tests so that there is a test that is administered by someone else. So “testid” is named “name” and administered by “admin”. Only “testid” is going to be unique. “Matchid” is the unique testid of someone who shares DNA with a testid so it’s called “matchid”. For simplicity I had left out “admin” and shouldn’t have mentioned it.
I’ve assigned common names to each testid and then subdivided these common names to family groups – Cheryl belongs to Joseph’s family and Cherie belongs to Robert’s family.The Cheryl and Cherie match Joe. Think that Cheryl is an alias for 68CF0FB9 (testid) , Cherie is an alias for F3B714B0(testid) and Joe is an alias for F3B714B0 (matchid). There will be only one record for Joe to match one owner, but there Joe will match up to 6 owners. I want to find all matchids that match owners from different families, like Joe matches Cheryl from Joseph’s family and Cherie from Robert’s family. In the above Rita matches Samuel from Joseph’s family and Nelson from Joseph’s family. I do NOT want these 2 records included.
As I see it’s a problem of comparing values of family and matchid in different records.
If matchid1 = matchid2 AND family1 does not equal family 2 then show both records. This is the simple one. Matchid could match more than 2 testids say there are 3. Matchid1= matchid2 = matchid3 AND family1= family2 does not equal family3 then display all 3 records. If Matchid1= matchid2 = matchid3 AND family1= family2 =family3 then go to next.
The OpenOffice Forum is me, same problem. They felt it was a DB problem, not a Calc problem.
Sorry that I wasn’t consistent. I think you got the idea. Basically, there’s no way to compare values in sequential records in Calc or Base.
Thank you for your time.
CGH