Results 1 to 4 of 4
  1. #1
    Pelato1980 is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Posts
    2

    Help request query for DB with teacher, year and school in Access 2007


    Good evening everybody, sorry for my horrible english, i'm newbie in this forum, i hope that someone can help me.
    I had a table similar this (data fictitious):
    Year Name School
    2017-18 John Smith J.F. Kennedy
    2016-17 John Smith A. Lincoln
    2016-17 Paul Brown M.L. King

    I'd like to create a query that returns me.... every record for year 2017-18 (this is simple).... my real difficulty... is that i want also every record of previous year but only for teachers actives in current year.... and not for teacher inactive in current year.... so for this table the result should be.... only first 2 records realtives to John Smith.
    Thank you so much for everyone will help me.
    Bye

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    in a form you have a combo box to pick a year.
    make a text box to hold the prev-year, when user picks the current yr = txtPrevYr = cboYr-1 (or however you calc it)

    make qs1Current to pull all current teachers ,current year. (forms!frmMain!cboYr)
    make qs2Prev to pull all teachers, of Prev Year : (forms!frmMain!txtPrevYr)
    make Q3 to join qs1Current and qs2Prev on teacher

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Ranman's approach will work. Be sure to make the join "Include all records from qs1Current and only those from qs2Prev that match". All in one statement like:

    SELECT qs1Current.Year, qs1Current.Name, qs1Current.School
    FROM
    (SELECT [Year], [Name], School FROM Table2 WHERE Table2.Year="2016-17") AS qs2Prev
    RIGHT JOIN
    (SELECT [Year], [Name], School FROM Table2 WHERE Table2.Year="2017-18") AS qs1Current ON qs2Prev.Name = qs1Current.Name;

    Can reference comboboxes in place of the static criteria as Ranman suggested.

    Another approach:

    SELECT [Year], [Name], School FROM Table2 WHERE [Year] = "2017-18"
    UNION SELECT [Year], [Name], School FROM Table2 WHERE [Year] = "2016-17" AND [Name] IN (SELECT [Name] FROM Table2 WHERE [Year]="2017-18");


    Should not use reserved words as names for anything. Year and Name are reserved words.
    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.

  4. #4
    Pelato1980 is offline Novice
    Windows 10 Access 2007
    Join Date
    Oct 2017
    Posts
    2
    Thank you very much to everybody for help. I'll study your solutions and i'll try to solve this little work. See you soon. Bye

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

Similar Threads

  1. change year of a date in ms access 2007
    By boywonder115 in forum Access
    Replies: 2
    Last Post: 02-22-2017, 12:11 AM
  2. Replies: 4
    Last Post: 04-06-2016, 10:31 AM
  3. Replies: 2
    Last Post: 10-13-2014, 12:06 AM
  4. Replies: 1
    Last Post: 02-04-2013, 05:52 PM
  5. Fiscal Year in Access 2007
    By blindhawkeye in forum Access
    Replies: 1
    Last Post: 08-16-2011, 02:38 PM

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