Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Union Select (10.4)

    Am using an Union Select to display records in a nametag format. The records are ordered by Year and Lastname. Those records shown for the first year seem to be ordered properly, however, those in the second year seem to ordered by Maiden name. I post my code/sql statement here because I know just about enuf to get into trouble. Can't seem to see why this won't work.

    "SELECT Members.ID, Members.FirstName, Members.LastName," & _
    " Members.MaidenName, Members.ClassYear,Attending.Attending" & _
    " FROM Members INNER JOIN Attending ON Members.ID = Attending.ID" & _
    " Where Attending.Attending = True AND Attending.Deceased = False" & _
    " And (Not IsNull(Lastname))" & _
    " ORDER BY Members.ClassYear, Members.Lastname" & _
    " Union SELECT Members.ID, IIF(Not IsNull(Spouse),Spouse,Null)" & _
    " AS SpouseFirstName,IIF(Not IsNull(Spouse),'(' & Members.FirstName" & _
    " & ' ' & Members.MaidenName & ')',Null) as SpouseName," & _
    " IIf(Not IsNull(Spouse), Members.LastName,Null) AS SpouseLastName," & _
    " Members.ClassYear,Attending.Attending" & _
    " FROM Members INNER JOIN Attending ON Members.ID = Attending.ID" & _
    " WHERE Members.Spouse<>'' AND Attending.Attending=True AND" & _
    " Attending.Deceased = False"

    Any help is appreaciated.

    Tahnx.

    Kim
    Thanks

    Kim

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Union Select (10.4)

    Try moving the Order By clause to the end of the SQL.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Union Select (10.4)

    Here's what I have discovered: the order of the nametag report is Firstname on first line, Maidenname on second line and Lastname on third line. The sql is written so that if the wife is the classmate, her name is listed Mary firstline, Dickson (maiden) second line, Peterson (Marriedname). The husband's tag is Kim firstline, Peterson second, (Mary Dickson) third.

    But the report layout (sql) says that the second line is maiden name and third line is lastname. Therefore, the order is all messed up. I can not see a way around this. I hope someone does.

    Thanx.

    Kim
    Thanks

    Kim

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Union Select (10.4)

    In a union query, the first query determines the names of the fields in the result. Fields from subsequent queries are used in the order they are listed. Aliases are ignored.

    The condition (Not ISNull(LastName)) in the first query can be replaced by LastName Is Not Null - this is more efficient, since it doesn't use a function.

    The condition Member.Spouse <> '' can probably be replaced by Members.Spouse Is Not Null. This makes the IIf functions superfluous - you only select records for which Spouse is not null anyway.

    There is no need to set an Order By in the query, for reports ignore the sort order of their record source. You must set the sort order of a report in the Sorting and Grouping window.

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Union Select (10.4)

    Kim,

    Just to clarify a little.

    In a Union query, you must put the order of the fields in all of the queries used in the same order. For example, the first query, as Hans indicates, drives the data. You selected ID, First, Last, Maiden, Year, Attendance. Your second query must also be in this order (which upon a quick look, I do not believe it is). If the second query select is Last, Maiden, First, ... then the Column for First will contain both First and Last names, and the Column for Last will contain Last and Maiden and the column for Maiden will contain Maiden and First (as long as the data types are the same and you dont get an error.)

    To fix this, order the fields of the second query to be in the same order of the first query.

    HTH
    Regards,

    Gary
    (It's been a while!)

  6. #6
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Union Select (10.4)

    Thanks for all the help.

    Here is the result:

    "SELECT Members.ID, Members.FirstName, Members.LastName," & _
    " Members.MaidenName, Members.ClassYear,Attending.Attending," & _
    " Attending.Deceased" & _
    " FROM Members INNER JOIN Attending ON Members.ID = Attending.ID" & _
    " Where Attending.Attending = True AND Attending.Deceased = False" & _
    " And LastName Is Not Null " & _
    " Union SELECT Members.ID, Spouse,Members.LastName,'(' & Members.FirstName" & _
    " & ' ' & Members.MaidenName & ')'," & _
    " Members.ClassYear,Attending.Attending, Attending.Deceased" & _
    " FROM Members INNER JOIN Attending ON Members.ID = Attending.ID" & _
    " WHERE Members.Spouse <> '' AND Attending.Attending=True AND" & _
    " Attending.Deceased = False"

    Need the spouse<> '' because otherwise I end up with Lastname on first line, (Firstname) on second line in parens.

    Another nicety I would like to see is to eliminate a third line for a non class mate. Here is what I now get:

    Mary
    Peterson
    (Kim)

    where Kim is the class mate. Is it possible to eliminate with what all else is going on?

    Thanks.

    Kim
    Thanks

    Kim

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •