Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 35
  1. #16
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Povo,

    I have created a database based on your excel sheets and have included it in the attached zip.
    1 table for each sheet. I modified the names to make Access (happy -less syntax critical).
    Most fields are text data type, except the long notes that was set to memo and some Day Month that are double.
    I let Access assign a PK for each table, but it has no real relevance to you at this point.
    I have included a module and table that I use to show tables and fields with attributes (listed below).
    I have mixed use of FirstName FName and LastName Surname LName (but I'm sure you'll recognize these).

    Errata:
    I do see I have misnamed a field FLName in the Deaths table. It should be LName.
    In Marriages BribeFName should be BrideFName (73)
    EntryID table_name field_name ordinal_position data_type length default
    1 Births BDay 0 Text 255
    2 Births BMth 1 Text 255
    3 Births BYer 2 Text 255
    4 Births BapRegDay 3 Text 255
    5 Births BapRegMth 4 Text 255
    6 Births BapRegYer 5 Text 255
    7 Births Typ 6 Text 255
    8 Births Co 7 Text 255
    9 Births FirstName 8 Text 255
    10 Births Surname 9 Text 255
    11 Births AddrLoc 10 Text 255
    12 Births ChurchParish 11 Text 255
    13 Births FatherFName 12 Text 255
    14 Births FatherLName 13 Text 255
    15 Births FatherOcc 14 Text 255
    16 Births MotherFName 15 Text 255
    17 Births MotherLName 16 Text 255
    18 Births Spon1FName 17 Text 255
    19 Births Spon1LName 18 Text 255
    20 Births Spon2FName 19 Text 255
    21 Births Spon2LName 20 Text 255
    22 Births Spon3FName 21 Text 255
    23 Births Spon3LName 22 Text 255
    24 Births Notes 23 Text 255
    25 Census ID 0 Long 4
    26 Census From 1 Double 8
    27 Census To 2 Double 8
    28 Census Co 3 Text 255
    29 Census FirstName 4 Text 255
    30 Census LastName 5 Text 255
    31 Census Residence 6 Text 255
    32 Census AdditionalNotes 7 Text 255
    33 Census Event Place 8 Text 255
    34 Census Source 9 Text 255
    35 Census DateRef 10 Text 255
    36 Census ListDetailsExplanation 11 Text 255
    46 Deaths ID 0 Long 4
    47 Deaths DeathBurialDay 1 Text 255
    48 Deaths DeathBurialMth 2 Text 255
    49 Deaths DeathBurialYer 3 Text 255
    50 Deaths Typ 4 Text 255
    51 Deaths Co 5 Text 255
    52 Deaths FName 6 Text 255
    53 Deaths FLName 7 Text 255
    54 Deaths Age 8 Text 255
    55 Deaths AddressLocation 9 Text 255
    56 Deaths Occupation 10 Text 255
    57 Deaths ParishDistrict 11 Text 255
    58 Deaths Notes 12 Text 255
    59 Marriages ID 0 Long 4
    60 Marriages MDay 1 Double 8
    61 Marriages MMth 2 Double 8
    62 Marriages MYear 3 Double 8
    63 Marriages Type 4 Text 255
    64 Marriages Co 5 Text 255
    65 Marriages GroomFName 6 Text 255
    66 Marriages GroomLName 7 Text 255
    67 Marriages GroomAge 8 Text 255
    68 Marriages GroomCdn 9 Text 255
    69 Marriages GroomLocation 10 Text 255
    70 Marriages GroomOccupation 11 Text 255


    71 Marriages GroomFather 12 Text 255
    72 Marriages GroomFatherOccupation 13 Text 255
    73 Marriages BribeFName 14 Text 255
    74 Marriages BrideLName 15 Text 255
    75 Marriages BrideAge 16 Text 255
    76 Marriages BrideCdn 17 Text 255
    77 Marriages BrideLocation 18 Text 255
    78 Marriages BrideOccupation 19 Text 255
    79 Marriages BrideFatherFName 20 Text 255
    80 Marriages BrideFatherLName 21 Text 255
    81 Marriages BrideFatherOccupation 22 Text 255
    82 Marriages Church 23 Text 255
    83 Marriages Witness1Forename 24 Text 255
    84 Marriages Witness1Surname 25 Text 255
    85 Marriages Witness2Forename 26 Text 255
    86 Marriages Witness2Surname 27 Text 255
    87 Marriages Srce 28 Text 255
    88 Marriages SourceDate 29 Text 255
    89 Marriages FullDescription 30 Memo 0
    [/code]
    Attached Files Attached Files

  2. #17
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I made the fieldname changes in my copy, then imported the universal search material.
    I'm including a screen shot but I don't think it would be of much value--- but then again we're only looking at a small ample of your data and uncertain of what you expect.

    Attachment 31331

  3. #18
    Povo is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    14
    Hi, this looks good, thanks. I don't have time to properly review tonight. I'll get back to you about this time tomorrow.

  4. #19
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453
    Hi

    in reality I have been working on the same sort of thing for around 10 months ( with a lot of help from the forum) and I can assure you that whilst in realty it is simple it can get quite complicated. If you want to use it as a learning curve carry on but if you want I am more than willing to send you a copy of my database

    Ian

  5. #20
    Povo is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    14
    Davegri/All,

    Ok, how far can I take the queries?

    For example, I created a query to search the Births table for someone called James Wilson (looking at either child’s name or the father’s name):

    Click image for larger version. 

Name:	Births search.png 
Views:	22 
Size:	12.9 KB 
ID:	31339


    The results are as I’d hoped for.

    Next, as an experiment, I added a James Wilson to my deaths table. I amended my query to look for the name in both tables as below.

    Click image for larger version. 

Name:	Birth + Death.png 
Views:	22 
Size:	10.8 KB 
ID:	31340


    This seems to really confuse Access and the results are jumbled and repeated:

    Click image for larger version. 

Name:	Results 2.png 
Views:	22 
Size:	40.6 KB 
ID:	31341


    Would I be right in saying that this is because the tables are not linked? Since I don’t want to link the tables (unless someone can advise me on a clever way of doing it), how can I get around this problem?

  6. #21
    Povo is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    14
    Orange,

    Thanks very much for this.

    Why do the dates go in as “Double” format – what does that mean?

    Would it be better to have the Census “ListDetailsExplanation” as “Memo” too or is it ok as text since it is limited to about 100 characters?

    Where have you hidden the ID column in your Births table?



    Ian,

    Eek, I’ve no particular desire to 10 months trying to get this sorted, especially if you’ve already got one done! I’m sure your layout will be slightly different to what I’ll end up with, but I’m sure if I had access to yours I could use your design to build my own. So yes, I’d be very grateful if you could send me a copy. Thanks for the offer.

  7. #22
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Date/time values are actually stored as a Double data type. The structure you see is due to formatting. Access applies a default format when field is created.

    Avoid Memo fields whenever possible.

    I doubt the ID field is hidden, assuming it exists in the table, but it does not appear to have been retrieved in query.

    Query without join is a Cartesian query - every record of each table associates with every record of other table.

    Are there death records where there is no corresponding individual in births table?

    However, why two tables? Have a table of people with their birth and death data. Yes, death data fields will be blanks for a lot of records but they should eventually get filled in and if not, so what?

    It is a balancing act between data normalization and ease of data entry/output. Normalize until it hurts and denormalize until it works.
    Last edited by June7; 11-21-2017 at 01:21 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #23
    Jen0dorf is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    UK
    Posts
    453
    Hi

    OK I've cleaned down my database to show just marriages, and I've left 600 records to give you a taste of how it works.

    It lists all marriage details

    You need to look at the table spec and then import your excel sheets into the table making sure of the format.

    The search is by forename, surname, date and soundex. etc

    Its been developed on the fly so to speak and I'm sure it can and will be improved on. But I developed it from scratch for nothing and with a Lot and I do mean a lot of help from the forum it works.

    If you have any questions I'm here. You need to send me an email address and I'll send you a download link

    cheers

    Ian

  9. #24
    Povo is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    14
    June,

    None of the death records have a corresponding birth record and none of the marriage records have a corresponding death record, etc etc.

    This data is not from a family tree – it is from various semi-random primary sources like newspapers and church registers. Genealogists tend to gather up tonnes of information and 99% of it will never be relevant to what they are researching.

    I could add a birth record to my database for someone who I currently have no known connection to … and a year later I may discover that they were married to the 3rd cousin of a 5th cousin twice removed of my aunt’s brother-in-law. So when I’ve got a new name to add to my tree I may want to do a quick search of my database to see if I’ve already got any records for that person.

    Ian,

    Sounds great, thanks. I’ll send you a PM.

  10. #25
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    June has described Access dates. I let the import from Excel to Access determine the field data types.
    Access can store text up to 255 chars in a text datatype field. Beyond 255 chars, you either split the field into multiple test fields, or use the memo default (long text). Apparently I didn't include an ID field in Births??
    The ID fields that are included in the database were inserted during import based on my selection of options.

    To get yourself prepared to question your tables, you should watch some youtube videos related to queries.
    Here are a few:
    https://www.youtube.com/watch?v=3RVoxLd7tEk
    https://www.youtube.com/watch?v=jM_O-JopORM
    https://www.youtube.com/watch?v=8l5I1Wc1WPI

  11. #26
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    RE: Post #20. "Name" is a reserved word in Access and shouldn't be used as an object name.

    June explained why you have so many records returned (Cartesian query) (Image "Birth + Deaths.png")......
    You might be able to use a Union query to get the records you want.

  12. #27
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  13. #28
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Yes, a UNION query is what you need. This will arrange the data into a single dataset to search which is what you should probably have to begin with.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #29
    Povo is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    14
    Thanks for all the tips. I watched the videos and I’ve read up on Union queries.

    So, I’ve created a query for my Birth and Death tables which returns the person’s first and last names, and the year. I then made a Union query for them which basically then lists all the people in the two tables.

    The next step in Microsoft’s instructions was to use my Union query to create a Union table. I tried that but then found it didn’t seem to update properly when I updated one of the original tables and clicked refresh.

    Instead I just queried the Union query which seems to give the desired result. However, this is three layers of queries – is there a simpler way or does that sound about right?

  15. #30
    Povo is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    14
    Another question … I want to add in a field to each table which says what type of record it is so I can make it appear in my query so that I know where to look for the original record. Can I make the field autofill the same text for each new record in that table? If I then hide that field in the table will it carry on working?

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need help designing a database
    By Aviator in forum Database Design
    Replies: 2
    Last Post: 03-15-2016, 05:24 PM
  2. Replies: 2
    Last Post: 07-31-2014, 05:45 AM
  3. database designing
    By tommyried in forum Database Design
    Replies: 1
    Last Post: 02-27-2014, 12:41 PM
  4. Designing Form (Beginner)
    By ccordner in forum Forms
    Replies: 1
    Last Post: 01-17-2012, 12:02 PM
  5. Help with designing database
    By nimalp in forum Access
    Replies: 6
    Last Post: 09-15-2010, 10:34 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