none
SQL Query challenge in Microsoft Access 2013

    Question

  • Have an urgent query to produce in MICROSOFT ACCESS 2013 before a crucial exec review tomorrow.  Appreciate your help in advance.  Essentially, I have two tables that are sourced by SharePoint lists that I need to produce a JOIN query with.  Here's the configuration.

    TABLE1 is named "TEACHERS".  Its fields are:
    Field 1: ID (AutoNumber)
    Field 2: NAME (Short Text)
    Field 3 EMAIL (Short Text)
    Field 4: PHONE (Short Text)
     
    TABLE2 is named "PLACEMENTS".  Its fields are:
    Field 1: ID (AutoNumber)
    Field 2: COURSE (Short Text)
    Field 3: CURRICULUM (Short Text)
    Field 4: SCHOOL (Short Text)
    Field 5: TEACHER_1 (Number)
    Field 6: TEACHER_2 (Number)

    Ultimately, I want a query that returns (a) fields 1 through 4 in TABLE2 and (B) fields 1 through 4 in TABLE1.

    Here's how the tables are related.  TABLE2's fields 5 & 6 are teachers where the properties of a teacher are stored in TABLE1.

    Here's the challenge.  Table #2 has 3 records with different teachers in fields 5 & 6.  BUT….TABLE #1 (i.e. TEACHERS) only contains records corresponding to Amy, Dania, Eva and Flynn.

    **TABLE2: PLACEMENTS**
    ID - COURSE - CURRICULUM - SCHOOL - TEACHER_1 - TEACHER_2
    1   |  APCS     |     BJC               |  LSHS     |    Amy          |     Beatrice
    1   |  APCS     |     BJC               |  LSHS     |    Cathy        |     Dania
    1   |  APCS     |     BJC               |  LSHS     |    Eva            |     Flynn

    Here's the requirement.  What I need is a query that returns the following:

    TEACHERS Field 2: NAME
    TEACHERS Field 3 EMAIL
    TEACHERS Field 4: PHONE
    PLACEMENTS Field 2: COURSE
    PLACEMENTS Field 3: CURRICULUM
    PLACEMENTS Field 4: SCHOOL

    I'm using MICROSOFT ACCESS 2013.  What I'm after for is the precise SQL query that I can copy and paste into the SQL VIEW.

    Thanks for reading and appreciate the help

    Bingfoot

    Friday, October 04, 2013 5:46 AM

Answers

  • Your database has an incorrect Table structure that makes it hard to do what you want.

    If you are not familiar with Relational Database Design (and Database Normalization) or need a refresh, please peruse through the following short articles from Microsoft:

    Understanding Relational Database Design

    and

    Description of the database normalization basics

     

    There may be work-arounds but it is better to do it right in the first place since incorrect Table structure means you will have to find many more work-arounds later in the database development.

     


    Van Dinh

    Friday, October 04, 2013 9:48 AM
  • Your database design indeed needs work. The repeating group TEACHER_1, TEACHER_2 is a violation of normalization rules and will continue to cause grief until you fix it.

    But since you're under the gun, I would do this:

    1. Create a query joining the two tables: Teachers.Name to Placements.Teacher1. Select your 6 fields. Save as Query1

    2. Repeat, this time joining to Placements.Teacher2. Save as Query2.

    3. Open a new query to SQL View and enter this union query:

    select * from Query1
    union all
    select * from Query2


    -Tom. Microsoft Access MVP

    Friday, October 04, 2013 1:27 PM

All replies

  • Your database has an incorrect Table structure that makes it hard to do what you want.

    If you are not familiar with Relational Database Design (and Database Normalization) or need a refresh, please peruse through the following short articles from Microsoft:

    Understanding Relational Database Design

    and

    Description of the database normalization basics

     

    There may be work-arounds but it is better to do it right in the first place since incorrect Table structure means you will have to find many more work-arounds later in the database development.

     


    Van Dinh

    Friday, October 04, 2013 9:48 AM
  • Your database design indeed needs work. The repeating group TEACHER_1, TEACHER_2 is a violation of normalization rules and will continue to cause grief until you fix it.

    But since you're under the gun, I would do this:

    1. Create a query joining the two tables: Teachers.Name to Placements.Teacher1. Select your 6 fields. Save as Query1

    2. Repeat, this time joining to Placements.Teacher2. Save as Query2.

    3. Open a new query to SQL View and enter this union query:

    select * from Query1
    union all
    select * from Query2


    -Tom. Microsoft Access MVP

    Friday, October 04, 2013 1:27 PM
  • He is missing TeacherNum from table #1.

    However, it is possible that Teacher_1,Teacher_2 in table 2 can be linked with the ID field of table #1.
    That of course is never recommended.

    Friday, October 04, 2013 1:31 PM
  • These are helpful links/resources Van.  Thank you.
    Monday, October 07, 2013 6:07 AM
  • The union worked!  Thanks.  Excellent tip. I would have never caught that.

    • Proposed as answer by Syswizard Tuesday, October 08, 2013 12:37 PM
    Monday, October 07, 2013 6:07 AM