Results 1 to 5 of 5
  1. #1
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80

    Query with maybe subquery

    I am trying to build a query that will select data from a few tables to create a datasheet and will give different results based on some underlying data in the tables. Basically I have a form that shows events and on this I have a table that will show the people who are scheduled onto this event. This part is done and is working fine but I then want to brng up a popup form when the user moves over the subform on the SubteamHeadDellID field that will show the mobilenumber details for the user to text to inform them of the event but due to the hierachial system the company operates if the SubTeamHeadDelID is the same as a member of the subteam (there are set heads and sometimes they will go onto other events not as a subteamhead) ie they are scheduled onto this event then I need the popup form to show the SubTeamParentIDs details instead.
    I created a subquery that I wrote in the SubTeamHeadDelID criteria field on the Query designer but it says the syntax is wrong and I'm not even sure if it would work. Here it is if anyone can tell me if this would work. I'm not that skilled at Query writing and need some guidance of the syntax or logic:



    Code:
     
    (SELECT tblSubTeam.SubTeamHeadDelID WHERE tblSubTeam.SubTeamHeadDelID <>tblDelegate.DelegateID IIF tblSubTeam.SubTeamHeadDelID=tblDelegateID SELECT tblSubTeamParentID)
    I can't test it until the syntax is right but I have no idea what would be right. I know I can get the right syntax in the query design window but this is written in the Zoom box so I have to write by hand.

    I hope this makes some sense though I doubt it

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Is this the full nested query or just the inner part?

    I suggest you build the inner query first with Query Designer and save it. Then when you get that working, build the outer query referencing the first query object. Then if you want as all-in-one nested copy paste the first query SQL into the second.
    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
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    How can I reference a subquery from within a query I have one query:
    Code:
     
    SELECT tblDelegate.ID, tblDelegate.DelegateFname, tblDelegate.DelegateLname, tblDelegate.DelegateMobileNumber, tblDelegate.DelegateEmailAddress, tblEventDelegate.Status, tblDelegate.SubTeamID, tblSubTeam.SubTeamHeadDelID, tblSubTeam.SubTeamParentID
    FROM tblSubTeam INNER JOIN (tblDelegate INNER JOIN tblEventDelegate ON tblDelegate.ID = tblEventDelegate.DelegateID) ON tblSubTeam.ID = tblDelegate.SubTeamID
    WHERE (((tblEventDelegate.Status)=1) AND ((tblSubTeam.SubTeamParentID)=IIf([tblSubTeam].[SubTeamHeadDelID]=[tblDelegate].[ID],[SubTeamParentID],[SubTeamHeadDelID])));
    this shows the delegates who are listed as SubteamHeadDelIDs and works but I need to somehow incorporate that into another query which will show all the delegates and show those that are SubTeamHeadDels by showing their SubTeamParentID.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    This is a saved query object? Build another query that refers to this one just like with a table and save it. Now if you want an all-in-one nested query, open both queries in SQL View. Paste the SQL of the first query into the second. Or just leave them as two queries.

    Without data to test I can't be sure how you would structure the queries to get what you want. It's just a bit difficult to determine from the SQL statement alone.
    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
    AndycompanyZ is offline Advanced Beginner
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    80
    Yes it is a saved query I tried refering to it in a query but it didn't give the results I was expecting so I've decided to scrap it and try another method. Thanks for the help.

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

Similar Threads

  1. Subquery sum?
    By anemoskkk in forum Access
    Replies: 0
    Last Post: 04-29-2011, 12:36 PM
  2. SQL SubQuery Does Not Work
    By saascuba in forum Access
    Replies: 3
    Last Post: 11-04-2010, 01:59 PM
  3. Subquery
    By combine21 in forum Queries
    Replies: 2
    Last Post: 09-17-2010, 04:33 PM
  4. Query and subquery wont work with combo box
    By jbg8931 in forum Queries
    Replies: 0
    Last Post: 05-09-2010, 10:24 AM
  5. Replies: 2
    Last Post: 05-27-2009, 08:47 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