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

    Name Tags (10.4)

    Would like to make multiple line name tags similar to mailing labels. Fields are Firstname, Lastname, MaidenName and Spouse. Would like to make two name tags, one for the member and the other for the spouse, if applicable.

    The code I am using to print the Member's name tag is:

    SELECT Members.Firstname, Members.LastName, Members.MaidenName, Members.Spouse, Attending.Attending
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE (((Members.Spouse)<>"") AND ((Attending.Attending)=True))
    ORDER BY Members.LastName;

    I have included the spouse field. My problem is how to place it onto the data report so as to print each name tag.

    Thanx for your help

    Kim
    Thanks

    Kim

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Name Tags (10.4)

    You really need to have a record for each name tag you want to print - so I would suggest a union query that captures the name of each primary person attending, and the name of each spouse attending. Note that a union query is essentially two select statements joined by a Union statement, but both selects have to return fields with the same name, so it may require some create expressions to get that kind of result. If you need more details, please post back.
    Wendell

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

    Re: Name Tags (10.4)

    Well, what you listed was SQL, not code. Is Spouse a field for the spouse's first name or what, and did you want to show it on the member's tag as well or only on the spouse's tag? And did you realize that the way it's written, you won't get any names for attending members who don't have a spouse?

    What do you want the name tags to look like? You lay out the report the way you want the tags to print. You need to design your query so that you get one record for the member and another record for the spouse unless everyone will have a spouse, in which case you can reuse the lastname field from the member's record.
    Charlotte

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

    Re: Name Tags (10.4)

    Guess what I wrote was not clear. There are two tables: Members and Attending. The Members table has Lastname, Firstname, MaidenName and Spouse. All fields have single entries, i.e. Spouse firstname only. The layout I am looking for is for a member:

    Mary Ann
    Dickson
    Peterson

    and for the spouse:

    Kim
    Peterson

    The attending table has the boolean attending.

    Having all this information in one record in two tables, is it possible to print nametags for those attending for both the member and spouse, if there is a spouse? Or should I make two queries, one for the Member and one for the Spouse?

    It would be nice to have it in one.

    Thanx for your help.

    Kim
    Thanks

    Kim

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name Tags (10.4)

    Use a UNION query. Something along the following lines:
    SELECT Lastname, Firstname, 'T' as member FROM Members
    UNION SELECT ALL Lastname, Firstname, 'F' as member FROM Attending;

    This will give a value of 'T' for records from the member table and 'F' for those from the Attending table. In you label report you can test for this field and adjust the format as appropriate.

    ps. I assume you do have a relationship to link the Member to the spouse.

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

    Re: Name Tags (10.4)

    With a lot of help here is what I now have:

    SELECT Members.ID, Members.FirstName, Members.LastName, Members.MaidenName, Attending.Attending
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE (((Attending.Attending)=True))
    ORDER BY Members.LastName, Members.ID
    UNION SELECT Members.ID, IIf(Not IsNull([Spouse]),[Spouse],Null) AS SpouseFirstName, IIf(Not IsNull([Spouse]),[LastName],Null) AS SpouseLastName,Members.MaidenName=null, Attending.Attending
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE (((Attending.Attending)=True));

    However, if the person is not married (no spouse) this union select prints a blank for the first name and then prints the last name.

    Any ideas on how to print only if the record has no first name and spouse name?

    Thanx.

    Kim
    Thanks

    Kim

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name Tags (10.4)

    Try using the trim function in the control on your report. (also property CanShrink= true)

    =Trim([fieldName])

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

    Re: Name Tags (10.4)

    Thanx for the response. The report is called by a form produced by Crystal Reports. I can't seem to locate the property canshrink.

    Thanx.
    Thanks

    Kim

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Name Tags (10.4)

    Try this and see if it doesn't eliminate the blank spouse records:

    SELECT Members.ID, Members.FirstName, Members.LastName, Members.MaidenName, Attending.Attending
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE (((Attending.Attending)=True))
    ORDER BY Members.LastName, Members.ID
    UNION SELECT Members.ID, Members.Spouse, Members.LastName, Members.MaidenName, Attending.Attending
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE (((Attending.Attending)=True) And (Not IsNull(Members.Spouse)));

    By putting the condition in the WHERE clause, there will be no spouse record if there isn't a spouse in the member record, so no form will print.
    Wendell

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

    Re: Name Tags (10.4)

    Thanx for the response. Whether or not the "And (Not IsNull(Members.Spouse))" is in the sql here are the results of that union query:

    ID FirstName LastName MaidenName Attending
    16 Hammer -1
    16 Gil Hammer -1
    19 Adeline Halfmann Ketter -1
    19 Charles Halfmann -1
    27 Darlis Kempf Kudek -1
    27 Del Kempf -1
    30 Baier -1
    30 Valeria Baier Martin -1

    Hard to get the fields to line up with the names but you can see that the firstname field for ID 16 & 30 are blank. It stil prints with a blank firstname and then the correct lastname. Also, in the Union Select portion Members.Maidenname must be set to null otherwise the spouse's record prints a maidenname.

    Thanx for the help.

    Kim
    Thanks

    Kim

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Name Tags (10.4)

    Is it possible that those spouse names are not null, and simply filled with spaces, or what is called a zero length string? That can happen if someone edits a record by replacing characters with blanks/spaces. It's simple enough to test with a filter or a query, but Hammer and Baier are certainly returning a record.
    Wendell

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

    Re: Name Tags (10.4)

    Wendell,

    If I use this query:

    SELECT Members.ID, Members.Firstname, Members.LastName, Members.Spouse, Attending.Attending
    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE (((Members.Spouse) Is Not Null) AND ((Attending.Attending)=True))

    the same records are returned as the Union Select only one of each, naturally.

    If this query is used:

    FROM Members INNER JOIN Attending ON Members.ID = Attending.ID
    WHERE (((Members.Spouse)<>'') AND ((Attending.Attending)=True));

    then only those records that have a spouse are returned. I have also checked the table and no spaces.

    Anything else I should check?

    Thanx.

    Kim
    Thanks

    Kim

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Name Tags (10.4)

    Ah Ha! You have zero length strings for spouse in at least some cases - so use the WHERE (((Members.Spouse)<>'') AND ((Attending.Attending)=True)); clause instead of what I sent you. That should fix your union query and the name tags functions.
    Wendell

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

    Re: Name Tags (10.4)

    Wendell,

    Palm fronds to you. Many thanx for the help. Works like a charm.

    Kim
    Thanks

    Kim

  15. #15
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name Tags (10.4)

    Crystal reports are a separate product, not part of Access. If you have future problems with a report you really ought to make it clear first if it is not a native access report. A field in an access report does have a CanShrink property. For a crystal report field I would have used a formula to format the address and in the code handled the blank entry.

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
  •