Results 1 to 6 of 6
  1. #1
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410

    WHERE not working correctly?

    It's supposed to only display where roster.TeamMemberTypeId = 5
    however it is showing other TeamMemberTypeId, such as 3, which are coaches.



    I cannot figure out what is wrong here, do you know? If so what is it / how do I fix it so it actually only shows TeamMemberTypeId=5?

    Code:
    SELECT MembershipNumber 'Mbr #', FirstName, LastName, TeamName, Level FROM ( SELECT DISTINCT person.Id 'PersonId', person.MembershipNumber, person.FirstName, person.LastName, team.TeamName, level.Description 'Level' FROM attribute.CompetitionSkatedEvents cse JOIN attribute.CompetitionEvents events on events.Id = cse.CompetitionEventId JOIN  entity.Competition comp ON events.CompetitionId = comp.Id JOIN attribute.CompetitionRegistration reg ON cse.RegistrationId = reg.Id AND reg.InvoiceNumber IS NOT NULL JOIN entity.Team team ON team.Id = reg.TeamId JOIN lookup.TeamLevel level ON team.TeamLevelId = level.Id LEFT JOIN attribute.TeamCompRoster roster ON roster.TeamId = team.Id AND roster.RegistrationId = reg.Id LEFT JOIN lookup.TeamMemberTypes teamMemberType ON teamMemberType.Id = roster.TeamMemberTypeId AND roster.TeamMemberTypeId = 5 LEFT JOIN entity.Person person ON roster.PersonId = person.Id WHERE comp.Id = 14724 AND cse.Bye <> 1 AND cse.Withdrawn = 0 AND cse.CompetitionEventId IN ( SELECT ce.Id 'CompetitionEventId' FROM attribute.CompetitionEvents ce WHERE ce.CompetitionId = comp.Id ) ) retrievedRoster WHERE PersonId IN ( SELECT PersonId FROM ( SELECT DISTINCT roster.PersonId 'PersonId', team.TeamName, level.Description 'Level' FROM attribute.CompetitionSkatedEvents cse JOIN attribute.CompetitionEvents events on events.Id = cse.CompetitionEventId JOIN  entity.Competition comp ON events.CompetitionId = comp.Id JOIN attribute.CompetitionRegistration reg ON cse.RegistrationId = reg.Id AND reg.InvoiceNumber IS NOT NULL JOIN entity.Team team ON team.Id = reg.TeamId JOIN lookup.TeamLevel level ON team.TeamLevelId = level.Id LEFT JOIN attribute.TeamCompRoster roster ON roster.TeamId = team.Id AND roster.RegistrationId = reg.Id LEFT JOIN lookup.TeamMemberTypes teamMemberType ON teamMemberType.Id = roster.TeamMemberTypeId AND roster.TeamMemberTypeId = 5 WHERE comp.Id = 14724 AND cse.Bye <> 1 AND cse.Withdrawn = 0 AND cse.CompetitionEventId IN ( SELECT ce.Id 'CompetitionEventId' FROM attribute.CompetitionEvents ce WHERE ce.CompetitionId = comp.Id ) ) temp GROUP BY PersonId HAVING COUNT( PersonId ) > 1 ) ORDER BY LastName

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Placing this SQL statement between CODE tags just makes it harder to read.

    SELECT MembershipNumber 'Mbr #', FirstName, LastName, TeamName, Level FROM ( SELECT DISTINCT person.Id 'PersonId', person.MembershipNumber, person.FirstName, person.LastName, team.TeamName, level.Description 'Level' FROM attribute.CompetitionSkatedEvents cse JOIN attribute.CompetitionEvents events on events.Id = cse.CompetitionEventId JOIN entity.Competition comp ON events.CompetitionId = comp.Id JOIN attribute.CompetitionRegistration reg ON cse.RegistrationId = reg.Id AND reg.InvoiceNumber IS NOT NULL JOIN entity.Team team ON team.Id = reg.TeamId JOIN lookup.TeamLevel level ON team.TeamLevelId = level.Id LEFT JOIN attribute.TeamCompRoster roster ON roster.TeamId = team.Id AND roster.RegistrationId = reg.Id LEFT JOIN lookup.TeamMemberTypes teamMemberType ON teamMemberType.Id = roster.TeamMemberTypeId AND roster.TeamMemberTypeId = 5 LEFT JOIN entity.Person person ON roster.PersonId = person.Id WHERE comp.Id = 14724 AND cse.Bye <> 1 AND cse.Withdrawn = 0 AND cse.CompetitionEventId IN ( SELECT ce.Id 'CompetitionEventId' FROM attribute.CompetitionEvents ce WHERE ce.CompetitionId = comp.Id ) ) retrievedRoster WHERE PersonId IN ( SELECT PersonId FROM ( SELECT DISTINCT roster.PersonId 'PersonId', team.TeamName, level.Description 'Level' FROM attribute.CompetitionSkatedEvents cse JOIN attribute.CompetitionEvents events on events.Id = cse.CompetitionEventId JOIN entity.Competition comp ON events.CompetitionId = comp.Id JOIN attribute.CompetitionRegistration reg ON cse.RegistrationId = reg.Id AND reg.InvoiceNumber IS NOT NULL JOIN entity.Team team ON team.Id = reg.TeamId JOIN lookup.TeamLevel level ON team.TeamLevelId = level.Id LEFT JOIN attribute.TeamCompRoster roster ON roster.TeamId = team.Id AND roster.RegistrationId = reg.Id LEFT JOIN lookup.TeamMemberTypes teamMemberType ON teamMemberType.Id = roster.TeamMemberTypeId AND roster.TeamMemberTypeId = 5 WHERE comp.Id = 14724 AND cse.Bye <> 1 AND cse.Withdrawn = 0 AND cse.CompetitionEventId IN ( SELECT ce.Id 'CompetitionEventId' FROM attribute.CompetitionEvents ce WHERE ce.CompetitionId = comp.Id ) ) temp GROUP BY PersonId HAVING COUNT( PersonId ) > 1 ) ORDER BY LastName

    These field references make no sense - I have never seen this syntax:

    MembershipNumber 'Mbr #',

    level.Description 'Level'

    ce.ID 'CompetitionEventID'

    roster.PersonID 'PersonID'

    Why are you using apostrophes this way? What are these text between the apostrophes supposed to be?

    Level is a reserved word. Should not use reserved words as names for anything. https://support.microsoft.com/en-us/kb/286335
    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
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    It's from someone else and I'm trying to fix.
    I think I fixed it by adding the TeamMemberTypeID=5 to the SELECT portion of the statement.

  4. #4
    Rivanni is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2016
    Posts
    9
    The field references as you call them are aliases. In SQL Server you can use quotes or square brackets. Needed when using spaces or reserved words.
    I see you also the reserved keywords 'lookup', 'description' and 'events' so use quotes or square brackets. So use [level], [ events], [description] and [lookup].

    Besides that, it's a nasty query. Try to use joins instead of IN clauses.

    Have you tested the outcome of the first main part of the query? You have to break the query in to pieces so you can test every part of it.

    The first part is:
    SELECT
    MembershipNumber 'Mbr #',
    FirstName,
    LastName,
    TeamName,
    Level
    FROM
    (
    SELECT DISTINCT
    person.Id 'PersonId',
    person.MembershipNumber,
    person.FirstName,
    person.LastName,
    team.TeamName,
    level.Description 'Level'
    FROM
    attribute.CompetitionSkatedEvents cse
    JOIN attribute.CompetitionEvents events
    ON events.Id = cse.CompetitionEventId
    JOIN entity.Competition comp
    ON events.CompetitionId = comp.Id
    JOIN attribute.CompetitionRegistration reg
    ON cse.RegistrationId = reg.Id
    AND reg.InvoiceNumber IS NOT NULL
    JOIN entity.Team team
    ON team.Id = reg.TeamId
    JOIN lookup.TeamLevel level
    ON team.TeamLevelId = level.Id
    LEFT JOIN attribute.TeamCompRoster roster
    ON roster.TeamId = team.Id
    AND roster.RegistrationId = reg.Id
    LEFT JOIN lookup.TeamMemberTypes teamMemberType
    ON teamMemberType.Id = roster.TeamMemberTypeId
    AND roster.TeamMemberTypeId = 5
    LEFT JOIN entity.Person person
    ON roster.PersonId = person.Id
    WHERE
    comp.Id = 14724
    AND cse.Bye <> 1
    AND cse.Withdrawn = 0
    AND cse.CompetitionEventId IN
    (
    SELECT
    ce.Id 'CompetitionEventId'
    FROM
    attribute.CompetitionEvents ce
    WHERE
    ce.CompetitionId = comp.Id
    )
    retrievedRoster

    --and the second part is the main WHERE clause directly following this first part.

    WHERE
    PersonId IN
    (
    SELECT
    PersonId
    FROM
    (
    SELECT DISTINCT --<<<<<<<<< try to run the query inside this where clause from here
    roster.PersonId 'PersonId',
    team.TeamName,
    level.Description 'Level'
    FROM
    attribute.CompetitionSkatedEvents cse
    JOIN attribute.CompetitionEvents events
    ON events.Id = cse.CompetitionEventId
    JOIN entity.Competition comp
    ON events.CompetitionId = comp.Id
    JOIN attribute.CompetitionRegistration reg
    ON cse.RegistrationId = reg.Id
    AND reg.InvoiceNumber IS NOT NULL
    JOIN entity.Team team
    ON team.Id = reg.TeamId
    JOIN lookup.TeamLevel level
    ON team.TeamLevelId = level.Id
    LEFT JOIN attribute.TeamCompRoster roster
    ON roster.TeamId = team.Id
    AND roster.RegistrationId = reg.Id
    LEFT JOIN lookup.TeamMemberTypes teamMemberType
    ON teamMemberType.Id = roster.TeamMemberTypeId
    AND roster.TeamMemberTypeId = 5
    WHERE
    comp.Id = 14724
    AND cse.Bye <> 1
    AND cse.Withdrawn = 0
    AND cse.CompetitionEventId IN
    (
    SELECT
    ce.Id 'CompetitionEventId'
    FROM
    attribute.CompetitionEvents ce
    WHERE
    ce.CompetitionId = comp.Id
    )
    ) temp
    GROUP BY
    PersonId
    HAVING
    COUNT( PersonId ) > 1 --<<<<<< to here
    &nbsp
    ORDER BY
    LastName

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The syntax I am familiar with is: MembershipNumber AS [Mbr #]

    Not sure I would use these aliases in query, at least not in the outer query. I would just set caption in label on form or report.

    I would definitely not use spaces and special characters/punctuation (except for underscore).

    The WHERE clause makes no sense. What is comp.Id? Why would criteria reference a field as a parameter?
    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.

  6. #6
    Rivanni is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2016
    Posts
    9
    After looking more closely to the WHERE clause I mentioned in my previous message I now see it's exactly the same query as the first part of the query. Why?

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

Similar Threads

  1. Replies: 9
    Last Post: 09-03-2015, 10:04 AM
  2. Console SQL Query App Not Working Correctly
    By ReignMan in forum Queries
    Replies: 7
    Last Post: 01-16-2015, 02:21 PM
  3. Unable to get Switch working correctly
    By Seeletse in forum Forms
    Replies: 13
    Last Post: 12-12-2012, 04:08 AM
  4. Condtion not working correctly
    By hawkins in forum Access
    Replies: 3
    Last Post: 09-07-2011, 02:59 PM
  5. Search field is not working correctly
    By jakeao in forum Programming
    Replies: 9
    Last Post: 05-18-2009, 07: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