Results 1 to 6 of 6
  1. #1
    Bingfoot is offline Novice
    Windows 8 Access 2013
    Join Date
    Oct 2013
    Posts
    2

    SQL Query challenge in Microsoft Access 2013


    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

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Your data is confusing. In your description of the data in your tables, you indicate that table 2 fields 5 and 6 are numbers, but in the next description, you show actual names for six teachers. Based upon your narrative, should not these be numbers? I am confused how this can occur that you only have three names yet you have records with six names/numbers in the table to be joined.

  3. #3
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    First, you would make your own life much easier if you added records for Beatrice and Cathy to TABLE#1 and left the unknown information in those records blank. That way you don't have to worry about what the missing records will do to your queries.

    Second, I'm assuming from your statements that it is the teacher name in TEACHER_1 and TEACHER_2, rather than the teacher key, which would have been the proper way to design the database.

    Try this -
    Code:
       SELECT
          TEACHERS.NAME,
          TEACHERS.EMAIL, 
          TEACHERS.PHONE, 
          P1.COURSE,
          P1.CURRICULUM, 
          P1.SCHOOL,
          P1.TEACHER_1    AS T1_Name,
          NZ(T1.EMAIL,"") AS T1_Email,
          NZ(T1.PHONE,"") AS T1_Phone,
          P1.TEACHER_2    AS T2_Name,
          NZ(T2.EMAIL,"") AS T2_Email,
          NZ(T2.PHONE,"") AS T2_Phone
       FROM 
         ((PLACEMENTS AS P1 LEFT JOIN TEACHERS AS T1
           ON P1.TEACHER_1=T1.NAME)
         LEFT JOIN JOIN TEACHERS AS T2
         ON P1.TEACHER_2=T2.NAME);
    If it complains about the join, try this:
    Code:
       FROM 
         (TEACHERS AS T2 RIGHT JOIN
           (PLACEMENTS AS P1 LEFT JOIN TEACHERS AS T1
           ON P1.TEACHER_1=T1.NAME)
         ON P1.TEACHER_2=T2.NAME);

  4. #4
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Third, it would be better to be more specific in naming the ID fields, and to break down the second table into two tables:
    Code:
    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 "COURSES". Its fields are:
    Field 1: ID (AutoNumber)
    Field 2: COURSE (Short Text)
    Field 3: CURRICULUM (Short Text)
    Field 4: SCHOOL (Short Text)
    
    TABLE3 is named "PLACEMENTS". Its fields are:
    Field 1: ID (AutoNumber)
    Field 2: TEACHERID (Number)
    Field 3: COURSEID (Number)
    Field 4: TEACHERTYPE (optional - use if Teacher_1 and Teacher_2 have different functions primary/backup or teacher/aide)

  5. #5
    Bingfoot is offline Novice
    Windows 8 Access 2013
    Join Date
    Oct 2013
    Posts
    2
    Quote Originally Posted by Dal Jeanis View Post
    Third, it would be better to be more specific in naming the ID fields, and to break down the second table into two tables:
    Code:
    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 "COURSES". Its fields are:
    Field 1: ID (AutoNumber)
    Field 2: COURSE (Short Text)
    Field 3: CURRICULUM (Short Text)
    Field 4: SCHOOL (Short Text)
    
    TABLE3 is named "PLACEMENTS". Its fields are:
    Field 1: ID (AutoNumber)
    Field 2: TEACHERID (Number)
    Field 3: COURSEID (Number)
    Field 4: TEACHERTYPE (optional - use if Teacher_1 and Teacher_2 have different functions primary/backup or teacher/aide)
    Excellent tip. Thank you. TeacherType should have been there from the start. Nice catch.

  6. #6
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    I'm sure it was there implicitly in the Teacher_1 vs Teacher_2. When those are properly normalized out of the same table, an explicit type becomes needed. You simultaneously get additional flexiblity. For example, you can have a course with two full instructors (for example, a science course with a lecture instructor and a separate lab instructor) or with no instructors (such as a study hall with two monitors and no actual teacher).

    By the way, since your Teacher_1 and Teacher_2 fields were numbers, I was wrong to assume they were names. (Duh.) Also, for no particular reason I seem to have included an unaliased version of TEACHERS. The SQL should probably have looked like this:
    Code:
    SELECT
          P1.COURSE,
          P1.CURRICULUM, 
          P1.SCHOOL,
          P1.TEACHER_1    AS T1_ID,
          NZ(T1.NAME,"")  AS T1_Name
          NZ(T1.EMAIL,"") AS T1_Email,
          NZ(T1.PHONE,"") AS T1_Phone,
          P1.TEACHER_2    AS T2_ID,   
          NZ(T2.NAME,"")  AS T2_Name,
          NZ(T2.EMAIL,"") AS T2_Email,
          NZ(T2.PHONE,"") AS T2_Phone
       FROM 
         ((PLACEMENTS AS P1 LEFT JOIN TEACHERS AS T1
           ON P1.TEACHER_1=T1.ID)
         LEFT JOIN JOIN TEACHERS AS T2
         ON P1.TEACHER_2=T2.ID);

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

Similar Threads

  1. Replies: 12
    Last Post: 07-25-2013, 12:49 PM
  2. Access 2013 Web App with Sharepoint 2013
    By miguel.escobar in forum Access
    Replies: 7
    Last Post: 06-17-2013, 09:03 AM
  3. Microsoft Access query name arrangement from the table
    By successfulfail in forum Access
    Replies: 3
    Last Post: 10-31-2012, 12:08 PM
  4. Replies: 37
    Last Post: 09-15-2011, 11:57 AM
  5. Replies: 1
    Last Post: 09-13-2011, 01:52 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