Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  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)

    Sometime ago I asked about a union select, thought I had it the way I wanted it. Tried to find it here but was not successful. So, here are the present parameters. Am trying to make name tags. First two lines are pretty straight forward - Firstname on the first, Last name on the second. But, on the third line I would like to have the married name of the female class mate, nothing for the male class mate. So far, not a problem. If the class mate is a male is where I am stumped. I can get his name tag but his spouse's is the problem causer. I would like to see her first name, married name, then the label 'Spouse' on the third line. My code:

    SELECT Members.ID, Members.FirstName, Members.LastName, MarriedName
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE Attending.Attending=True
    ORDER BY Members.MarriedName, Members.Firstname
    UNION SELECT Members.ID, Members.Spouse, Members.marriedName,
    IIF( marriedname is Null,lastname,'(Spouse)')
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE Spouse <> '' AND Attending.Attending=True;

    for a male class mate yields Firstname, (Spouse) on second line and empty third line.

    Any help is greatly appreciated.
    Thanks

    Kim

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

    Re: Union Select (10.4)

    Kim,

    1. Could you post a small anonymized sample of your Members table, either directly in the bodt of a post or as an attachment, so that we can see what kind of data you're working with?

    2. Just out of curiosity: you always put 10.4 as version number on your posts. What does it refer to?

  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)

    Hans-
    Thanks for the respones. Not too sure what anonymized means so if the stuff I post is now what you want, holler. Think you have a good idea what the fields are called so here are some of the data populating those fields:

    FirstName
    Kim
    Mary Jane
    Adeline

    Lastname
    Peterson
    Ketter
    Falk

    MarriedName

    Vass
    Halfmann

    Spouse

    Robert
    Charles

    All are class mate members. I would like the name tag to be:

    Should be two columns for two name tags here:

    Adeline Charles
    Ketter Halfmann
    Halfmann (spouse)

    kim Mary Ann
    Peterson Peterson
    (spouse)

    I enter 10.4 because it asks for the version number of Access that I am using, or at least that is what I think it is asking.

    Can't seem to put this stuff in columns. Possible?
    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)

    Kim.

    1. I'm sorry, I guess that "to anonymize" does not exist. I meant that you should replace real names by made-up ones.

    2. I can't match up the names as you posted them. Could you try again?

    3. You can either use <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags to show text "as is":<pre>Column1 Column 2
    John Mary
    Jim Sandra
    Henry Charles</pre>

    or insert a table:

    <table border=1><td>row1-col1</td><td>row1-col2</td><td>row2-col1</td><td>row2-col2</td></table>
    Use the 1-Click TagPanel to insert these "tags" into a post.

  5. #5
    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)

    Hans,

    Let me try it again.


    <table border=1><td>firstname</td><td>Lastname</td><td>Marriedname</td><td>Spouse</td><td>kim</td><td>peterson</td><td></td><td>Mary Ann</td></tr><td>Mary</td><td>Jones</td><td>Able</td><td>Robert</td></tr><td>Ed</td><td>Smith</td><td></td><td>Georgia</td>
    </table>

    If you need more let me know.
    Thanks

    Kim

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

    Re: Union Select (10.4)

    Thanks very much. That is clearer. Can you repost how you want the name tags to come out now, for you have used (partially) different names than in the original example? Sorry to be such a bother, but I want to get a clear idea of what you have and what you want to get.

  7. #7
    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)

    Whoops. Forgot to finish it. Here is how I would like the name tags:

    kim Mary Ann
    Peterson Peterson
    (Spouse)

    Mary Robert
    Jones Able
    Able (Spouse)

    Ed Georgia
    Smith Smith
    (Spouse)

    Hope this helps to clarify. Not a bother at all. Am learning how to format on this forum. Sorry I did not make it clear the first time.

    As far as the 10.4, I should use xp?
    Thanks

    Kim

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

    Re: Union Select (10.4)

    Last question first: the usual designation is Office XP for the entire suite, and Access 2002, Excel 2002 etc. for the individual applications (don't ask me why, in previous incarnations the suite and the applications had the same suffix)

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

    Re: Union Select (10.4)

    OK, try this query:

    SELECT Members.ID, Members.FirstName, Members.LastName, MarriedName
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE Attending.Attending=True
    UNION SELECT Members.ID, Members.Spouse, Nz([MarriedName],[LastName]), 'Spouse'
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE Spouse <> '' AND Attending.Attending=True;

    The Nz function returns its first argument unless that is empty (null), in that case the second argument is returned.

  10. #10
    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)

    Hans,

    Both yours and mine return the same info. Neither places a lastname for the spouse of a male class mate. Here is an example:

    kim Mary Ann
    Peterson
    Spouse

    Both return the name tag as illustrated in one of the previous posts for female class mates.
    Thanks

    Kim

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

    Re: Union Select (10.4)

    They don't return the same info. See attached demo (only a Members table, without an Attending table)
    Attached Files Attached Files

  12. #12
    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)

    Hans,

    Got to take some time to figure out what's happening. I copied and pasted from your post into my query and that is why I posted what I did. It seems not to have changed anything. I'll keep messin' with it to see how I'm screwing up.

    Appreciate your help. I'm sure I'll be back.
    Thanks

    Kim

  13. #13
    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)

    Apparently, in the table/query I was using, I had entered some data into the Members table without ensuring that it was also in the Attending table. Thus, incorrect data representation in the query output. Thanx for your patience.

    I tried to cut an paste the query into VB and VB has a problem with the Nz portion. Any work around?
    Thanks

    Kim

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

    Re: Union Select (10.4)

    Nz is an Access-specific function, it is not known in other VBA dialects. In VB6 you can use

    IIf([MarriedName] Is Null, [LastName], [MarriedName])

    instead of

    Nz([MarriedName],[LastName])

  15. #15
    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)

    Hans,

    Many thanks for your help. I was using is null in VB and was not getting the same info as in Access. Changed that to = '' and, voila! Everything seems to be working as planned.
    Thanks

    Kim

Page 1 of 2 12 LastLast

Posting Permissions

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