none
quantity per package label

    Question

  • Hi,

    I have a form for inserting invoices, and on the subform I have a command button to print the labels, the label would contain the quantity of the product, so, for instance if the quantity is  11000 and package contains 2000 only so I have to print 5 labels with quantity 2000 and one label with 1000 qty what I need to do, is when I click the label cmd button to insert the records required to temp table to print the label

    I really appreciate any suggestion to accomplish this

    Saturday, June 22, 2013 10:31 AM

Answers

  • You don't need to use a temporary table for this, you can return the rows as the result table of a query.  Firstly you need to add an auxiliary Counters table with a single column Counter of Long integer data type and fill it with sequential numbers from 1 to some arbitrarily high number which will exceed the number of packages you'll ever need for one order item.  You can easily do this by serially filling down a column in Excel and importing the table into Access.  Then, taking the tables in my demo file Invoice.PDF.zip as an example, which you'll find in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    by adding a QuantityInPack column to the InvoiceDetails table and creating a simple form frmInvoiceDetails bound to this table, the following query does what you want:

    PARAMETERS Forms!frmInvoiceDetails!InvoiceNumber TEXT(14),
    Forms!frmInvoiceDetails!ProductID LONG;
    SELECT Counter AS LabelNumber, Product,Quantity AS TotalQuantity,
    QuantityInPack
    FROM Counters, Products INNER JOIN InvoiceDetails
    ON Products.ProductID = InvoiceDetails.ProductID
    WHERE Counter <= Quantity\QuantityInPack
    AND InvoiceNumber = Forms!frmInvoiceDetails!InvoiceNumber
    AND Products.ProductID = Forms!frmInvoiceDetails!ProductID
    UNION ALL
    SELECT Counter, Product, Quantity,
    Quantity - ((Quantity\QuantityInPack) * QuantityInPack)
    FROM Counters, Products INNER JOIN InvoiceDetails
    ON Products.ProductID = InvoiceDetails.ProductID
    WHERE Counter = (Quantity\QuantityInPack)+1
    AND InvoiceNumber = Forms!frmInvoiceDetails!InvoiceNumber
    AND Products.ProductID = Forms!frmInvoiceDetails!ProductID
    AND Quantity - ((Quantity\QuantityInPack) * QuantityInPack) > 0;

    You'd need to change the parameters from references to controls in the form to references to controls in your subform of course, with the usual syntax of:

    Forms!<ParentFormName>!<SubformControlName>.Form!<ControlName>

    Note how integer division is used to get the number of completely filled packs, and the quantity for any partially filled pack is then computed in the second part of the UNION ALL operation.

    Ken Sheridan, Stafford, England

    • Marked as answer by DBProject Saturday, June 22, 2013 1:22 PM
    Saturday, June 22, 2013 12:43 PM
  • I've just updated the file in my SkyDrive folder.  It now generates all the labels per invoice, rather than per invoice line.

    Ken Sheridan, Stafford, England

    • Marked as answer by DBProject Sunday, June 23, 2013 6:34 PM
    Sunday, June 23, 2013 6:08 PM
  • I've now had a look at the file and have uploaded an amended copy as Packs.zip to my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    The new box labels report uses a query very similar to the original one, but in this case, rather than referencing the InvoiceDetails table, references a query which returns the total number of boxes per order item. Note that this calls a RoundUp function which I've placed in the basRounding module.

    Ken Sheridan, Stafford, England

    • Marked as answer by DBProject Sunday, December 01, 2013 2:00 AM
    • Unmarked as answer by DBProject Sunday, December 01, 2013 2:01 AM
    • Marked as answer by DBProject Sunday, December 01, 2013 2:02 AM
    Friday, November 29, 2013 6:12 PM
  • I just ask if there a way to add the total quantity of the box to the box label
    I've posted an amended file to SkyDrive.

    Ken Sheridan, Stafford, England

    • Marked as answer by DBProject Monday, December 02, 2013 4:41 AM
    Sunday, December 01, 2013 4:57 PM

All replies

  • "when I click the label cmd button to insert the records required to temp table to print the label"

    Can you show us the code where you do that? It would seem you could store the "package quantity" in that temp table, and then modify your report to include that new data.

    Also, are you storing the "max package quantity" somewhere? Referring to your example, it seems that the "max package quantity" would be 2000 - are you storing that somewhere, or is this something the user enters when printing the labels?


    -- Scott McDaniel, Microsoft Access MVP

    Saturday, June 22, 2013 11:37 AM
  • hi,

    Thank you for your reply

    the max package quantity entered by the users when he inserts the invoice and stored within the invoices details table 

    <<It would seem you could store the "package quantity" in that temp table, and then modify your report to include that new data.>>

    that's true

    it seems like I can't split one record quantity to numbers of records based on the qty/p/ctn

    for example if I have this record in invoice item line

    Item, quantity (adapter, 11000) and qty-p-ctn is 2000
    so I would have records to make the labels

    adapter 2000
    adapter 2000
    adapter 2000
    adapter 2000
    adapter 2000
    adapter 1000

    thank you once again for your reply

    Saturday, June 22, 2013 11:46 AM
  • You don't need to use a temporary table for this, you can return the rows as the result table of a query.  Firstly you need to add an auxiliary Counters table with a single column Counter of Long integer data type and fill it with sequential numbers from 1 to some arbitrarily high number which will exceed the number of packages you'll ever need for one order item.  You can easily do this by serially filling down a column in Excel and importing the table into Access.  Then, taking the tables in my demo file Invoice.PDF.zip as an example, which you'll find in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    by adding a QuantityInPack column to the InvoiceDetails table and creating a simple form frmInvoiceDetails bound to this table, the following query does what you want:

    PARAMETERS Forms!frmInvoiceDetails!InvoiceNumber TEXT(14),
    Forms!frmInvoiceDetails!ProductID LONG;
    SELECT Counter AS LabelNumber, Product,Quantity AS TotalQuantity,
    QuantityInPack
    FROM Counters, Products INNER JOIN InvoiceDetails
    ON Products.ProductID = InvoiceDetails.ProductID
    WHERE Counter <= Quantity\QuantityInPack
    AND InvoiceNumber = Forms!frmInvoiceDetails!InvoiceNumber
    AND Products.ProductID = Forms!frmInvoiceDetails!ProductID
    UNION ALL
    SELECT Counter, Product, Quantity,
    Quantity - ((Quantity\QuantityInPack) * QuantityInPack)
    FROM Counters, Products INNER JOIN InvoiceDetails
    ON Products.ProductID = InvoiceDetails.ProductID
    WHERE Counter = (Quantity\QuantityInPack)+1
    AND InvoiceNumber = Forms!frmInvoiceDetails!InvoiceNumber
    AND Products.ProductID = Forms!frmInvoiceDetails!ProductID
    AND Quantity - ((Quantity\QuantityInPack) * QuantityInPack) > 0;

    You'd need to change the parameters from references to controls in the form to references to controls in your subform of course, with the usual syntax of:

    Forms!<ParentFormName>!<SubformControlName>.Form!<ControlName>

    Note how integer division is used to get the number of completely filled packs, and the quantity for any partially filled pack is then computed in the second part of the UNION ALL operation.

    Ken Sheridan, Stafford, England

    • Marked as answer by DBProject Saturday, June 22, 2013 1:22 PM
    Saturday, June 22, 2013 12:43 PM
  • thank you very much Ken Sheridan

    that's exactly what I need to do, just I have to say thank you again and over again

    I really appreciate your help

    Saturday, June 22, 2013 1:28 PM
  • Glad to have helped.  Because this is an interesting little task, and from time to time we've had similar questions here and elsewhere, I'm going to wrap this up in a simple little demo file which I'll add to my public SkyDrive folder.  I'll post back here with the link once I've done so.

    Ken Sheridan, Stafford, England

    Saturday, June 22, 2013 5:11 PM
  • Hi,

    I am very happy to hear that you will make a demo for others benefit 

    thank you once again

    Saturday, June 22, 2013 5:16 PM
  • I've now uploaded the demo file Packs.zip to my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Ken Sheridan, Stafford, England

    Sunday, June 23, 2013 4:28 PM
  • I've just updated the file in my SkyDrive folder.  It now generates all the labels per invoice, rather than per invoice line.

    Ken Sheridan, Stafford, England

    • Marked as answer by DBProject Sunday, June 23, 2013 6:34 PM
    Sunday, June 23, 2013 6:08 PM
  • hi, Ken Sheridan

    It's a long time since i posted this thread, but I have an obstacle about adding more labels for packaging 

    using the demo that you provided us on SkyDrive folder I can print a number of labels for packages depending on the package  standard quantity 

    assume, the package is a 10 units and I will deliver 105 units

    the example we have prints 11 labels (10 labels with 10 & 1 label with 5)

    now I need to print labels for the bags, a bag would contain number of packages 

    so, assume the bag contains 4 packages, so I need to print 3 labels (2 labels with [4 package = 40 units] for each bag

    and one label for the third bag [2 package = 15 units] 

    I really appreciate your help

    Thank you very much


    dbprojecttoday

    Thursday, November 28, 2013 9:43 PM
  • I'll try and have a look at the file to see if it can be extended to do this.  I'm going to be tied up with one or two things over the next few days, so I might not be able to do anything for a little while.  I'll post back in due course.

    Ken Sheridan, Stafford, England

    Thursday, November 28, 2013 11:54 PM
  • I've now had a look at the file and have uploaded an amended copy as Packs.zip to my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    The new box labels report uses a query very similar to the original one, but in this case, rather than referencing the InvoiceDetails table, references a query which returns the total number of boxes per order item. Note that this calls a RoundUp function which I've placed in the basRounding module.

    Ken Sheridan, Stafford, England

    • Marked as answer by DBProject Sunday, December 01, 2013 2:00 AM
    • Unmarked as answer by DBProject Sunday, December 01, 2013 2:01 AM
    • Marked as answer by DBProject Sunday, December 01, 2013 2:02 AM
    Friday, November 29, 2013 6:12 PM
  • Hi Ken Sheridan,

    Thank you very much for you fast respond, I really appreciate your help

    It works great

    I just ask if there a way to add the total quantity of the box to the box label

    Thank you very much for your help


    dbprojecttoday

    Sunday, December 01, 2013 2:00 AM
  • I just ask if there a way to add the total quantity of the box to the box label
    I've posted an amended file to SkyDrive.

    Ken Sheridan, Stafford, England

    • Marked as answer by DBProject Monday, December 02, 2013 4:41 AM
    Sunday, December 01, 2013 4:57 PM
  • Hi Ken Sheridan,

    I really appreciate your dedication

    Thank you very much


    dbprojecttoday

    Monday, December 02, 2013 4:41 AM
  • Hi Ken Sheridan

    I get a wrong values for quantity in box,  I am trying to get the correct values on the box label, but I can't figure out how to handle this.

    I get the total quantity of the invoice for each lable, which means the number of labels is correct but the quantity are all the same as the quantity of the invoice.

    I really appreciate your help


    dbprojecttoday

    Wednesday, December 18, 2013 4:13 AM
  • As far as I can see it's showing the correct values with the data in my demo.  If I'm misunderstanding what you expect to see on the labels can you give me chapter and verse as to where my demo's labels are incorrect.  Or if it's showing correct values with the current data can you amend the data so that it shows incorrect values.  Until I can break it I can't fix it.

    Ken Sheridan, Stafford, England

    Wednesday, December 18, 2013 11:00 AM
  • Hi,

    Basic widget product if the QuantityInPack is 250 and PackInBox is 2 so, if the quantity in the invoice is 750 in I suppose to get 2 boxes labels one for 500 units and one for 250 units and the result is perfect

    But if the quantity is 751 or over the quantity of each label will be 500 

    if there's a way to trap this without adding new field to products table to define the total box quantity will be really appreciated

    thank you very much again 


    dbprojecttoday

    Friday, December 20, 2013 12:17 PM
  • I can see where the bug lies.  If the total pack count integrally divides by the number of boxes then the first part of the UNION ALL operation returns one too many rows, and the second part returns no rows.  This is fine where all packs are full, but not if one pack is only partially filled.

    At the moment I can't readily see a solution, but I'll think about it and post back in due course.  It may not be soon, however, and if I can't find a solution it will be necessary to drop the BoxQuantity column from the query and hence from the label.

    Ken Sheridan, Stafford, England

    Friday, December 20, 2013 6:20 PM
  • I've made another stab at the query and uploaded the amended file as Packs.zip to:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    See if this fits the bill.


    Ken Sheridan, Stafford, England

    Saturday, December 21, 2013 4:21 PM