Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

ORDER BY [--] Conflicts with DISTINCT

I have a VB6 program with an ADO connection to Access97 database.  A query that I set up in Access (and ran successfully) was copied/pasted to my VB program.  However, when I try to open a recordset based on the SQL I get the error: ORDER BY [fld] Conflicts with DISTINCT.

The SQL:

    strSelect = "SELECT DISTINCT tblPlacementInfo.IDLink, [LastName] & ', ' & [FirstName] & ' ' & [MiddleName] AS Name, "
    strSelect = strSelect & "tblMainInfo.DateofBirth, LUtblRace.Race, LUtblSex.Sex "
    strSelect = strSelect & "FROM ((tblMainInfo INNER JOIN tblPlacementInfo ON tblMainInfo.LinkID = tblPlacementInfo.IDLink) "
    strSelect = strSelect & "INNER JOIN LUtblRace ON tblMainInfo.Race = LUtblRace.RaceCode) "
    strSelect = strSelect & "INNER JOIN LUtblSex ON tblMainInfo.Sex = LUtblSex.SexCode "
    strSelect = strSelect & "ORDER BY [LastName], [FirstName], [MiddleName] ;"

I suspect someone's encountered this somewhere else, but I did not find any refs.

Thanks.

Ken
0
kenspencer
Asked:
kenspencer
1 Solution
 
TimCotteeHead of Software ServicesCommented:

   strSelect = "SELECT DISTINCT tblPlacementInfo.IDLink, [LastName] & ', ' & [FirstName] & ' ' & [MiddleName]
AS Name, "
   strSelect = strSelect & "tblMainInfo.DateofBirth, LUtblRace.Race, LUtblSex.Sex "
   strSelect = strSelect & "FROM ((tblMainInfo INNER JOIN tblPlacementInfo ON tblMainInfo.LinkID =
tblPlacementInfo.IDLink) "
   strSelect = strSelect & "INNER JOIN LUtblRace ON tblMainInfo.Race = LUtblRace.RaceCode) "
   strSelect = strSelect & "INNER JOIN LUtblSex ON tblMainInfo.Sex = LUtblSex.SexCode "
   strSelect = strSelect & "ORDER BY [LastName] & ', ' & [FirstName] & ' ' & [MiddleName]"

Should work for you.
0
 
andyclapCommented:
Unfortunately this is the way it works in access. If you use an orderby after a distinct you'll need to select the fields you're ordering by too. You can use a calclated field, but annoyingly you need to use the calculation again rather than the alias to reference it in the order by

You can either use:

strSelect = "SELECT DISTINCT tblPlacementInfo.IDLink, [LastName] & ', ' & [FirstName] & ' ' & [MiddleName]
AS Name, "
   strSelect = strSelect & "tblMainInfo.DateofBirth, LUtblRace.Race, LUtblSex.Sex "
   strSelect = strSelect & "FROM ((tblMainInfo INNER JOIN tblPlacementInfo ON tblMainInfo.LinkID =
tblPlacementInfo.IDLink) "
   strSelect = strSelect & "INNER JOIN LUtblRace ON tblMainInfo.Race = LUtblRace.RaceCode) "
   strSelect = strSelect & "INNER JOIN LUtblSex ON tblMainInfo.Sex = LUtblSex.SexCode "
   'order by the calculated field
   strSelect = strSelect & "ORDER BY [LastName] & ', ' & [FirstName] & ' ' & [MiddleName] ;"

or

strSelect = "SELECT DISTINCT tblPlacementInfo.IDLink, [LastName] & ', ' & [FirstName] & ' ' & [MiddleName]
AS Name, "
   strSelect = strSelect & "tblMainInfo.DateofBirth, LUtblRace.Race, LUtblSex.Sex "
  'have to bring back these fields too
   strSelect = strSelect & ", [LastName], [FirstName], [MiddleName] "
   strSelect = strSelect & "FROM ((tblMainInfo INNER JOIN tblPlacementInfo ON tblMainInfo.LinkID =
tblPlacementInfo.IDLink) "
   strSelect = strSelect & "INNER JOIN LUtblRace ON tblMainInfo.Race = LUtblRace.RaceCode) "
   strSelect = strSelect & "INNER JOIN LUtblSex ON tblMainInfo.Sex = LUtblSex.SexCode "
   strSelect = strSelect & "ORDER BY [LastName], [FirstName], [MiddleName] ;"

0
 
kenspencerAuthor Commented:
Oops!  I had copied/pasted the SQL, then edited it to remove the double-quotes from the calc field (Name), and that's where I took out the ", " and " " entirely.  I did not realize that DISTINCT causes problems with ORDER BY.  Tim was first and so gets the points.  Thanks to Andy for the explanation.

Ken
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now