Results 1 to 7 of 7
  1. #1
    pdevito3 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2013
    Posts
    35

    SQL: Union Query to Combine 3 Queries

    I have three queries that make a training list based on a person's role, team, and ad-hoc exceptions. The personnelID field is filtered by a listbox on a form. Each of these work great on their own (nice!). Now I want to combine them. I made the below union query that works... however when it is run, I am prompted to enter the personnelID again. This prompt only happens once. Do I need to incorporate a qhere statement somewhere, even though each of the individual queries have them already?

    Thanks!



    Code:
    SELECT First_Name, Last_Name, TrainingTitle, PersonnelID
    FROM qryPersonnelTrainingByRole
    UNION ALL
    SELECT First_Name, Last_Name, TrainingTitle, PersonnelID
    FROM qryPersonnelTrainingByTeam
    UNION ALL SELECT First_Name, Last_Name, TrainingTitle, PersonnelID
    FROM qryPersonnelTrainingByAdHocPersonnel;
    The where statement of potential use:
    Code:
    WHERE (((tblPersonnel.PersonnelID)=[Forms]![frmMain]![lbxPersonnel]));

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    If the 3 queries have parameter that references the form control, the UNION should not prompt for input if the form is open.

    I suggest you create a field in the UNION that identifies the source dataset.

    SELECT First_Name, Last_Name, TrainingTitle, PersonnelID, "ByRole" AS Category FROM ...
    ... "ByTeam" ...
    ... "ByAdHoc" ...
    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
    pdevito3 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Aug 2013
    Posts
    35
    Thanks June.

    I like the suggestion to show the category and changed the SQL accordingly, but am still getting a prompt for Personnel ID. I also think it's strange that it is asking specifically for the key versus the listbox value. What do you think is causing this?

    See the new code below:
    Code:
    SELECT First_Name, Last_Name, TrainingTitle, PersonnelID, "ByRole" AS Category FROM qryPersonnelTrainingByRole
     Union all
     SELECT First_Name, Last_Name, TrainingTitle, PersonnelID, "ByTeam" AS Category FROM qryPersonnelTrainingByTeam
     union all
     SELECT First_Name, Last_Name, TrainingTitle, PersonnelID, "ByAdHoc" AS Category FROM qryPersonnelTrainingByAdHocPersonnel;

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    It is not finding PersonnelID field so it prompts for input.

    Post queries for analysis or provide db. Follow instructions at bottom of my post.
    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.

  5. #5
    pdevito3 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Aug 2013
    Posts
    35
    Roles Query:

    Code:
    SELECT DISTINCT tblPersonnel.First_Name, tblPersonnel.Last_Name, tblTrainingList.TrainingTitle
    FROM tblTrainingList INNER JOIN (tblPersonnel INNER JOIN ((refTblTrainingGroups INNER JOIN ((refTblRoles INNER JOIN refJunctTblGroupToRole ON refTblRoles.RoleID = refJunctTblGroupToRole.RoleID) INNER JOIN refJunctTblPersonnelToRole ON refTblRoles.RoleID = refJunctTblPersonnelToRole.RoleID) ON refTblTrainingGroups.GroupID = refJunctTblGroupToRole.GroupID) INNER JOIN refJunctTblTrainingToGroup ON refTblTrainingGroups.GroupID = refJunctTblTrainingToGroup.GroupID) ON tblPersonnel.PersonnelID = refJunctTblPersonnelToRole.PersonnelID) ON tblTrainingList.TrainingID = refJunctTblTrainingToGroup.TrainingID
    WHERE (((tblPersonnel.PersonnelID)=[Forms]![frmMain]![lbxPersonnel]))
    ORDER BY tblTrainingList.TrainingTitle;
    Teams Query:
    Code:
    SELECT DISTINCT tblPersonnel.First_Name, tblPersonnel.Last_Name, tblTrainingList.TrainingTitle, tblPersonnel.PersonnelID
    FROM tblPersonnel INNER JOIN refJunctTblPersonnelToTeam ON (tblPersonnel.PersonnelID = refJunctTblPersonnelToTeam.PersonnelID) AND (tblPersonnel.PersonnelID = refJunctTblPersonnelToTeam.PersonnelID) AND (tblPersonnel.PersonnelID = refJunctTblPersonnelToTeam.PersonnelID), tblTrainingList INNER JOIN refJunctTblTrainingToTeam ON (tblTrainingList.TrainingID = refJunctTblTrainingToTeam.TrainingID) AND (tblTrainingList.TrainingID = refJunctTblTrainingToTeam.TrainingID)
    WHERE (((tblPersonnel.PersonnelID)=[Forms]![frmMain]![lbxPersonnel]));
    Ad Hoc Query:
    Code:
    SELECT DISTINCT tblPersonnel.First_Name, tblPersonnel.Last_Name, tblTrainingList.TrainingTitle, tblPersonnel.PersonnelID
    FROM tblTrainingList INNER JOIN (tblPersonnel INNER JOIN refJunctTblAdHocTrainingToPersonnel ON tblPersonnel.PersonnelID = refJunctTblAdHocTrainingToPersonnel.PersonelID) ON tblTrainingList.TrainingID = refJunctTblAdHocTrainingToPersonnel.TrainingID
    WHERE (((tblPersonnel.PersonnelID)=[Forms]![frmMain]![lbxPersonnel]));

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I don't see PersonnelID field selected in the Roles query.
    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.

  7. #7
    pdevito3 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Aug 2013
    Posts
    35
    Wow. Yeah, I must of accidently unchecked that box in the editor. Works like a charm now. Appreciate the help and the extra pair of eyes.

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

Similar Threads

  1. Union Query or Multiple Queries.???
    By MFS in forum Queries
    Replies: 9
    Last Post: 08-30-2014, 04:48 AM
  2. Replies: 1
    Last Post: 10-23-2012, 02:04 PM
  3. Replies: 1
    Last Post: 09-13-2012, 09:31 AM
  4. Union query- 9 Queries
    By JessieBee in forum Queries
    Replies: 3
    Last Post: 07-14-2012, 05:46 AM
  5. One filter for 3 Queries in Union Query
    By rlsublime in forum Access
    Replies: 3
    Last Post: 03-20-2012, 04:01 PM

Tags for this Thread

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