Results 1 to 8 of 8
  1. #1
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82

    Need help on left join and limiting results


    I'm trying to list ALL offices in a district but return results for a specific course. The query I currently have works somewhat, but if I select First Aid and office A has records for First Aid and MS Word, it returns two results for that office.

    Code:
    SELECT tblOffices.OfficeName, IIf([CourseID]<>138 Or IsNull([CourseID])=True,0,[AllottedSpots]) AS [Allotted Spots], tblOffices.ID
    FROM tblOffices LEFT JOIN tblOfficeAllottedSpots ON tblOffices.ID = tblOfficeAllottedSpots.OfficeID
    WHERE (((tblOfficeAllottedSpots.CourseID)=138 Or (tblOfficeAllottedSpots.CourseID) Is Null) AND ((tblOffices.District)=54)) OR (((tblOfficeAllottedSpots.CourseID)<>138 And (tblOfficeAllottedSpots.CourseID) Is Not Null) AND ((tblOffices.District)=54))
    ORDER BY tblOffices.OfficeName;
    It's very close, but not perfect.

    Thanks,

    Scott

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Without knowing what the Allotted Spots field contains, the first thing to try is to set Unique Values =Yes
    Use SELECT DISTINCT instead of SELECT
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82
    Quote Originally Posted by ridders52 View Post
    Without knowing what the Allotted Spots field contains, the first thing to try is to set Unique Values =Yes
    Use SELECT DISTINCT instead of SELECT
    Allotted Spots is a number based on the iif statement. It does not have to be unique.

    I gave DISTINCT a try and same results.

    I made the above query into a stored query, then made another query to pick only those records where CourseID=138 or CourseID IS NULL.

    I don't like using stored queries for so many reasons. I would really like to know how to do it programmatically. I feel like a WHERE IN clause would work, but I'm not sure how to write it.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Suggest you post a stripped down version of your database and someone will look at it.
    Just those tables and the query will do.
    Replace any confidential data first.

    You may also find this sql to vba converter useful https://www.access-programmers.co.uk...d.php?t=293372
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82
    Quote Originally Posted by ridders52 View Post
    Suggest you post a stripped down version of your database and someone will look at it.
    Just those tables and the query will do.
    Replace any confidential data first.

    You may also find this sql to vba converter useful https://www.access-programmers.co.uk...d.php?t=293372
    Unfortunately I can't. The names of the fields and much of the code is protected info. Ugh. I hate SQL.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Remove all code.
    Remove all but a few records leaving just enough to see the issue. Remove any irrelevant fields. Replace names with Donald duck etc,
    As long as there's nothing confidential, there should be no problem.
    Otherwise, unable to assist further.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    scott0_1 is offline Advanced Beginner
    Windows Vista Access 2013 32bit
    Join Date
    Apr 2018
    Posts
    82
    Code:
    tblOfficeSeatAllocation
    --------------------------
    ID - autonumber
    CourseID - number
    OfficeID - number
    AllottedSpots - number
    Code:
    tblOffices
    -----------
    ID - autonumber
    Name - string
    District - number
    The SQL query is as above.

    I'm trying to list all offices in district 54 and the seat allocation. If null or 0, display 0. No repeats of office.

    That's as close as I can do.

    Thanks for the help! I know it's not ideal!

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    In post #1, you used a table tblAllottedSpots. Now you have a different table tblOfficeSeatAllocation.
    Which should it be?
    You've also not indicated which field(s) are common to each table
    You could show a relationships diagram.

    With the limited info you've given, I suggest you do an aggregate query where you group by office and filter for district 54.
    Use Nz(...,0) to display zero.
    That doesn't cover everything you had in post #1 but beyond that it's all guesswork.
    I might hit lucky but I could waste lots of my time and yours making incorrect guesses.

    So if that's really the best you can do, I'll say sorry and drop out of this thread.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 3
    Last Post: 09-20-2017, 09:50 AM
  2. Left Join Query returns invalid results
    By darike in forum Queries
    Replies: 13
    Last Post: 09-07-2016, 06:51 AM
  3. Replies: 12
    Last Post: 09-10-2015, 05:33 PM
  4. Inner Join works but not Left Join
    By kwooten in forum Queries
    Replies: 1
    Last Post: 12-11-2012, 05:09 PM
  5. Replies: 3
    Last Post: 02-02-2011, 01:00 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