Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Brandon, Manitoba, Canada
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Label Challenge (2000 SP3)

    Is there a way I can correlate the number of labels printed for each person in one table with a "quantity" field in another table, or within a query?

    I developed an access 2000 database for a city band to track its fruit sale. Now they would like to print off one ballot for every two boxes of fruit each student has sold. (there will be prizes).
    Applicable Tables:
    Students - contains names of sellers. Not all have sold.
    Orders - contains studentid, orderid, customerid, date
    Orderdetails - contains the quantity field I want to reference.
    Customers - probably not needed in this equation but fyo
    Products - probably not needed in this equation but fyo
    Query:
    SummaryofBoxesperStudent - displayes the total boxes each student has sold. Sum(OrderDetails.Quantity) and grouped by Students.studentid

    How can I create a report that will print one label for every two boxes a student has sold?

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

    Re: Label Challenge (2000 SP3)

    What does printing labels have to do with this? Or are you talking about label controls? Can you explain a bit more about what you're trying to accomplish?
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Brandon, Manitoba, Canada
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Label Challenge (2000 SP3)

    I want to create a report formated for printing lablels. There would be one label on the report for each student who has sold two boxes of fruit. I would therefore need to have a qery to base the report on that would list every student as many times as the quantitysold/2. A further twist: I just found out the query should exclude the top 5 sellers! Hope this is clearer than the soup I'm about to eat.

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

    Re: Label Challenge (2000 SP3)

    You could use the following kludge:
    <UL><LI>Create a new query based on SummaryofBoxesperStudent.
    <LI>Set the Sort Order for the quantity field to Descending.
    <LI>Set the Top property of the query to 5.
    <LI>Save this query as, say, qryTop5.
    <LI>Create a table with a single field SequenceID (number, long integer)
    <LI>Populate this table manually with values 1, 2, 3, etc., up to half the maximum number of sold items you expect.
    <LI>Create a new query; add SummaryofBoxesperStudent and the table you just created, but don't join them.
    <LI>Add the fields from SummaryofBoxesperStudent that you need, and the SequenceID field from the table.
    <LI>In the criteria for StudentID, enter Not In (SELECT StudentID FROM qryTop5). This excludes the top 5 sellers.
    <LI>In the criteria for SequenceID, enter <=[SumOfQuantity]/2 (substitute whatever the quantity sold field is called). This creates the needed number of copies.
    (Note: some browsers mangle the comparison operator, it should be < = without a space in between)
    <LI>Save this query.
    <LI>Base a report on this query.[/list]

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Brandon, Manitoba, Canada
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Label Challenge (2000 SP3)

    Hans. You're a genius. It works! We'll dedicate a march to you at the next concert.

    With Thanks
    Doug

Posting Permissions

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