Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Printing labels 2 (Office 2000)

    Another question on printing labels. Sometimes I have to print two labels for membership cards. Sometimes the names are the same (Mr. & Mrs) but other times a different name goes on the second card. I have inserted a new field called "Second card".

    I would like to somehow tell Access that if there's anything in that field, print the original label plus a second label with that name in it, otherwise just print the original label.

    Where do I do this and how? Ah, I just thought that I should do a new query with that information only in it right?

    Thanks again,
    Louise

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Printing labels 2 (Office 2000)

    Off-hand, I'd say you needed a UNION query to do this. The first SELECT statement would take the Primary name, the 2nd SELECT statement would take the 2nd card name (if it wasn't NULL).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing labels 2 (Office 2000)

    Hi Mark,
    I don't know what a Union query is or how to set it up. Will have to learn more about it.

    Thanks!
    Louise

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

    Re: Printing labels 2 (Office 2000)

    A union query must be edited in SQL view. It looks like this:

    SELECT Field1, Field2 FROM Table1
    UNION
    SELECT Field3, Field4 FROM Table2

    Both SQL statements must select the same number of fields, and corresponding fields must be of the same type. The field names in the first SQL statement determine the field names in the union query.

    Also see SQL: UNION Query, Creating Union Queries and/or Access - Union Queries or Joining Queries Together.

    Feel free to post back if you need more help.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing labels 2 (Office 2000)

    Hi Hans,

    Thank you very much! Those were very helpful! They all refer to using two tables. I only have one. I just added the second name to the same table. Can I still use the Union query but reference the same table?

    Thanks
    Louise

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing labels 2 (Office 2000)

    Yes you can!

    Just repeat the fields and table name.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing labels 2 (Office 2000)

    Ok, Sorry to be so dense, but getting back to the original question; If the labels are made up of different "types" of information, e.i. text and number, how do I use the Union query whose requirements are "corresponding fields must be of the same type" to produce the label report to get the results and information I need?

    Thanks again,
    Louise

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing labels 2 (Office 2000)

    Does this do what you want to do?

    Try this against the NW DB, add CustomerName2 and put a couple of names in to test.

    <pre>SELECT Customers.CompanyName, Customers.Address, Customers.City, Customers.PostalCode
    FROM Customers
    WHERE (((Customers.CompanyName) Is Not Null))
    UNION
    SELECT Customers.CompanyName2, Customers.Address, Customers.City, Customers.PostalCode
    FROM Customers
    WHERE (((Customers.CompanyName2) Is Not Null));
    </pre>


    HTH, John

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing labels 2 (Office 2000)

    Hi John,
    Thank you very much. Sorry it took a couple of days to answer you, I had to get some regular work done. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    I tried the NW DB as you suggested but I'm going to have to play with it for a while longer. I noticed that the NW's customer table is all text, even the phone and fax fields. My table has all kinds of data types including number, yesno, date/time and text. Am I assuming correctly that ALL the fields in the table have to be the same type? or is it that the corresponding field name has to be the same type. Little confused here.

    The labels for the membership cards are only for NEW members, therefore when I run the query, I usually just have to enter the new account number(s) (number type) in the criteria and I only get those I need. Being new at Union queries, when I ran your example, it seems that I couldn't (or don't know how to) limit the results.

    Thanks again,
    Louise

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

    Re: Printing labels 2 (Office 2000)

    There is no requirement that *all* fields should be of the same type, just corresponding fields. For example, in the union query

    SELECT Field1, Field2 FROM Table1
    UNION
    SELECT Field3, Field4 FROM Table1

    the fields Field1 and Field3 must be of the same type, e.g. both text fields or both date fields. Similarly, Field2 and Field4 must be of the same type, for example both Yes/No fields or both number fields. But Field1 and Field2 may be of different types.
    You can specify a WHERE condition for each of the parts, for example:

    SELECT Field1, Field2 FROM Table1 WHERE Field5 = 1
    UNION
    SELECT Field3, Field4 FROM Table1 WHERE Field6 = "USA"

    The conditions can be the same or completely different, there's no requirement that the fields involved in the WHERE part are of the same type.

  11. #11
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing labels 2 (Office 2000)

    Thanks Hans! Will check it out.
    Louise

  12. #12
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing labels 2 (Office 2000)

    Hi,

    Ok, I'm getting closer but I don't know what I'm doing wrong. The following is giving me everyone that has a "second name" listed in the table, not just the one customer I'm looking for as listed in the first WHERE. If I enter the same WHERE statement under the second SELECT, I get errors. Also, what would I do if I had more than one? For example, 400980, 400505 and 400796?

    SELECT [AccountNumber], [LastName], [PCRMCardName], [BoatName], [ContractStart]
    FROM [YACHT BASIN CUSTOMERS]
    WHERE [AccountNumber] = 400980
    UNION SELECT [AccountNumber], [LastName], [2ndPCRMCardName], [BoatName], [ContractStart]
    FROM [YACHT BASIN CUSTOMERS]
    WHERE [2ndPCRMCardName] Is Not Null;

    Thanks again,
    Louise

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

    Re: Printing labels 2 (Office 2000)

    If records have to satisfy more than one condition, you use one WHERE clause with the two conditions connected by AND:

    WHERE condition1 AND condition2

    If you want to include records for which AccountNumber is one of a small range of values, you can use the following syntax:

    WHERE field In (value1, value2, value3)

    In your example:

    SELECT [AccountNumber], [LastName], [PCRMCardName], [BoatName], [ContractStart]
    FROM [YACHT BASIN CUSTOMERS]
    WHERE [AccountNumber] In (400980, 400505, 400796)
    UNION SELECT [AccountNumber], [LastName], [2ndPCRMCardName], [BoatName], [ContractStart]
    FROM [YACHT BASIN CUSTOMERS]
    WHERE [2ndPCRMCardName] Is Not Null AND [AccountNumber] In (400980, 400505, 400796);

  14. #14
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing labels 2 (Office 2000)

    Hi Hans,

    Thank you so much! <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22>

    In just reading it, I'm surprised at the term "in" instead of "is". Like... "the account number IS....." Good thing I don't do programing! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Thank you for the clearing up the AND. That makes sense, but "in"?

    Thanks again!
    Louise

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

    Re: Printing labels 2 (Office 2000)

    The keyword In can be used to specify a (small) list of values instead of a single one. To specify that a field should be one particular value, you use something like

    WHERE LastName = "Jones"

    To specify that a field should be one of two values, you can use

    WHERE LastName = "Jones" OR LastName = "Nesmith"

    Although you can expand this for more values, it quickly becomes tedious. As a more convenient alternative, you can use In (...)

    WHERE LastName In ("Jones", "Nesmith", "Tork", "Dolenz")

    You can also use Not In (...) to specify that a field should *not* be one of the listed values. See In Operator - Access (it's for Access 2003 but applies to all versions).

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
  •