Results 1 to 5 of 5
  1. #1
    mscale is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    3

    Linking dates between tables with a +/- possibility in query

    I'm a student working on the creation of a historical database and I'm pretty new to Access, so appreciate any help.

    I have to link records together by the age of individuals in two sources (Victorian census returns). One source recorded ages rounded to 5 (i.e. 5 or 0 endings), while the other recorded ages precisely (more or less - ages don't always stay consistent between census returns because they were often reported by other people). I need to create means of linking people together correctly between the two sources allowing for the ages to be + or - 5 years. I have each census return in a separate table, and have converted the ages in each return to a birth year by subtracting the age from the census year (=[census year]-[age]). How do I add the possibility of the birth year being + or - 5 and still achieve the correct link when I search between the two tables (e.g. so an individual who is recorded as 30 in 1841 - birth date 1811 - is successfully linked to the same individual who comes out as 48 in 1861 - birth date 1813)?

    Thank you -
    Michelle

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    You can try something like
    Code:
    SELECT ... FROM table1 t1 INNER JOIN table1 t2 ON t2.LastName = t1.LastName AND t2.BirthYear BETWEEN t1.BirthYear-5 AND t1.BirthYear+5 ...
    This returns persons present in both tables (all persons not present in both tables are left out). When one table is more presentable compared with other, then you have to use LEFT JOIN instead of INNER JOIN. For people not found in 2nd table all fields from 2nd table will then be Null.
    And relying only on birth year will probably give a lot of wrong info - there must be something other used to link tables too (e.g. last name in my example)

  3. #3
    mscale is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    3
    Thank you for the suggestion.

    And relying only on birth year will probably give a lot of wrong info - there must be something other used to link tables too (e.g. last name in my example)[/QUOTE]

    Yes, I have both a full last name and a standardized last name field which are the other main potential linkages between the two tables. The reason the birth year is important is that there are a lot of duplicated names within families - need to be able to tell the fathers from the sons, etc!

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    He/She already gave you the answer. Instead of just linking on last name, you also connect on first name, it's just a minor addition to the query originally given.

  5. #5
    mscale is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    3
    Quote Originally Posted by rpeare View Post
    He/She already gave you the answer. Instead of just linking on last name, you also connect on first name, it's just a minor addition to the query originally given.
    Yes, that sometimes works, but you don't always want to link on first name: sometimes it isn't given, or it is spelled weirdly, or a person's name is incorrectly recorded (like a John turns into a Joshua and then back to John in a later record), or there is just an initial. Standardized forenames can help, in addition to the standardized last names. The year of birth data point can be quite critical.

    Thanks -
    Michelle

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

Similar Threads

  1. Replies: 3
    Last Post: 09-07-2017, 02:07 PM
  2. Make Table Query - giving multiple name possibility
    By huntclub in forum Database Design
    Replies: 2
    Last Post: 05-27-2014, 12:11 PM
  3. Linking tables by query?
    By Newbie11 in forum Queries
    Replies: 2
    Last Post: 04-05-2012, 11:39 AM
  4. Query two tables for specified dates
    By funkygoorilla in forum Queries
    Replies: 2
    Last Post: 12-12-2011, 11:12 AM
  5. Replies: 0
    Last Post: 03-04-2011, 10:28 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