Results 1 to 3 of 3
  1. #1
    orangemath08 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2017
    Posts
    2

    Weird table join...

    Hello - I apologize if this has been asked/answered before but I'm not finding it and am having trouble even wording the type of query I want. Below is an example of what I'm trying to achieve.




    Table Data Table Date Periods
    C1 C2 C3 D1 D2 D3
    Person 1 7 200 Year 1 1 360
    Person 2 249 1330 Year 2 361 720
    Person 3 721 1750 Year 3 721 1080
    Year 4 1081 1440
    Year 5 1441 1800
    Query Results
    C1 C2 C3 D1
    Person 1 7 200 Year 1
    Person 2 249 1330 Year 1
    Person 2 249 1330 Year 2
    Person 2 249 1330 Year 3
    Person 2 249 1330 Year 4
    Person 3 721 1750 Year 3
    Person 3 721 1750 Year 4
    Person 3 721 1750 Year 5


    Now I'll try in words: I want to add a record to 'Table Data' for every record in 'Table Date Periods' starting where C2 is between D2 and D3 and ending where C3 is between D2 and D3.

    Not sure if this makes sense but any help would be very much appreciated and I'm happy to try to clarify.

    Thanks!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Consider:

    Query1:

    SELECT Data.C1, Data.C2, Data.C3, DLookUp("D1","DatePeriods",[C2] & " BETWEEN D2 AND D3") AS MinYr, DMax("D1","DatePeriods",[C3] & " BETWEEN [d2] AND [D3]") AS MaxYr
    FROM Data;

    Query2:

    SELECT Query1.C1, Query1.C2, Query1.C3, Query1.MinYr, Query1.MaxYr, DatePeriods.D1, DatePeriods.D2, DatePeriods.D3
    FROM DatePeriods, Query1
    WHERE (((DatePeriods.D1) Between [MinYr] And [MaxYr]));

    If year values will grow to include multiple digits, will need placeholder zeros (ex: Year 001, Year 010, Year 100) or convert field to a number type and exclude the word 'Year'.

    Advise no spaces or punctuation/symbols (with exception of underscore) in any names.
    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.

  3. #3
    orangemath08 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2017
    Posts
    2
    Thanks June7! This worked and will be a big help to me.

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

Similar Threads

  1. Replies: 3
    Last Post: 12-28-2016, 10:15 PM
  2. Join A Field In One Table to Two Fields In Another Table
    By Aaron5714 in forum Database Design
    Replies: 3
    Last Post: 05-10-2014, 02:56 PM
  3. Table Join Help
    By Jonesy in forum Database Design
    Replies: 2
    Last Post: 09-13-2013, 11:35 AM
  4. join table
    By graciemora in forum Queries
    Replies: 2
    Last Post: 10-28-2010, 01:57 PM
  5. Self Join in a Table
    By LornaM in forum Database Design
    Replies: 10
    Last Post: 05-06-2009, 10:29 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