Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Sometime ago this forum helped me with a Union All Select query. I still do not have a handle on this type of query. In this one I have two tables in which a query is run for making name tags. The format for the classmate is:
    Firstname
    (Maidenname) if any
    Lastname

    The format for the spouse is:
    Firstname
    Lastname

    The where part is if both are attending the event then the count is 2 otherwise if only one is attending the count is one, which is obvious but I am playing around trying to get this thing to work. As of now I have this query:

    [sql]SELECT FirstName, LastName, MaidenName, ClassYear, ClassPhoto, ClassYear AS OrderYear,
    LastName AS Sortname, 1 AS SortOrder
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE EventAttend=True
    UNION ALL SELECT Spouse, LastName, '',
    IIF(Spouse = '',Classyear,''),
    IIF(MaidenName = '(Spouse)','',''), ClassYear,
    Lastname, 2 As SortOrder
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE Spouse <> '' AND EventAttend=True
    ORDER BY OrderYear, SortName, SortOrder[/sql]

    However, with this query, both Classmate and Spouse show up even though the record shows that only one will be in attendance. So I tried inserting this: IIF(NumAttend=1,1,NumAttend). But can't seem to tie to the query to be effective.

    Any ideas would be great. Thanx.
    Thanks

    Kim

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I think it'll be hard to help you without seeing a stripped down and zipped copy of the database.

    What is the purpose of IIF(MaidenName = '(Spouse)','','') ? The value will always be an empty string whether MaidenName is "(Spouse)" or not.

    IIF(NumAttend=1,1,NumAttend) isn't very useful either, it's equivalent to NumAttend, but what is NumAttend? A field in one of the tables?

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Yes, NumAttend is field in a table. The qty in most cases will be either 1 or 2. If the classmate is married but spouse is not attending this function, then that number would be 1. Trying to use that field to somehow eliminate the printing of that name tag. At the present my thinking is to add an other field called SpouseEventAttend as a boolean. When the user fills in the number attending this event that boolean field could be flagged as true if both classmate and spouse are attending or false if only the class mate is attending. If classmate is bringing an additional friend, then I'm really stumped.

    The database is used in a VB6 program.Hopefully, that should be of no concern.

    I think that the IIF(MaidenName = '(Spouse)','','') is to even out the Union All Select.

    The zipped, stripped db is attached.

    Appreciate the help.
    Attached Files Attached Files
    Thanks

    Kim

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In the record with ID 213, how should the query determine to whom the maiden name belongs? (They're not attending, but that is beside the point.) I know that Rosie is probably a woman and Donald a man, but Access doesn't know that.

    Why do you have separate Members and Attending tables? They have a one-to-one relationship so it would be more efficient to combine the tables into one.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Wisconsin, USA
    Posts
    160
    Thanks
    1
    Thanked 0 Times in 0 Posts
    The Maiden name goes to the married classmate. Rosie is the classmate as entered into the user interface.

    I understand the efficiencies or lack thereof with a one to one. However, for now that has to stay as is. Hopefully, that will not affect the Union All Select, will it?
    Thanks

    Kim

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Yes, you can keep the two tables.

    Does this do what you want?

    SELECT Firstname, Lastname, MaidenName, ClassYear, Classphoto, ClassYear AS SortYear, Lastname AS SortName, 1 AS SortOrder
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE NumAttend>0
    UNION ALL SELECT Spouse, Lastname, Null, Null, Null, ClassYear, Lastname, 2
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE Spouse Is Not Null AND NumAttend>1
    ORDER BY SortYear, SortName, SortOrder

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

    Many thanks for the help. That is exactly what I was/am looking to do. Appreciate your help.
    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
  •