Results 1 to 14 of 14
  1. #1
    PipSqueek is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    7

    MSAccess Genealogy DB - help!

    Hi



    I used to know MSAccess but it is a fair few years and I am a bit lost now. Basically, I need a simple genealogy database so I decided to start with Microsoft's old Genealogy one (that can be found online as 01076524). It seems to be what I need except for the reports. I would like to be able to make the "individuals form" into a report. Obviously the form has unbound fields and I just can't find a way to create a report showing the same information.

    Any suggestions? Or any better alternatives? I do have a GED file which I have imported and I do have the info on an app but I wanted to find a way to make physical index cards that I can show my parents (ie. print out the forms to stick on the cards with all the individual data including parents, spouse and children). I could do with more information being recorded too but I think that is just a simple process of adding more fields to the individuals table).

    I appreciate any help as I am struggling at the moment. I was self taught in MSAccess in the first place and without the manual I don't know where to start.

    Thank you.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you need the tPerson table
    PersonID
    FirstN
    LastN
    BirthDate
    DeathDate
    etc..
    MarryID (from table below to show who's parents of this person)


    and the tMarriage table
    MarryID
    HusbID (from tPerson )
    WifeID (from tPerson ) or use Spouse1,2 in this PC climate
    MarryDate
    MarryPlace
    DivorceDate
    etc...

    build form & reports from this.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Can you give a download link for the MS template or is it listed as a template in one of the versions of Access?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    PipSqueek is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    7
    Thank you Ranman... so are you saying to ditch the old MSAccess template as the only tables there are individuals and families? I did try starting from scratch by entering some basic data and letting Access sort itself out, but that seemed to be a more complicated structure (I can add it here if you like). I guess I am saying that the old template seems to be able to link all the info (as per the form) but I don't know how to make that into a report. I only want a basic DB (unless someone has a complicated one they can offer me ;-) ) Also when I worked in Access you could do one-to-many relationships but I really can't see that any more, no matter how I arrange things.

    And if you are saying that is a new structure (your example) how would that track children? I know... stupid questions, but it has been so long. I just need some guidance (or maybe a lot!)

  5. #5
    PipSqueek is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    7
    I will try ridder.. bear with me :-) It is an old outdated version (not sure how old but .mdb)

    I can't quickly find the .mdb but this is the link to the .accdb file (which I thought might be easier to work with being nearer to my version of Access).

    https://www.devhut.net/2015/10/27/ms...logy-database/
    Last edited by PipSqueek; 10-10-2018 at 12:46 PM. Reason: Additional information

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Thanks
    I've downloaded the ACCDB file from the Devhut site and had a quick look at it.
    I would suggest you scrap the old switchboard form and related table. The form can only have 8 menu items.
    Replace with your own menu form.

    Suggest you create a query with the fields you want in your report. Post the SQL if you need help with creating the report.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    PipSqueek is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    7
    Umm... thank you ridder but did I not say I haven't played with Access for years? So I was ignoring the switchboard, but the individuals had all the info I needed. But is the basic structure enough? I have tried creating a query from the fields but it doesn't seem to work (as I said one to many etc seems to not be there any more) to make a report.

    Should I just ignore this MSAccess created DB and start again (as I said I have a new database where I entered info and let Access work it out)? I am way behind the knowledge of you guys but I used to do this! I never set a DB up though so that is where I am getting confused. Thank you again for any help.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by PipSqueek View Post
    Umm... thank you ridder but did I not say I haven't played with Access for years? So I was ignoring the switchboard, but the individuals had all the info I needed. But is the basic structure enough? I have tried creating a query from the fields but it doesn't seem to work (as I said one to many etc seems to not be there any more) to make a report.

    Should I just ignore this MSAccess created DB and start again (as I said I have a new database where I entered info and let Access work it out)? I am way behind the knowledge of you guys but I used to do this! I never set a DB up though so that is where I am getting confused. Thank you again for any help.
    OK - bear in mind I've only just downloaded this & I've never used access for a family tree database.
    As you haven't used Access for many years, I would adapt this database rather than start again.
    Leave replacing the switchboard form for now - that can wait ...

    The structure is in some ways close to what you want BUT there are some MAJOR issues. If this was an MS template ... its terrible!

    Individuals table
    - no PK field! Make ID the PK field
    - remove spaces from ALL field names & use CamelCase e.g. BirthDate
    - Sex (suggest rename as Gender) - reduce field length to 1 and use M/F ... NOT 0/1 as it is now
    - all date fields - change to Date datatype - currently Text
    - full name - scrap it as its duplicated data - get full name in queries by combining given name & surname (rename as FirstName / LastName) - reduce field sizes - 20 is probably more than enough
    - add title field

    Families table
    -
    remove spaces from ALL field names & use CamelCase
    - MarriageDate - change to Date field
    - FamilyID & GEDFamilyID - don't see any reason for using both - just use FamilyID - if the F prefix is useful add it to FamilyID (if not adjust Parents data in Individuals table)
    - consider issues like civil partnerships / divorce etc

    I've made those changes in the attached for you to consider
    You DO have a one to many relationship between the 2 tables & you can make additional joins using queries
    I've also added a subdatasheet on the Families table which you may or may not want - easy to remove if you find it confusing

    I also made some new queries based on these as a starting point.
    Suggest you add some data to check the structure works for you. I don't have a GED file available to test this myself
    They aren't perfect & once you add real data with several generations you will start to see any issues

    The old query & report will no longer work - renames with OLD suffix

    Hope this gets you started

    Someone else may come along with better ideas - especially if they've done this before
    For example, Ssanfu is particularly good at modelling this type of task

    Note that you can purchase ready made databases for this task. It may be worth doing so as family relationships can get very complex
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    PipSqueek is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    7
    Thanks Ridders. I will unzip and have a look. I do believe this was created by MS but have to agree that it looks like very strange and unprofessional. I agree with most of your points except I think the date fields need to be text because with genealogy you are never totally sure of the exact date (eg. BirthDate Jan-Mar 1899) and I am guessing the GED ID is there because they have the import GED File function.

    I guess I thought the one to many wasn't there as I was used to seeing the eternity symbol by the tables. I will check out your file and see if it helps.

    As I say, I do have genealogy programme which as all my data in, but I wanted something that I could manipulate to print out information that my parents can easily understand (i.e. index cards).

    Thank you so much for taking the time to help me with this. If I have more questions, I hope you don't mind me coming back to ask.

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Good point re date fields. The trouble is you won't be able to sort by date as e.g. April 2010 will be sorted before Jan 1899 if its a text field.

    The Devhut site indicated it was an old MS template which had been updated to ACCDB by an experienced developer.
    The lack of a PK field and the peculiar use of 0/1 for M/F was very odd

    Feel free to come back if you wish though you may need to supply example data if there are questions about structure
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    PipSqueek is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    7
    Quote Originally Posted by ridders52 View Post
    Good point re date fields. The trouble is you won't be able to sort by date as e.g. April 2010 will be sorted before Jan 1899 if its a text field.

    The Devhut site indicated it was an old MS template which had been updated to ACCDB by an experienced developer.
    The lack of a PK field and the peculiar use of 0/1 for M/F was very odd

    Feel free to come back if you wish though you may need to supply example data if there are questions about structure
    Thank you Ridders. Yes I sent you a link to the "modernised" database because I couldn't find the original but it is exactly the same as the .mdb.

    Obviously, none of the forms are working now on yours so I assume I just need to update the code to the new field names on the forms I want (though that is really only the individuals form). Also the GEDimport has stopped working but I can't work out why. I wanted to GEDimport because I do have another tree but it won't give me the "clean looking" basic individual report I need for my parents. I have now realised the GEDImport doesn't notice if it is duplicating a record - is there anyway for the module to address that or do I just use a "find duplicates" function?

    I am so out of date so feel free to start ignoring me as this is a steep learning curve... if only I wasn't so honest, else I would have stolen the office copy of the manual :-)

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by PipSqueek View Post
    Thank you Ridders. Yes I sent you a link to the "modernised" database because I couldn't find the original but it is exactly the same as the .mdb.

    Obviously, none of the forms are working now on yours so I assume I just need to update the code to the new field names on the forms I want (though that is really only the individuals form). Also the GEDimport has stopped working but I can't work out why. I wanted to GEDimport because I do have another tree but it won't give me the "clean looking" basic individual report I need for my parents. I have now realised the GEDImport doesn't notice if it is duplicating a record - is there anyway for the module to address that or do I just use a "find duplicates" function?

    I am so out of date so feel free to start ignoring me as this is a steep learning curve... if only I wasn't so honest, else I would have stolen the office copy of the manual :-)
    Hi

    I didn't bother updating the forms or reports but these should work fine once the field names are updated.
    The Full Name field control will need to be deleted or its control source replaced with = FirstName & " " & LastName

    Now that both tables have a primary key field it should be easy enough to prevent records being duplicated.
    The ID field is just the GED_ID minus the 'I' prefix

    If you can supply me with a small sample GED file I'll have a look at the import function in the next day or two
    However, I don't want to spend too long on this until I know it is fit for purpose in terms of table structure
    As already mentioned, family trees can get very complicated

    There are various other examples available online.
    For example look at the models in Barry Williams site: http://www.databaseanswers.org/data_...logy/index.htm

    You should read the comments here: http://www.fabalou.com/Access/Genera...gy_example.asp
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    PipSqueek is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    7
    Thank you - I don't want you to spend too much time either. This was supposed to be a simple question. I will look at your links in the morning but thank you for taking the time so far as I can see your structure is much better than the original MS template and I feel I can make it work (in time). I will see tomorrow if I can copy the ged. Is there a way to PM you or should I just put it on here? The information I have is not genetically linked to me (though it is important to me). If I have to zip the file then it may take longer.

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by PipSqueek View Post
    I will see tomorrow if I can copy the ged. Is there a way to PM you or should I just put it on here? The information I have is not genetically linked to me (though it is important to me). If I have to zip the file then it may take longer.
    If you're happy for it to be posted so anyone can view it, do so in this thread.
    However, it might be better for now to restrict the info - you can PM me but attachments aren't allowed in a PM
    That was why I suggested emailing me instead using the link in my signature line.

    Either way, you will need to zip it. Just right click in Explorer and send to a compressed folder
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Beginner Designing Genealogy Database
    By Povo in forum Access
    Replies: 34
    Last Post: 10-21-2019, 09:48 AM
  2. Replies: 2
    Last Post: 11-04-2017, 05:06 PM
  3. Learn MSAccess by playing MSAccess Jeopardy
    By pkstormy in forum Sample Databases
    Replies: 4
    Last Post: 11-17-2016, 07:27 AM
  4. Oracle to MSACCESS
    By karankukreja in forum Access
    Replies: 1
    Last Post: 03-24-2011, 08:20 AM
  5. MSACCESS Query
    By saa18 in forum Access
    Replies: 0
    Last Post: 11-14-2008, 05:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums