Finding Family Relationships in Access genealogy dbase

Moderator: MOD_nyhetsgrupper

Svar
Ray Murphy

Finding Family Relationships in Access genealogy dbase

Legg inn av Ray Murphy » 24. november 2006 kl. 6.54

Hi All,

I've converted a gedcom file to an MS Access database via the GED2CSV
program and got some good things happening with it, but I haven't got a clue
how to connect people with relatives. For example, I can see in the
'Relations table' where married couples are 'connected', but I can see no
connections between children and parents, and grandparents etc.

Any ideas?

Thanks

Ray

Dave Mayall

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Dave Mayall » 24. november 2006 kl. 17.18

"Ray Murphy" <raymur@chariot.net.au> wrote in message
news:4566888c$1_2@news.chariot.net.au...
Hi All,

I've converted a gedcom file to an MS Access database via the GED2CSV
program and got some good things happening with it, but I haven't got a
clue how to connect people with relatives. For example, I can see in the
'Relations table' where married couples are 'connected', but I can see no
connections between children and parents, and grandparents etc.

Any ideas?

Yes.

Use something designed for the job!!

Ray Murphy

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Ray Murphy » 24. november 2006 kl. 18.23

"Dave Mayall" <dave@research-group.co.uk> wrote in message
news:4sokdoFv90dlU1@mid.individual.net...
"Ray Murphy" <raymur@chariot.net.au> wrote in message
news:4566888c$1_2@news.chariot.net.au...
Hi All,

I've converted a gedcom file to an MS Access database via the GED2CSV
program and got some good things happening with it, but I haven't got a
clue how to connect people with relatives. For example, I can see in the
'Relations table' where married couples are 'connected', but I can see no
connections between children and parents, and grandparents etc.

Any ideas?

Yes.

Use something designed for the job!!

RM: Presumably you mean a genealogy program, but they are not designed to
query and display any data in my database - such as displaying a table
containing the basic data for all children and great grand parents in
consecutive lines.

Ray

Charlie Hoffpauir

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Charlie Hoffpauir » 24. november 2006 kl. 19.08

On Sat, 25 Nov 2006 03:51:13 +1030, "Ray Murphy"
<raymur@chariot.net.au> wrote:

"Dave Mayall" <dave@research-group.co.uk> wrote in message
news:4sokdoFv90dlU1@mid.individual.net...

"Ray Murphy" <raymur@chariot.net.au> wrote in message
news:4566888c$1_2@news.chariot.net.au...
Hi All,

I've converted a gedcom file to an MS Access database via the GED2CSV
program and got some good things happening with it, but I haven't got a
clue how to connect people with relatives. For example, I can see in the
'Relations table' where married couples are 'connected', but I can see no
connections between children and parents, and grandparents etc.

Any ideas?

Yes.

Use something designed for the job!!

RM: Presumably you mean a genealogy program, but they are not designed to
query and display any data in my database - such as displaying a table
containing the basic data for all children and great grand parents in
consecutive lines.

Ray


Import the GEDCOM into a genealogy program that doesn't use a

proprietary file format (Legacy, Rootsmagic, The Master Genealogist
for ex.) and then import/convert the database files to Access.... then
operate on them in whatever manner you wish.

Or, with the more versatile programs, create a custom report in the
program that gives you the data you want, and import that report into
Access.
Charlie Hoffpauir
http://freepages.genealogy.rootsweb.com/~charlieh/

Dennis Lee Bieber

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Dennis Lee Bieber » 24. november 2006 kl. 21.50

On Sat, 25 Nov 2006 03:51:13 +1030, "Ray Murphy" <raymur@chariot.net.au>
declaimed the following in soc.genealogy.computing:


RM: Presumably you mean a genealogy program, but they are not designed to
query and display any data in my database - such as displaying a table
containing the basic data for all children and great grand parents in
consecutive lines.

Unfortunately (if I understand what your "data for all children and

great grand parents" implies), you are unlikely to find any RDBM that
can do that "out of the box"... You are looking at nested SQL queries,
at the least... Something like...

select c.name, c.dob, c.... gg.name, gg.dob, gg.... from persons as c
left outer join persons as p on c.fatherID = p.ID or c.motherID = p.ID
left outer join persons as g on p.fatherID = g.ID or p.motherID =
g.ID
left outer join persons as gg on g.fatherID = gg.ID or
g.motherID = gg.ID
order by c.ID, gg.ID

(left outer joins used to ensure getting data for individuals who do not
continue all the way to gg level)

I don't know what form of tables your utility generated, but I
expect that /somewhere/ in the data is information of the form:

ID | fID | mID

This could be in a distinct "family" table, or embedded within a single
"individual info" table.

I'll echo the post about using a genealogy program that uses a
common database management system, and then accessing its tables. TMG
uses Visual FoxPro (and the format for version 3 or 4 TMG used to be
available; TMG 6 may have added some fields but should be close enough
to start exploring).

It is not too difficult (under WinXP, at least) to define a ODBC
data-source using a VFP driver and configured for a "free table
directory" (this being the TMG project directory). One can then use
Access' interface to either Import selected tables (best to select all)
into a JET database (Access is a GUI form/report writer with a close tie
to the JET database engine, "Access Data Projects" just use JET to store
the report/form definitions, but use M$ SQL Server/MSDE as the database
engine).

If you have too much data for the file size limit of JET, you may
have to use "link tables".

Importing is safer in that any corruption of the data can be fixed
by deleting all tables and re-importing from the TMG project. Linking is
"live" -- changes made using Access will be reflected in the TMG
project.
--
bieber.genealogy Dennis Lee Bieber
HTTP://home.earthlink.net/~bieber.genealogy/

T.M. Sommers

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av T.M. Sommers » 25. november 2006 kl. 13.43

Ray Murphy wrote:
"Dave Mayall" <dave@research-group.co.uk> wrote in message
news:4sokdoFv90dlU1@mid.individual.net...
"Ray Murphy" <raymur@chariot.net.au> wrote in message
news:4566888c$1_2@news.chariot.net.au...

I've converted a gedcom file to an MS Access database via the GED2CSV
program and got some good things happening with it, but I haven't got a
clue how to connect people with relatives. For example, I can see in the
'Relations table' where married couples are 'connected', but I can see no
connections between children and parents, and grandparents etc.

Any ideas?

Yes.

Use something designed for the job!!

RM: Presumably you mean a genealogy program, but they are not designed to
query and display any data in my database - such as displaying a table
containing the basic data for all children and great grand parents in
consecutive lines.

Take a look at Lifelines. It has its own report-generation language.

--
Thomas M. Sommers -- tms@nj.net -- AB2SB

Gjest

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Gjest » 25. november 2006 kl. 15.44

On Fri, 24 Nov 2006 20:50:18 GMT, Dennis Lee Bieber
<bieber.genealogy@earthlink.net> wrote:

RM: Presumably you mean a genealogy program, but they are not designed to
query and display any data in my database - such as displaying a table
containing the basic data for all children and great grand parents in
consecutive lines.

Unfortunately (if I understand what your "data for all children and
great grand parents" implies), you are unlikely to find any RDBM that
can do that "out of the box"... You are looking at nested SQL queries,
at the least... Something like...

If Dennis' ideas don't generate a solution, you might also try posting
this problem on the comp.databases group. There are some very
talented professionals there who may be able/willing to assist. You
could post a small, representative portion of the GEDCOM format and
the corresponding .csv output for comparison. Then explain in some
detail what you want done in terms of the .csv formatted data so they
have an idea what it started out as, and what you want to represent as
a finished report.

Just remember that most of them aren't necessarily likely to be
familiar with dedicated genealogy programs. Don't be too put off if
you happen to run across the few on that newsgroup with attitudes
suggesting that only those with PhDs in computer science belong there
- most of them are quite willing to assist where they can. You might
also want to indicate what your level of competence with databases is,
so they know how to gear their responses.

C.R.

Ray Murphy

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Ray Murphy » 26. november 2006 kl. 7.31

<keyes04@netscape.net> wrote in message
news:53lgm291c13a10k24sa3dr89siele9a56r@4ax.com...
On Fri, 24 Nov 2006 20:50:18 GMT, Dennis Lee Bieber
bieber.genealogy@earthlink.net> wrote:

RM: Presumably you mean a genealogy program, but they are not designed
to
query and display any data in my database - such as displaying a table
containing the basic data for all children and great grand parents in
consecutive lines.

Unfortunately (if I understand what your "data for all children and
great grand parents" implies), you are unlikely to find any RDBM that
can do that "out of the box"... You are looking at nested SQL queries,
at the least... Something like...

If Dennis' ideas don't generate a solution, you might also try posting
this problem on the comp.databases group. There are some very
talented professionals there who may be able/willing to assist. You
could post a small, representative portion of the GEDCOM format and
the corresponding .csv output for comparison. Then explain in some
detail what you want done in terms of the .csv formatted data so they
have an idea what it started out as, and what you want to represent as
a finished report.

Just remember that most of them aren't necessarily likely to be
familiar with dedicated genealogy programs. Don't be too put off if
you happen to run across the few on that newsgroup with attitudes
suggesting that only those with PhDs in computer science belong there
- most of them are quite willing to assist where they can. You might
also want to indicate what your level of competence with databases is,
so they know how to gear their responses.

C.R.

RM: Thanks everyone on this thread for the brilliant responses. I suddenly
got tied up for a few days, but I'll reply in detail to each post when I
have time to wrap my brain around some of the suggestions.

I can see that there are a few different paths that can be taken, and they
are dependent on what my priorities are (or become), so I'll put up a few
web pages (including my amateurish projects) to show exactly what I'm doing
and where I'm trying to head.

In the meantime I'll throw in these few points:

(1) The object is to get gedcoms from numerous sources and convert each of
them (as stand-alone data) into a standard gedcom format so that the
conversion to MS Access will always be the same. Currently this is being
done via the Legacy program, and anything that gets dropped at that point is
ignored - not that any of it would be wanted anyway by the look of it.

(2) The translation from gedcom to Access is being done via GED2CSV (a free
30 day program).

(3) The Access queries need to be able to produce a display of every
Individual in Datasheet view (as a Master Index) - which is also displayed
in 'Form view' when dbl-clicking on a Name, and below that (in form view)
is a 'file card' [in datasheet view] showing all ancestors Name, Birth Date
& Location.
Currently I've got this, but I've reached a maximum limit and cannot bring
in spouses or Descendants.

(4) I also have another simple query the same as that above which brings in
all Events - such as Marriage or Death dates etc, but I have no idea how to
merge that data with 'people'. The idea is to treat Events + People as
exactly the same thing. The same is planned for personal characteristics
that are mentioned in notes, but that would be a manual operation -
converting them to "pseudo-Events".

(5) Ultimately all People + Events should be in one table for one gedcom,
and from there it's easy to write queries to export csv files for things
like:
(a) "All children and their G Grandparents" (Names, Dates, Locations)
(b) "All Persons & their Wedding dates"
(c) All Persons & Spouses
(d) All cases of things like "Not married", Early death, "No children"
etc.

The main reason for assembling data in this way is expand on astrological
research, for which special programs have been written, but it will also be
used for simple genetics research. It's a project that could conceivably
grab people's attention if they see how easy it would be to extract other
information from gedcoms - outside of any genealogy program.

It could even develop into an Open Source project if anyone knew how to get
one started.

Ray

dn17613
Innlegg: 313
Registrert: 2. desember 2004 kl. 21.10
Sted: TÅRNÅSEN

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av dn17613 » 26. november 2006 kl. 22.36

Ray Murphy skrev:
(1) ...convert each of them [...] into a standard gedcom format so that the conversion to MS Access will always be the same. Currently this is being done via the Legacy program...

(2) The translation from gedcom to Access is being done via GED2CSV (a free
30 day program).


You do know that the Legacy file format is infact a Access database? Or are there other reasons to go through gedcam again?

André Reitan

Dennis Lee Bieber

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Dennis Lee Bieber » 26. november 2006 kl. 23.05

On Sun, 26 Nov 2006 16:54:07 +1030, "Ray Murphy" <raymur@chariot.net.au>
declaimed the following in soc.genealogy.computing:


(1) The object is to get gedcoms from numerous sources and convert each of
them (as stand-alone data) into a standard gedcom format so that the
conversion to MS Access will always be the same. Currently this is being
done via the Legacy program, and anything that gets dropped at that point is
ignored - not that any of it would be wanted anyway by the look of it.

Have you explored:

http://office.microsoft.com/en-au/templ ... 1426031033

I have no idea how effective it is, but might serve as a starting
point.

(2) The translation from gedcom to Access is being done via GED2CSV (a free
30 day program).

{see above}


(3) The Access queries need to be able to produce a display of every
Individual in Datasheet view (as a Master Index) - which is also displayed
in 'Form view' when dbl-clicking on a Name, and below that (in form view)
is a 'file card' [in datasheet view] showing all ancestors Name, Birth Date
& Location.
Currently I've got this, but I've reached a maximum limit and cannot bring
in spouses or Descendants.

I sure hope you are comfortable with SQL and VBA programming! In any

normalized database the only way to get to spouse and/or one generation
away requires a join to the same data table (using two synonym names for
the table [this presumes that each individual record has "spouseID,
fatherID, motherID"; it is possible that your data structure has a
"family" table that identifies "fID, fatherID, motherID", and the
"individual" table just has a single "familyID"]). SQL is not designed
to handle recursive queries (return x, x-parents, x-parents^2,
x-parents^3... until no more are found). Such is handled
programmatically by creating, say, a scratch table of selected person
IDs (only, maybe a primary key for sorting in inserted order). Then
performing one generation selects and adding the IDs to the scratch
table -- repeat using the IDs just added, ad infinitum until you run out
of IDs in the scratch table. Then you can use the scratch table in a
join with the main table to select details for just those individuals.

(4) I also have another simple query the same as that above which brings in
all Events - such as Marriage or Death dates etc, but I have no idea how to
merge that data with 'people'. The idea is to treat Events + People as
exactly the same thing. The same is planned for personal characteristics
that are mentioned in notes, but that would be a manual operation -
converting them to "pseudo-Events".

I suspect you really need to look into TMG's schema

(http://www.whollygenes.com/forums201/in ... wtopic=381 and some
other information at http://solumslekt.org/forays/tmg.php)... EVERYTHING
in TMG is an "event", even a person's name is an "event" ("tag" in TMG
terms, but I started with UFT which was the other "event-based" program
out there).

(5) Ultimately all People + Events should be in one table for one gedcom,
and from there it's easy to write queries to export csv files for things
like:

Unlikely to be feasible... In a way, even a plain GEDCOM is "two
tables" -- individual records followed by family records. And, unless
you limit the "events" to one each of BMDB, the only way to handle
variable numbers of events is to use a table where each event is one
row/record, with an identifier of which person it belongs to. Google for
"database normalization" or "Codd normal forms"

(a) "All children and their G Grandparents" (Names, Dates, Locations)

See previous post...

(b) "All Persons & their Wedding dates"

select i.name, e.date from individual as i
inner join event as e
on i.ID = e.principal1_ID or i.ID = e.principal2_ID
where e.eventType = "Wedding"

{note that some events are related to TWO primary (principal)
individuals, so for something like a wedding you need to match against
both principals}. {this also assumes an event-based data base and not a
family-based}

(c) All Persons & Spouses

select i.name, s.name from individual as i
inner join event as e
on i.ID = e.principal1_ID
inner join individual as s
on e.principal2_ID = s.ID

{Note: this query will not duplicate people... the first name will be
the person is "principal1" position and the second name will be the
"principal2" position}

(d) All cases of things like "Not married", Early death, "No children"
etc.

select i.name from individual as i
where i.id not in
(select unique e1.principal1_ID from event as e1
where e1.eventType = "Wedding"
union select unique e2.principal2_ID from event as e2
where e2.eventType = "Wedding")

{NOTE: I need to check the SQL usage of "not in" with a subselect; the
subselect finds all IDs that HAVE wedding events, then the outer select
should keep those IDs that don't appear}

Define "early death"...

select i.name from individual as i
where i.id not in
(select unique e1.principal1_ID from event as e1
where e1.eventType = "FatherRelationship"
or e1.eventType = "MotherRelationship")

{Assumes principal1 is the parent, and principal2 is the child; same
comment about proper use of subselect and not in...}

--
bieber.genealogy Dennis Lee Bieber
HTTP://home.earthlink.net/~bieber.genealogy/

Paul Blair

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Paul Blair » 27. november 2006 kl. 1.14

André Reitan wrote:
Ray Murphy wrote:

(1) ...convert each of them [...] into a standard gedcom format so
that the conversion to MS Access will always be the same. Currently
this is being done via the Legacy program...
(2) The translation from gedcom to Access is being done via GED2CSV
(a free
30 day program).


You do know that the Legacy file format is infact a Access database?
Or are there other reasons to go through gedcam again?

André Reitan
--
Postet fra DIS-Norges tjeneste Slektsforum
https://slektsforum.slektogdata.no/

It surely is an Access db.

If you know any do-it-yourself things about Access, you can use the raw
Legacy data to produce all sorts of items not available in Legacy
itself. For example, I made a table of the longest marriage durations
for a web page that way.

Paul

Ray Murphy

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Ray Murphy » 27. november 2006 kl. 7.10

"Paul Blair" <pblair@pcug.org.au> wrote in message
news:456a2ddc$0$5744$afc38c87@news.optusnet.com.au...
André Reitan wrote:
Ray Murphy wrote:

(1) ...convert each of them [...] into a standard gedcom format so
that the conversion to MS Access will always be the same. Currently
this is being done via the Legacy program...
(2) The translation from gedcom to Access is being done via GED2CSV
(a free
30 day program).


You do know that the Legacy file format is infact a Access database?
Or are there other reasons to go through gedcam again?

André Reitan

[TWO in one - reply]

RM: Thanks André. I've just now had another look in the Legacy/Data folder
and then remembered why I decided to stay with the GED2CSV converter for
gedcom to Access translations (for the moment anyway). It's because that
translation provides tables that are ready to go, and more suitable for a
novice like myself.

For example, it produces an 'Individuals + Events' table which I found easy
to link and get all events visible in a Form when any name is selected with
the mousewheel etc.

I suppose an experienced Access user would probably achieve the same thing
very quickly with any Legacy file, and point out that by doing so, it cuts
out
the need for the commercial converter; however for now I need to stick with
something that is actually producing [and exporting] some of the desired
results.

It surely is an Access db.

If you know any do-it-yourself things about Access, you can use the raw
Legacy data to produce all sorts of items not available in Legacy itself.
For example, I made a table of the longest marriage durations for a web
page that way.

Paul

RM: Yes, there would be a lot of potential there - especially if all the
data was re-formatted in Access to allow the *easy* building of queries -
via conventional means, or (if necessary) via the slower reading of strings.
Basically that's what I'm trying to do.

Ray

Ray Murphy

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Ray Murphy » 27. november 2006 kl. 7.18

"Charlie Hoffpauir" <invalid@invalid.com> wrote in message
news:22dem2lmvj46eihj0ahumgrqliqh4oi1kr@4ax.com...
On Sat, 25 Nov 2006 03:51:13 +1030, "Ray Murphy"
raymur@chariot.net.au> wrote:


"Dave Mayall" <dave@research-group.co.uk> wrote in message
news:4sokdoFv90dlU1@mid.individual.net...

"Ray Murphy" <raymur@chariot.net.au> wrote in message
news:4566888c$1_2@news.chariot.net.au...
Hi All,

I've converted a gedcom file to an MS Access database via the GED2CSV
program and got some good things happening with it, but I haven't got a
clue how to connect people with relatives. For example, I can see in
the
'Relations table' where married couples are 'connected', but I can see
no
connections between children and parents, and grandparents etc.

Any ideas?

Yes.

Use something designed for the job!!

RM: Presumably you mean a genealogy program, but they are not designed to
query and display any data in my database - such as displaying a table
containing the basic data for all children and great grand parents in
consecutive lines.

Import the GEDCOM into a genealogy program that doesn't use a
proprietary file format (Legacy, Rootsmagic, The Master Genealogist
for ex.) and then import/convert the database files to Access.... then
operate on them in whatever manner you wish.

RM: Excuse my ignorance here, but I assume you mean - import a gedcom into a
genealogy program that uses a *standard* gedcom format.

Or, with the more versatile programs, create a custom report in the
program that gives you the data you want, and import that report into
Access.
Charlie Hoffpauir

RM: At this point I think I want to import *everything* from a gedcom into
Access (to be able to tap into it); so I cannot see any advantage in any
type of selective filtering, but there may be advantages in what you are
suggesting that I am oblivious to.

Ray

Ray Murphy

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Ray Murphy » 27. november 2006 kl. 7.21

"T.M. Sommers" <tms@nj.net> wrote in message
news:45683949$0$5786$470ef3ce@news.pa.net...
Ray Murphy wrote:
"Dave Mayall" <dave@research-group.co.uk> wrote in message
news:4sokdoFv90dlU1@mid.individual.net...
"Ray Murphy" <raymur@chariot.net.au> wrote in message
news:4566888c$1_2@news.chariot.net.au...

[.....]


Use something designed for the job!!

RM: Presumably you mean a genealogy program, but they are not designed to
query and display any data in my database - such as displaying a table
containing the basic data for all children and great grand parents in
consecutive lines.

Take a look at Lifelines. It has its own report-generation language.

--
Thomas M. Sommers -- tms@nj.net -- AB2SB

RM: I tried to download it but nothing happens.

Ray

Paul Blair

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Paul Blair » 27. november 2006 kl. 7.24

Paul

RM: Yes, there would be a lot of potential there - especially if all the
data was re-formatted in Access to allow the *easy* building of queries -
via conventional means, or (if necessary) via the slower reading of strings.
Basically that's what I'm trying to do.

Ray




I'm not at all sure what you mean. Legacy stores data at pretty much
first principles level, leaving the door(s) wide open for personal
efforts. You can build anything you want from that, provided the data is
in a table or tables, and there is a means of relating one table to
another. The systems are designed primarily for genealogy.

Paul

Dennis Lee Bieber

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Dennis Lee Bieber » 27. november 2006 kl. 8.17

On Mon, 27 Nov 2006 16:46:08 +1030, "Ray Murphy" <raymur@chariot.net.au>
declaimed the following in soc.genealogy.computing:

..
RM: Excuse my ignorance here, but I assume you mean - import a gedcom into a
genealogy program that uses a *standard* gedcom format.

No, he meant what he said... Use a genealogy program whose NATIVE

data format is openly documented, or otherwise directly accessible using
non-genealogy tools. Legacy uses M$ JET database engine (its .FDB files
are essential .MDB files with a different name, and no forms/reports
internal); TMG uses Visual FoxPro format (and these, via ODBC drivers,
can be imported directly into a JET database). JET is the native
database engine used by M$ Access -- so the import can be done via
Access commands.

For all practical purposes, there is no "standard" GEDCOM unless you
restrict yourself to ONLY the data used by the LDS (who produce PAF, as
I recall).
--
bieber.genealogy Dennis Lee Bieber
HTTP://home.earthlink.net/~bieber.genealogy/

Ray Murphy

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Ray Murphy » 27. november 2006 kl. 10.13

"Dennis Lee Bieber" <bieber.genealogy@earthlink.net> wrote in message
news:oiwah.4318$1s6.465@newsread2.news.pas.earthlink.net...
On Mon, 27 Nov 2006 16:46:08 +1030, "Ray Murphy" <raymur@chariot.net.au
declaimed the following in soc.genealogy.computing:

RM: Excuse my ignorance here, but I assume you mean - import a gedcom
into a
genealogy program that uses a *standard* gedcom format.

No, he meant what he said... Use a genealogy program whose NATIVE
data format is openly documented, or otherwise directly accessible using
non-genealogy tools.

RM: Ok, I thought all gedcoms were readable if they were in text.

Legacy uses M$ JET database engine (its .FDB files
are essential .MDB files with a different name, and no forms/reports
internal); TMG uses Visual FoxPro format (and these, via ODBC drivers,
can be imported directly into a JET database). JET is the native
database engine used by M$ Access -- so the import can be done via
Access commands.

RM: Ah, of course! Write a suitable importing instruction and everything
from one of those can be automatically placed in the user's Access data
base.

For all practical purposes, there is no "standard" GEDCOM unless you
restrict yourself to ONLY the data used by the LDS (who produce PAF, as
I recall).

RM: Thanks. I had the idea that the LDS gedcoms were considered the
standard; but in any case it seems at the moment that virtually any gedcom
format will be alright for this project because it's only basic data that
will being used, although I learned recently via this newsgroup that a few
of the good genealogy programs include facilities for adding a large array
of Events, but I'm beginning to think that the average person wouldn't be
using most of them because they seem extremely cumbersome to enter.

Ray

Ray Murphy

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Ray Murphy » 27. november 2006 kl. 10.24

"Paul Blair" <pblair@pcug.org.au> wrote in message
news:456a84b1$0$9775$afc38c87@news.optusnet.com.au...
Paul

RM: Yes, there would be a lot of potential there - especially if all the
data was re-formatted in Access to allow the *easy* building of queries -
via conventional means, or (if necessary) via the slower reading of
strings.
Basically that's what I'm trying to do.

I'm not at all sure what you mean. Legacy stores data at pretty much first
principles level, leaving the door(s) wide open for personal efforts. You
can build anything you want from that, provided the data is in a table or
tables, and there is a means of relating one table to another. The systems
are designed primarily for genealogy.

Paul

RM: When you boil it all down I'm talking about duplication of some things
so that non-experts (especially myself) can more easily figure out ways to
write queries.
I understand that duplication should be discouraged, but it seems less risky
when only two tables are being used -- Individuals + Events.
I just hope I don't get reported for doing it! :-))>

Ray

Joe Makowiec

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Joe Makowiec » 27. november 2006 kl. 14.28

On 27 Nov 2006 in soc.genealogy.computing, Ray Murphy wrote:

RM: Ok, I thought all gedcoms were readable if they were in text.

GEDCOM files /are/ plain text. Open one in, say, Notepad and have a
look.

RM: Ah, of course! Write a suitable importing instruction and
everything from one of those can be automatically placed in the
user's Access data base.

Many genealogy programs use tables which are directly readable by
DBMSes - Legacy (Access MDB files) and TMG (FoxPro .dbf) have been
mentioned in this thread. Import into those programs, and you have a
set of DBMS-readable files.

RM: Thanks. I had the idea that the LDS gedcoms were considered the
standard; but in any case it seems at the moment that virtually any
gedcom format will be alright for this project because it's only
basic data that will being used,

http://homepages.rootsweb.com/~pmcbride ... 5gctoc.htm

There are a couple of problems with this:
- Some items in the standard are (legitimately) open to interpretation.
- Some producers of genealogy programs use what are euphamistically
termed 'extensions' to the standard in order to make their product's
database structure 'fit' the GEDCOM model. Some of these attempts are
more successful than others.

although I learned recently via this newsgroup that a few of the
good genealogy programs include facilities for adding a large array
of Events, but I'm beginning to think that the average person
wouldn't be using most of them because they seem extremely
cumbersome to enter.

Don't make this assumption. I read the The Master Genealogist (TMG)
mailing list; there are all kinds of creative attempts to use the
advanced features of TMG in ways their creator never intended. And for
those who do that, and want to export via GEDCOM, the impact ranges
from trivial to near-impossible.

http://lists.rootsweb.com/index/other/Software/TMG.html

--
Joe Makowiec
http://makowiec.org/
Email: http://makowiec.org/contact/?Joe

Charlie Hoffpauir

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Charlie Hoffpauir » 27. november 2006 kl. 15.03

On Mon, 27 Nov 2006 19:50:11 +1030, "Ray Murphy"
<raymur@chariot.net.au> wrote:

"Paul Blair" <pblair@pcug.org.au> wrote in message
news:456a84b1$0$9775$afc38c87@news.optusnet.com.au...

Paul

RM: Yes, there would be a lot of potential there - especially if all the
data was re-formatted in Access to allow the *easy* building of queries -
via conventional means, or (if necessary) via the slower reading of
strings.
Basically that's what I'm trying to do.

I'm not at all sure what you mean. Legacy stores data at pretty much first
principles level, leaving the door(s) wide open for personal efforts. You
can build anything you want from that, provided the data is in a table or
tables, and there is a means of relating one table to another. The systems
are designed primarily for genealogy.

Paul

RM: When you boil it all down I'm talking about duplication of some things
so that non-experts (especially myself) can more easily figure out ways to
write queries.
I understand that duplication should be discouraged, but it seems less risky
when only two tables are being used -- Individuals + Events.
I just hope I don't get reported for doing it! :-))

Ray



Ray,

After reading all the posts in in this thread, I have a couple of
questions/suggestions....

1. You mentioned in the first post starting with a GEDCOM file. From
where do you get that file? If it's from a genealogy program you have,
which one is it?

2. I ask, because from some of your comments, (ie "it's only basic
data that will being used"), it would seem that you should be able to
generate a "report" from within your genealogy program, print it to a
file, and then import that flie into Accsess. When I was using FTM ver
8 I used to do that frequently so that I could create an Access
database of my data, to enable me to do a queary based on a
combination of Husband's and wives names. Once you get everything you
want in a report, it's quite easy to get that imported into Access,
and easily write queries to do some very "custom" searches. Most
genealogy programs I've tried will create custom reports like this.

Charlie Hoffpauir
http://freepages.genealogy.rootsweb.com/~charlieh/

Dennis Lee Bieber

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Dennis Lee Bieber » 27. november 2006 kl. 19.23

On Mon, 27 Nov 2006 19:39:35 +1030, "Ray Murphy" <raymur@chariot.net.au>
declaimed the following in soc.genealogy.computing:

RM: Ok, I thought all gedcoms were readable if they were in text.

ALL GEDCOMs ARE text; there are NO binary GEDCOMs.


GEDCOM (GEnealogical Data COMmunication) is a format for
transferring data between researchers, but is NOT the format used by
genealogy programs for storing the data (which is why there isn't any
real standard -- every program adds features in its internal database
and then warps GEDCOM output in the attempt to transfer the additional
features; only to have that data thrown away by any program that doesn't
know about the feature).

RM: Ah, of course! Write a suitable importing instruction and everything
from one of those can be automatically placed in the user's Access data
base.

In the case of TMG, most of the set-up is in defining the ODBC

datasource. From a recent email response I sent to the TMG mailing list:

-=-=-=-=-=-=-=-
Start/Settings/Control Panel
Administrative Tools
Data Sources (ODBC)
User DSN
[Add]

Microsoft Visual FoxPro Driver
[Finish]

(Data Source Name) {Pick a simple name}
(Description) {Whatever}

* Free Table Directory

(Path) {Full path to project directory}

[Warning: this control panel seems to use the 16-bit W3.x file selector,
and I had a lot of problems using the Browse feature -- the scroll bar
and arrows seemed to be unable to work with my directory structure]

[Okay] all the windows back to the desktop...


Open Access, create a new/blank database...

From the database "table" list, <right-click>
Import OR
Link Tables depending upon your trust

Import should be fully safe, as the data is copied into a M$ JET engine
format, whereas Link is "live", it accesses the actual VFP tables as
needed.

Change "Files of Type" to "ODBC Databases ()".

Select "Machine Data Source"; select the TMG source created above.

Select [All Tables] and okay out. Wait for the import or links to be
created...
-=-=-=-=-=-=-=-

This procedure gives you all the TMG data. Making usable reports
consists of studying the data schema (I think I posted a link to the
description file), and writing SQL queries to combine the needed fields
from the many tables. TMG's place information is the most difficult to
do a simple query on -- might need a VBA function for that <G>.


RM: Thanks. I had the idea that the LDS gedcoms were considered the
standard; but in any case it seems at the moment that virtually any gedcom
format will be alright for this project because it's only basic data that
will being used, although I learned recently via this newsgroup that a few
of the good genealogy programs include facilities for adding a large array
of Events, but I'm beginning to think that the average person wouldn't be
using most of them because they seem extremely cumbersome to enter.

On the contrary, if one is using an event-based program, they are

relatively easy... I'd love to show the data entry screens, but I don't
think anyone would thank me for embedded graphics. However, a text dump
of one individual shows the type of data that can be handled

-=-=-=-=-=-

Individual Detail

Subject* John BIEBER (44)
Father* Joseph BIEBER (54) (b. Jan 18, 1818, d. Feb 8, 1879)
Mother* Josephine BAUCH (55) (b. Mar 4, 1820, d. Feb 26,
1900)

Birth* Oct 10, 1850 Avon, Lorain, Ohio.
Birth- circa ___ __ Ohio; based upon stated age on 1880
Alt: 1857 Census transcript.
Churchmem ___ __ ____ North Dorr, Kent, Michigan; Roman
b* Catholic.
Marriage* Nov 11, 1873 Catharina STEFFES (45) (b. May 16,
1852, d. Jan 30, 1938); North Dorr,
Kent, Michigan.
Residence ___ __ ____ Catharina STEFFES (45) (b. May 16,
* 1852, d. Jan 30, 1938); Section 36,
Jamestown, Ottawa, Michigan; on a farm,
originally purchased by her father.
Anecdote* ___ __ ____ The area was relatively wild with
abundant bear and turkey. There is a
story of Mrs. Pfeiffer chasing a bear
from the log house, which did not have
a solid door. Rattlesnakes were also
common, causing John to wear high
rubber boots year round for farming or
building.
Occupatio ___ __ ____ Jamestown, Ottawa, Michigan; a
n* carpenter; He built Mitchel and Gitchel
Schools in Jamestown Township. He also
built Sycamore and Plainview Schools in
Dorr Township.
Son: Apr 27, 1874 Joseph BIEBER (46); Jamestown, Ottawa,
Michigan.
Son: Mar 15, 1876 Michael BIEBER (47); Jamestown, Ottawa,
Michigan.
Son: Aug 8, 1878 Anton BIEBER (48); Jamestown, Ottawa,
Michigan.
Daughter: Jun 1, 1880 Helen BIEBER (49); Jamestown, Ottawa,
Michigan.
Census188 after Jun 1, Catharina STEFFES (45) (b. May 16,
0* 1880 1852, d. Jan 30, 1938); Jamestown,
Ottawa, Michigan.
Landcontr Aug 6, 1881 Michael BIEBER (56) (b. 1845, d. );
a* Section 36, Jamestown, Ottawa,
Michigan.
Son: Feb 4, 1883 John BIEBER Jr. (51); Jamestown,
Ottawa, Michigan.
Daughter: Apr 5, 1886 Mary Josephine BIEBER (50); Jamestown,
Ottawa, Michigan.
Son: Sep 9, 1888 William BIEBER (10); Jamestown, Ottawa,
Michigan.
Son: Nov 10, 1890 August BIEBER (52); Jamestown, Ottawa,
Michigan.
Son: Nov 13, 1893 Frank BIEBER (53); Jamestown, Ottawa,
Michigan.
Death* Aug 1, 1920 Byron, Kent, Michigan; cancer of the
esophagus.
Burial* ___ __ ____ Visitation Cemetery, North Dorr, Kent,
Michigan.
Reference= (Dec'd @ 69 yrs) Children=9
Flags : SEX=M LIVING=N BIRTH ORDER=? MULTIPLE
BIRTH=? ADOPTED=? ANCESTOR INTEREST=3
DESCENDANT INTEREST=3 FAMILY=Y EOL=N

Printed on: 27 Nov 2006
Prepared by:
Dennis Lee Bieber
bieber.genealogy@earthlink.net
-=-=-=-=-=--

Starting with the "Birth*" line, each identified item is an "event".
Most are the basic BMDB data in this sample, but you'll notice a
"Census1880" -- which is a custom tag used to identify/group a census
entry.

Where a program like FTM tends to have just a generic "note", TMG
allows for creating situation specific events. This then allows for
doing searches based on the events themselves (find all persons with a
birth date between 1780 and 1881 OR a death date between 1880 and 1980,
who do not have a Census1880 event -- for example).
--
bieber.genealogy Dennis Lee Bieber
HTTP://home.earthlink.net/~bieber.genealogy/

Dennis Lee Bieber

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Dennis Lee Bieber » 27. november 2006 kl. 19.23

On Mon, 27 Nov 2006 13:28:30 GMT, Joe Makowiec
<makowiec@invalid.invalid> declaimed the following in
soc.genealogy.computing:

Don't make this assumption. I read the The Master Genealogist (TMG)
mailing list; there are all kinds of creative attempts to use the
advanced features of TMG in ways their creator never intended. And for

About once a year I get a short-lived urge to take the history of
Standard Oil and enter it into TMG...

Mergers would be a "marriage" with a silent "death" immediately
following, maybe with a "name-change" event for the surviving entity.
"birth" events for corporate splits (again, maybe with a "death" if the
parent corporation ceased to exist).

Customize the sentences for those events, and one could probably
produce a highly incestuous looking report <G>
--
bieber.genealogy Dennis Lee Bieber
HTTP://home.earthlink.net/~bieber.genealogy/

Hugh Watkins

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Hugh Watkins » 27. november 2006 kl. 19.56

Ray Murphy wrote:

"Charlie Hoffpauir" <invalid@invalid.com> wrote in message
news:22dem2lmvj46eihj0ahumgrqliqh4oi1kr@4ax.com...

On Sat, 25 Nov 2006 03:51:13 +1030, "Ray Murphy"
raymur@chariot.net.au> wrote:


"Dave Mayall" <dave@research-group.co.uk> wrote in message
news:4sokdoFv90dlU1@mid.individual.net...

"Ray Murphy" <raymur@chariot.net.au> wrote in message
news:4566888c$1_2@news.chariot.net.au...

Hi All,

I've converted a gedcom file to an MS Access database via the GED2CSV
program and got some good things happening with it, but I haven't got a
clue how to connect people with relatives. For example, I can see in
the
'Relations table' where married couples are 'connected', but I can see
no
connections between children and parents, and grandparents etc.

Any ideas?

Yes.

Use something designed for the job!!

RM: Presumably you mean a genealogy program, but they are not designed to
query and display any data in my database - such as displaying a table
containing the basic data for all children and great grand parents in
consecutive lines.


Import the GEDCOM into a genealogy program that doesn't use a
proprietary file format (Legacy, Rootsmagic, The Master Genealogist
for ex.) and then import/convert the database files to Access.... then
operate on them in whatever manner you wish.


RM: Excuse my ignorance here, but I assume you mean - import a gedcom into a
genealogy program that uses a *standard* gedcom format.


Or, with the more versatile programs, create a custom report in the
program that gives you the data you want, and import that report into
Access.
Charlie Hoffpauir


RM: At this point I think I want to import *everything* from a gedcom into
Access (to be able to tap into it); so I cannot see any advantage in any
type of selective filtering, but there may be advantages in what you are
suggesting that I am oblivious to.

merging gedcoms tends to make one big soup
a mess with much duplication

I only ever import data I have totally reviewed person by person

I find FTM 16 is adequate for my own use

I purchased Custodian 2 and 3
but could not be bothered with reentering all my data

http://www.custodian3.co.uk/

and Custodian 3 is a database with a very useful range of templates
so why are you reinventing the wheel?

Hugh W

--

Beta blogger
http://nanowrimo3.blogspot.com/ visiting my past
http://hughw36-2.blogspot.com/ re-entry
http://snaps4.blogspot.com/" photographs and walks

old blogger
http://hughw36.blogspot.com/ MAIN BLOG

john

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av john » 27. november 2006 kl. 21.05

Ray Murphy wrote:
"Dennis Lee Bieber" <bieber.genealogy@earthlink.net> wrote in message
news:oiwah.4318$1s6.465@newsread2.news.pas.earthlink.net...
On Mon, 27 Nov 2006 16:46:08 +1030, "Ray Murphy" <raymur@chariot.net.au
declaimed the following in soc.genealogy.computing:

RM: Excuse my ignorance here, but I assume you mean - import a gedcom
into a
genealogy program that uses a *standard* gedcom format.

No, he meant what he said... Use a genealogy program whose NATIVE
data format is openly documented, or otherwise directly accessible using
non-genealogy tools.

RM: Ok, I thought all gedcoms were readable if they were in text.

Legacy uses M$ JET database engine (its .FDB files
are essential .MDB files with a different name, and no forms/reports
internal); TMG uses Visual FoxPro format (and these, via ODBC drivers,
can be imported directly into a JET database). JET is the native
database engine used by M$ Access -- so the import can be done via
Access commands.

RM: Ah, of course! Write a suitable importing instruction and everything
from one of those can be automatically placed in the user's Access data
base.

For all practical purposes, there is no "standard" GEDCOM unless you
restrict yourself to ONLY the data used by the LDS (who produce PAF, as
I recall).

RM: Thanks. I had the idea that the LDS gedcoms were considered the
standard; but in any case it seems at the moment that virtually any gedcom
format will be alright for this project because it's only basic data that
will being used, although I learned recently via this newsgroup that a few
of the good genealogy programs include facilities for adding a large array
of Events, but I'm beginning to think that the average person wouldn't be
using most of them because they seem extremely cumbersome to enter.

Ray

I believe the genealogy program Family Historian

(http://www.family-historian.co.uk/) stores it's data in a GEDCOM file
by default. You might want to look at the report functions it offers

Paul Blair

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Paul Blair » 27. november 2006 kl. 22.22

Ray Murphy wrote:
"Charlie Hoffpauir" <invalid@invalid.com> wrote in message
news:6grlm29nv43vb6176kj705r582ej7oj6c2@4ax.com...
On Mon, 27 Nov 2006 19:50:11 +1030, "Ray Murphy"
raymur@chariot.net.au> wrote:

"Paul Blair" <pblair@pcug.org.au> wrote in message
news:456a84b1$0$9775$afc38c87@news.optusnet.com.au...
Paul
RM: Yes, there would be a lot of potential there - especially if all
the
data was re-formatted in Access to allow the *easy* building of
queries -
via conventional means, or (if necessary) via the slower reading of
strings.
Basically that's what I'm trying to do.
I'm not at all sure what you mean. Legacy stores data at pretty much
first
principles level, leaving the door(s) wide open for personal efforts.
You
can build anything you want from that, provided the data is in a table
or
tables, and there is a means of relating one table to another. The
systems
are designed primarily for genealogy.

Paul
RM: When you boil it all down I'm talking about duplication of some things
so that non-experts (especially myself) can more easily figure out ways to
write queries.
I understand that duplication should be discouraged, but it seems less
risky
when only two tables are being used -- Individuals + Events.
I just hope I don't get reported for doing it! :-))


Ray,

After reading all the posts in in this thread, I have a couple of
questions/suggestions....

1. You mentioned in the first post starting with a GEDCOM file. From
where do you get that file? If it's from a genealogy program you have,
which one is it?

RM: This is a key question. The gedcoms will be generated by virtually all
programs - many of them on the net.

2. I ask, because from some of your comments, (ie "it's only basic
data that will being used"), it would seem that you should be able to
generate a "report" from within your genealogy program, print it to a
file, and then import that flie into Accsess. When I was using FTM ver
8 I used to do that frequently so that I could create an Access
database of my data, to enable me to do a queary based on a
combination of Husband's and wives names. Once you get everything you
want in a report, it's quite easy to get that imported into Access,
and easily write queries to do some very "custom" searches. Most
genealogy programs I've tried will create custom reports like this.

Charlie Hoffpauir

RM: I appreciate the effort that different posters have made while trying to
follow this unusual project and provide good solutions for various facets of
it without having a complete picture.

Most or all of the replies have included alerts to possible problems
or better pathways - which is more than I had hoped for, but I think that
despite the large array of possible solutions from here on, there are two
things that are pretty much set in concrete -- (1) The data for my research
will be coming from a whole variety of gedcom formats and (2) Each gedcom
needs to be put into one style of Access database as an individual project.

This ~seems~ to exclude the use of the various program filtering facilities
if rapid handling of data is to be achieved, because it could become
unwieldily to try and handle data at the 'program level' - with so many of
them in use.

The idea is to use whatever any style of gedcom contains and run with it
(assuming every gedcom contains the most basic information). Anything more
than that might need to be dropped for the sake of uniformity when hundreds
of assorted gedcoms are being used.

I'm still trying to fully absorb the value of using any of the ready-made
Access files that are produced by some programs, and whether the advantages
of using them are outweighed by the disadvantages involved in having two
input systems instead of one (gedcom + Access files rather than all
gedcoms).

Ray


Let's make some decisions...
1. Get the assorted GEDCOMs from wherever, store a safe copy.
2. Absorb them into Legacy, in individual files
3. Examine each one for quality (set your own standards...)
4. When quality is OK, merge them together

(2) takes care of variability, (3) gives you a degree of control/qm, (4)
gives the final result in M$ Access...which gives you the required
authority over the data.

Paul

Ray Murphy

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Ray Murphy » 27. november 2006 kl. 22.26

"Charlie Hoffpauir" <invalid@invalid.com> wrote in message
news:6grlm29nv43vb6176kj705r582ej7oj6c2@4ax.com...
On Mon, 27 Nov 2006 19:50:11 +1030, "Ray Murphy"
raymur@chariot.net.au> wrote:


"Paul Blair" <pblair@pcug.org.au> wrote in message
news:456a84b1$0$9775$afc38c87@news.optusnet.com.au...

Paul

RM: Yes, there would be a lot of potential there - especially if all
the
data was re-formatted in Access to allow the *easy* building of
queries -
via conventional means, or (if necessary) via the slower reading of
strings.
Basically that's what I'm trying to do.

I'm not at all sure what you mean. Legacy stores data at pretty much
first
principles level, leaving the door(s) wide open for personal efforts.
You
can build anything you want from that, provided the data is in a table
or
tables, and there is a means of relating one table to another. The
systems
are designed primarily for genealogy.

Paul

RM: When you boil it all down I'm talking about duplication of some things
so that non-experts (especially myself) can more easily figure out ways to
write queries.
I understand that duplication should be discouraged, but it seems less
risky
when only two tables are being used -- Individuals + Events.
I just hope I don't get reported for doing it! :-))


Ray,

After reading all the posts in in this thread, I have a couple of
questions/suggestions....

1. You mentioned in the first post starting with a GEDCOM file. From
where do you get that file? If it's from a genealogy program you have,
which one is it?

RM: This is a key question. The gedcoms will be generated by virtually all
programs - many of them on the net.

2. I ask, because from some of your comments, (ie "it's only basic
data that will being used"), it would seem that you should be able to
generate a "report" from within your genealogy program, print it to a
file, and then import that flie into Accsess. When I was using FTM ver
8 I used to do that frequently so that I could create an Access
database of my data, to enable me to do a queary based on a
combination of Husband's and wives names. Once you get everything you
want in a report, it's quite easy to get that imported into Access,
and easily write queries to do some very "custom" searches. Most
genealogy programs I've tried will create custom reports like this.

Charlie Hoffpauir

RM: I appreciate the effort that different posters have made while trying to
follow this unusual project and provide good solutions for various facets of
it without having a complete picture.

Most or all of the replies have included alerts to possible problems
or better pathways - which is more than I had hoped for, but I think that
despite the large array of possible solutions from here on, there are two
things that are pretty much set in concrete -- (1) The data for my research
will be coming from a whole variety of gedcom formats and (2) Each gedcom
needs to be put into one style of Access database as an individual project.

This ~seems~ to exclude the use of the various program filtering facilities
if rapid handling of data is to be achieved, because it could become
unwieldily to try and handle data at the 'program level' - with so many of
them in use.

The idea is to use whatever any style of gedcom contains and run with it
(assuming every gedcom contains the most basic information). Anything more
than that might need to be dropped for the sake of uniformity when hundreds
of assorted gedcoms are being used.

I'm still trying to fully absorb the value of using any of the ready-made
Access files that are produced by some programs, and whether the advantages
of using them are outweighed by the disadvantages involved in having two
input systems instead of one (gedcom + Access files rather than all
gedcoms).

Ray

Charlie Hoffpauir

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Charlie Hoffpauir » 28. november 2006 kl. 4.47

On Tue, 28 Nov 2006 08:22:53 +1100, Paul Blair <pblair@pcug.org.au>
wrote:

<snip>
RM: I appreciate the effort that different posters have made while trying to
follow this unusual project and provide good solutions for various facets of
it without having a complete picture.

Most or all of the replies have included alerts to possible problems
or better pathways - which is more than I had hoped for, but I think that
despite the large array of possible solutions from here on, there are two
things that are pretty much set in concrete -- (1) The data for my research
will be coming from a whole variety of gedcom formats and (2) Each gedcom
needs to be put into one style of Access database as an individual project.

This ~seems~ to exclude the use of the various program filtering facilities
if rapid handling of data is to be achieved, because it could become
unwieldily to try and handle data at the 'program level' - with so many of
them in use.

The idea is to use whatever any style of gedcom contains and run with it
(assuming every gedcom contains the most basic information). Anything more
than that might need to be dropped for the sake of uniformity when hundreds
of assorted gedcoms are being used.

I'm still trying to fully absorb the value of using any of the ready-made
Access files that are produced by some programs, and whether the advantages
of using them are outweighed by the disadvantages involved in having two
input systems instead of one (gedcom + Access files rather than all
gedcoms).

Ray


Let's make some decisions...
1. Get the assorted GEDCOMs from wherever, store a safe copy.
2. Absorb them into Legacy, in individual files
3. Examine each one for quality (set your own standards...)
4. When quality is OK, merge them together

(2) takes care of variability, (3) gives you a degree of control/qm, (4)
gives the final result in M$ Access...which gives you the required
authority over the data.

Paul

Although I personally don't use Legacy, I do agree with Paul that
using it in this case does seem to be a very good approach. At the
very least, importing all the different "flavors" of GEDCOMS into
Legacy will make them all compatible.... at the basic level. Then it
(seems to be) a minor step to getting the Access files you are looking
for.

Just download a copy of the free version of Legacy and give it a try.

Charlie Hoffpauir
http://freepages.genealogy.rootsweb.com/~charlieh/

Ray Murphy

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Ray Murphy » 29. november 2006 kl. 10.31

"Charlie Hoffpauir" <invalid@invalid.com> wrote in message
news:g3cnm2p90sksqr0rq1in57ltt4gh1jvaee@4ax.com...
On Tue, 28 Nov 2006 08:22:53 +1100, Paul Blair <pblair@pcug.org.au
wrote:

snip
RM: I appreciate the effort that different posters have made while
trying to
follow this unusual project and provide good solutions for various
facets of
it without having a complete picture.

Most or all of the replies have included alerts to possible problems
or better pathways - which is more than I had hoped for, but I think
that
despite the large array of possible solutions from here on, there are
two
things that are pretty much set in concrete -- (1) The data for my
research
will be coming from a whole variety of gedcom formats and (2) Each
gedcom
needs to be put into one style of Access database as an individual
project.

This ~seems~ to exclude the use of the various program filtering
facilities
if rapid handling of data is to be achieved, because it could become
unwieldily to try and handle data at the 'program level' - with so many
of
them in use.

The idea is to use whatever any style of gedcom contains and run with it
(assuming every gedcom contains the most basic information). Anything
more
than that might need to be dropped for the sake of uniformity when
hundreds
of assorted gedcoms are being used.

I'm still trying to fully absorb the value of using any of the
ready-made
Access files that are produced by some programs, and whether the
advantages
of using them are outweighed by the disadvantages involved in having two
input systems instead of one (gedcom + Access files rather than all
gedcoms).

Ray


Let's make some decisions...
1. Get the assorted GEDCOMs from wherever, store a safe copy.
2. Absorb them into Legacy, in individual files
3. Examine each one for quality (set your own standards...)
4. When quality is OK, merge them together

(2) takes care of variability, (3) gives you a degree of control/qm, (4)
gives the final result in M$ Access...which gives you the required
authority over the data.

Paul

Although I personally don't use Legacy, I do agree with Paul that
using it in this case does seem to be a very good approach. At the
very least, importing all the different "flavors" of GEDCOMS into
Legacy will make them all compatible.... at the basic level. Then it
(seems to be) a minor step to getting the Access files you are looking
for.

Just download a copy of the free version of Legacy and give it a try.

Charlie Hoffpauir

RM: [Two replies in one].
Yes, that's the way to go. I did as Paul suggested and can see that all the
information that would ever be wanted is contained in the Legacy - Access
document - including an extra field for dates that have been converted into
a useable format, and probably other things that I haven't yet noticed. The
only snag is - I can't do anything useful with any of it yet.

I also exported the same data from Legacy as a gedcom and then converted it
to Access via the GED2CSV converter, and it only took a few seconds to get
all the Events into a "file cards" attached to each name (simply by making a
subdatasheet for the Event table).

It seems that the ideal way to go from here, is to begin tapping into the
Legacy-Access document so that eventually all Ancestors, Descendants,
Siblings and Events become Events on each 'file card'.

Ray

Dennis Lee Bieber

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Dennis Lee Bieber » 29. november 2006 kl. 18.43

On Wed, 29 Nov 2006 19:54:35 +1030, "Ray Murphy" <raymur@chariot.net.au>
declaimed the following in soc.genealogy.computing:

RM: [Two replies in one].
Yes, that's the way to go. I did as Paul suggested and can see that all the
information that would ever be wanted is contained in the Legacy - Access
document - including an extra field for dates that have been converted into
a useable format, and probably other things that I haven't yet noticed. The
only snag is - I can't do anything useful with any of it yet.

Then, if I may be so bold, you need to study two things: SQL in

detail (inner/outer joins, subselects, etc.) and the actual Legacy
schema that is in use... (maybe three things -- you may need some VBA
skills)

I also exported the same data from Legacy as a gedcom and then converted it
to Access via the GED2CSV converter, and it only took a few seconds to get
all the Events into a "file cards" attached to each name (simply by making a
subdatasheet for the Event table).

It seems that the ideal way to go from here, is to begin tapping into the
Legacy-Access document so that eventually all Ancestors, Descendants,
Siblings and Events become Events on each 'file card'.

The "ideal way", in my opinion, is not to reformulate the Legacy

tables, but to create Access "queries" (which act as "views" that can be
used in subsequent queries and reports) that will produce virtual tables
in the format you need, and use those for reporting purposes. Data
editing may need VBA as views may not be directly updatable -- the VBA
may need to take the changed "view" data and access the original Legacy
tables individually.

--
bieber.genealogy Dennis Lee Bieber
HTTP://home.earthlink.net/~bieber.genealogy/

Ray Murphy

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Ray Murphy » 30. november 2006 kl. 1.52

"Dennis Lee Bieber" <bieber.genealogy@earthlink.net> wrote in message
news:aFjbh.4489$sf5.2834@newsread4.news.pas.earthlink.net...
On Wed, 29 Nov 2006 19:54:35 +1030, "Ray Murphy" <raymur@chariot.net.au
declaimed the following in soc.genealogy.computing:

RM: [Two replies in one].
Yes, that's the way to go. I did as Paul suggested and can see that all
the information that would ever be wanted is contained in the Legacy -
Access document - including an extra field for dates that have been
converted into a useable format, and probably other things that I haven't
yet noticed.
The only snag is - I can't do anything useful with any of it yet.

Then, if I may be so bold, you need to study two things: SQL in
detail (inner/outer joins, subselects, etc.) and the actual Legacy
schema that is in use... (maybe three things -- you may need some VBA
skills)

RM: Thanks again for participating. That's the very thing I'm trying to
avoid - systematically studying SQL in detail so that I can put most queries
together as they are needed. It's apparently no mean feat, getting a handle
on all that stuff and then remembering it. I manage to get by with VBA (and
VB) though.
All you guys who can do it easily ought to join Mensa!

I also exported the same data from Legacy as a gedcom and then converted
it to Access via the GED2CSV converter, and it only took a few seconds to
get all the Events into a "file cards" attached to each name (simply by
making a subdatasheet for the Event table).

It seems that the ideal way to go from here, is to begin tapping into the
Legacy-Access document so that eventually all Ancestors, Descendants,
Siblings and Events become Events on each 'file card'.

The "ideal way", in my opinion, is not to reformulate the Legacy
tables, but to create Access "queries" (which act as "views" that can be
used in subsequent queries and reports) that will produce virtual tables
in the format you need, and use those for reporting purposes. Data
editing may need VBA as views may not be directly updatable -- the VBA
may need to take the changed "view" data and access the original Legacy
tables individually.

RM: Yes, normally that's the way to go alright - use Access the way it's
supposed to be used, but in this case Access won't be used
regularly as a database for the retrieval of data.
It's only being used once for each gedcom (or Legacy-Access file) for the
sole purpose of deciphering it and *spreading out* all the
required data into human and VB readable text -- with the same method
for every file, which means I only need to learn how to write a limited
number of queries

I want to import single files and then query them in multiple ways
until all Ancestors, Descendants, Spouses, Children and Events are
all placed on an "Events file card" attached to each name and then
export the whole lot in csv format and then dump the original data.

The routine will be this:
* Locate gedcoms that can be legitimately used.
* Import gedcoms into Legacy (to achieve maximum output).
* Import each resulting Legacy MDB file into the new program
* Fill up the 'file cards' with Events via queries and looping-writes.
* Delete everything without exact dates via manual select-delete.
* Delete unwanted Events in large slabs via manual select-delete.
* Export 2 csv files containing everything -- Individuals & Events
.. .(each with generated unique alpha-numeric ID's).
* Filter infinitely in a VB program by the read-write method and
.. .then use the output csv's for research work.

So for an easy file, the Access program would only see the data (that
originated from any style of gedcom) for half an hour.
Import -- Process -- Export -- Dump and start again with the
next Legacy MDB.

The exported csv files could later be imported into an existing
(bloated but fast) Access MDB if VB6 filtering was too slow, but if
genetics was the focus, then large families need to be kept separate.

--
bieber.genealogy Dennis Lee Bieber

Ray

Paul Blair

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Paul Blair » 30. november 2006 kl. 3.12

Ray Murphy wrote:
"Dennis Lee Bieber" <bieber.genealogy@earthlink.net> wrote in message
news:aFjbh.4489$sf5.2834@newsread4.news.pas.earthlink.net...
On Wed, 29 Nov 2006 19:54:35 +1030, "Ray Murphy" <raymur@chariot.net.au
declaimed the following in soc.genealogy.computing:

RM: [Two replies in one].
Yes, that's the way to go. I did as Paul suggested and can see that all
the information that would ever be wanted is contained in the Legacy -
Access document - including an extra field for dates that have been
converted into a useable format, and probably other things that I haven't
yet noticed.
The only snag is - I can't do anything useful with any of it yet.

Then, if I may be so bold, you need to study two things: SQL in
detail (inner/outer joins, subselects, etc.) and the actual Legacy
schema that is in use... (maybe three things -- you may need some VBA
skills)

RM: Thanks again for participating. That's the very thing I'm trying to
avoid - systematically studying SQL in detail so that I can put most queries
together as they are needed. It's apparently no mean feat, getting a handle
on all that stuff and then remembering it. I manage to get by with VBA (and
VB) though.
All you guys who can do it easily ought to join Mensa!

I also exported the same data from Legacy as a gedcom and then converted
it to Access via the GED2CSV converter, and it only took a few seconds to
get all the Events into a "file cards" attached to each name (simply by
making a subdatasheet for the Event table).

It seems that the ideal way to go from here, is to begin tapping into the
Legacy-Access document so that eventually all Ancestors, Descendants,
Siblings and Events become Events on each 'file card'.

The "ideal way", in my opinion, is not to reformulate the Legacy
tables, but to create Access "queries" (which act as "views" that can be
used in subsequent queries and reports) that will produce virtual tables
in the format you need, and use those for reporting purposes. Data
editing may need VBA as views may not be directly updatable -- the VBA
may need to take the changed "view" data and access the original Legacy
tables individually.

RM: Yes, normally that's the way to go alright - use Access the way it's
supposed to be used, but in this case Access won't be used
regularly as a database for the retrieval of data.
It's only being used once for each gedcom (or Legacy-Access file) for the
sole purpose of deciphering it and *spreading out* all the
required data into human and VB readable text -- with the same method
for every file, which means I only need to learn how to write a limited
number of queries

I want to import single files and then query them in multiple ways
until all Ancestors, Descendants, Spouses, Children and Events are
all placed on an "Events file card" attached to each name and then
export the whole lot in csv format and then dump the original data.

The routine will be this:
* Locate gedcoms that can be legitimately used.
* Import gedcoms into Legacy (to achieve maximum output).
* Import each resulting Legacy MDB file into the new program
* Fill up the 'file cards' with Events via queries and looping-writes.
* Delete everything without exact dates via manual select-delete.
* Delete unwanted Events in large slabs via manual select-delete.
* Export 2 csv files containing everything -- Individuals & Events
. .(each with generated unique alpha-numeric ID's).
* Filter infinitely in a VB program by the read-write method and
. .then use the output csv's for research work.

So for an easy file, the Access program would only see the data (that
originated from any style of gedcom) for half an hour.
Import -- Process -- Export -- Dump and start again with the
next Legacy MDB.

The exported csv files could later be imported into an existing
(bloated but fast) Access MDB if VB6 filtering was too slow, but if
genetics was the focus, then large families need to be kept separate.

--
bieber.genealogy Dennis Lee Bieber

Ray


There's another thing to think of, if I may.

If you are going to get Fred's GEDCOM only once, then, after import to
Legacy, I'd rehash the tables to suit my purpose. It makes writing
queries a lot plainer.

But if Fred's GEDCOM will be part of an ongoing update process, dasn't
muck wuth redoing tables - you will spend time doing legwork where it
returns little profit to you. Sharpen your query-writing skills instead.

Paul

Dennis Lee Bieber

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Dennis Lee Bieber » 30. november 2006 kl. 6.47

On Thu, 30 Nov 2006 11:19:48 +1030, "Ray Murphy" <raymur@chariot.net.au>
declaimed the following in soc.genealogy.computing:


RM: Thanks again for participating. That's the very thing I'm trying to
avoid - systematically studying SQL in detail so that I can put most queries
together as they are needed. It's apparently no mean feat, getting a handle
on all that stuff and then remembering it. I manage to get by with VBA (and
VB) though.
All you guys who can do it easily ought to join Mensa!

My suggestion was to work out "ONE TIME" the SQL to create a virtual

view of the data (that is, an SQL query that would create the table
format you wish to use in the future) that can be then used in the
future as the source for querying; then save that particular SQL.
--
bieber.genealogy Dennis Lee Bieber
HTTP://home.earthlink.net/~bieber.genealogy/

Ray Murphy

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Ray Murphy » 1. desember 2006 kl. 11.44

"Dennis Lee Bieber" <bieber.genealogy@earthlink.net> wrote in message
news:Rfubh.5567$1s6.1835@newsread2.news.pas.earthlink.net...
On Thu, 30 Nov 2006 11:19:48 +1030, "Ray Murphy" <raymur@chariot.net.au
declaimed the following in soc.genealogy.computing:



RM: Thanks again for participating. That's the very thing I'm trying to
avoid - systematically studying SQL in detail so that I can put most
queries
together as they are needed. It's apparently no mean feat, getting a
handle
on all that stuff and then remembering it. I manage to get by with VBA
(and
VB) though.
All you guys who can do it easily ought to join Mensa!

My suggestion was to work out "ONE TIME" the SQL to create a virtual
view of the data (that is, an SQL query that would create the table
format you wish to use in the future) that can be then used in the
future as the source for querying; then save that particular SQL.
--
bieber.genealogy Dennis Lee Bieber

RM: I'm still at the starting line - trying to figure out how to put both
parents ID's in the Individual's Table. Everything is working fine if I
enter them manually for the first generation. After that, all generations
mysteriously follow suit.

Ray

Gjest

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Gjest » 1. desember 2006 kl. 21.28

Ray Murphy wrote:

Hi All,

I've converted a gedcom file to an MS Access database via the GED2CSV
program and got some good things happening with it, but I haven't got a clue
how to connect people with relatives. For example, I can see in the
'Relations table' where married couples are 'connected', but I can see no
connections between children and parents, and grandparents etc.

Any ideas?

Thanks

Ray

Hi Ray,

I'm the author of Ged2Csv, so I'm quite familiar with the output
format. I'd me more than happy to help you with the SQL you'll need to
connect all the various bits of information. If you are familiar with
PHP, you should be able to pick out what you need from this file:
http://www.infused-solutions.com/softwa ... ctions.php

If you need more help than that send me an email at
keithm@infused-solutions.com.

Cheers,
Keith Morrison
http://www.infused-solutions.com

Ray Murphy

Re: Finding Family Relationships in Access genealogy dbase

Legg inn av Ray Murphy » 1. desember 2006 kl. 22.27

<keithm@infused.org> wrote in message
news:1165004894.729982.54380@n67g2000cwd.googlegroups.com...
Ray Murphy wrote:

Hi All,

I've converted a gedcom file to an MS Access database via the GED2CSV
program and got some good things happening with it, but I haven't got a
clue
how to connect people with relatives. For example, I can see in the
'Relations table' where married couples are 'connected', but I can see no
connections between children and parents, and grandparents etc.

Any ideas?

Thanks

Ray

Hi Ray,

I'm the author of Ged2Csv, so I'm quite familiar with the output
format. I'd me more than happy to help you with the SQL you'll need to
connect all the various bits of information. If you are familiar with
PHP, you should be able to pick out what you need from this file:
http://www.infused-solutions.com/softwa ... ctions.php

If you need more help than that send me an email at
keithm@infused-solutions.com.

Cheers,
Keith Morrison
http://www.infused-solutions.com

RM: I'm stumped for words :-))
I hadn't heard of PHP, but I'll arrange to adapt & use some of the extremely
helpful code and also find out how to use PayPal before the trial period
expires for this incredibly fast and user friendly converter.

Thanks a lot.

Ray

Svar

Gå tilbake til «soc.genealogy.computing»