Results 1 to 6 of 6
  1. #1
    DBA-Meister is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    22

    Invalid Sort


    I have written a query as:

    Code:
    SELECT SubTeamID, Teams.TeamName, P1.FirstName, P1.LastName, P1.NameSuffix, P2.FirstName, P2.LastName, P2.NameSuffix
    FROM Teams, TwoManTeamPlayers AS TMT, Players AS P1, Players AS P2
    WHERE     P1.PersonID=TMT.[Player-1] and
                  P2.PersonID=TMT.[Player-2] and
                  P1.TeamID=Teams.TeamID
    ORDER BY Teams.TeamName, TMT.SubTeamID;
    However, whenever I run it, it sorts on P1.FirstName not TeamName. I have tried changing the order of the tables in the FROM clause with no success. I have also tried an ORDER BY with numeric indicators instead of actual field names. Still doesn't work. I get all the data, but it always sorts on player's first name instead of team name.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    There are no JOIN clauses in this query. Maybe that is the issue.
    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
    DBA-Meister is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    22
    June: I see three joins in the WHERE Clause. Perhaps using the equal ( = ) sign without adjacent spaces is a problem?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Example of query with JOIN

    SELECT Holes.*, dcpfiledata.* FROM Holes INNER JOIN dcpfiledata ON Holes.Hole=dcpfiledata.HoleNum WHERE (((dcpfiledata.CBR)<>9999));

    Spaces don't matter with symbol operators.
    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
    DBA-Meister is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2012
    Posts
    22
    I understand that using INNER JOIN is the common or preferred method of joining, but using the WHERE clause is still allowed and permissible with most db engines including access.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I am aware of that (although I don't use that structure) but since your output is not what you want, all I'm saying is try it and see what happens. Your data would probably need RIGHT or LEFT joins. Want to provide db for analysis? 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.

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

Similar Threads

  1. Invalid argument
    By wharting in forum Import/Export Data
    Replies: 4
    Last Post: 10-19-2011, 11:49 PM
  2. Replies: 7
    Last Post: 08-28-2011, 02:07 PM
  3. Invalid use of me
    By kman42 in forum Access
    Replies: 1
    Last Post: 04-28-2011, 12:40 PM
  4. Invalid use of Null
    By Wayne311 in forum Programming
    Replies: 4
    Last Post: 01-27-2011, 05:10 PM
  5. How to sort by three sort orders
    By captgnvr in forum Access
    Replies: 4
    Last Post: 11-09-2009, 07:30 AM

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