Label Challenge (2000 SP3)

Topic

New Reply

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?

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

You could use the following kludge:

• Create a new query based on SummaryofBoxesperStudent.
• Set the Sort Order for the quantity field to Descending.
• Set the Top property of the query to 5.
• Save this query as, say, qryTop5.
• Create a table with a single field SequenceID (number, long integer)
• Populate this table manually with values 1, 2, 3, etc., up to half the maximum number of sold items you expect.
• Create a new query; add SummaryofBoxesperStudent and the table you just created, but don’t join them.
• Add the fields from SummaryofBoxesperStudent that you need, and the SequenceID field from the table.
• In the criteria for StudentID, enter Not In (SELECT StudentID FROM qryTop5). This excludes the top 5 sellers.
• 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)
• Save this query.
• Base a report on this query.
  [/list]

Reply | Quote

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

With Thanks
Doug

Reply | Quote

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

With Thanks
Doug

Reply | Quote

You could use the following kludge:

• Create a new query based on SummaryofBoxesperStudent.
• Set the Sort Order for the quantity field to Descending.
• Set the Top property of the query to 5.
• Save this query as, say, qryTop5.
• Create a table with a single field SequenceID (number, long integer)
• Populate this table manually with values 1, 2, 3, etc., up to half the maximum number of sold items you expect.
• Create a new query; add SummaryofBoxesperStudent and the table you just created, but don’t join them.
• Add the fields from SummaryofBoxesperStudent that you need, and the SequenceID field from the table.
• In the criteria for StudentID, enter Not In (SELECT StudentID FROM qryTop5). This excludes the top 5 sellers.
• 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)
• Save this query.
• Base a report on this query.
  [/list]

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote