split record quantity to multiple reocords based on qty per package (1 Viewer)

[Zhang]

Hi,

I have a form for inserting invoices, and on the subform records I have a command button to print the labels, the label would contain the quantity of the product, so, if the quantity is ex. 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 6 records required to print the labels to temp table

thank you very much

 

[jjturner]

welcome to AWF!

I've dropped in here after a long hiatus from AWF myself...

So does your Products table have a PackageQuantity field? Or are there possibly options for PackageQuantity that can be chosen for the Products (i.e., "bulk", "standard", "sample", etc.)?

 

[Zhang]

hi,

thank you for your reply

the user enters the package quantity and the value is stored in the invoice item line tables

 

[jjturner]

Correct me if I'm wrong - there are 3 Quantities:

• Product Quantity
• Products per Package Quantity
• Number of Packages

Which Quantities is the user entering?

 

[Zhang]

you are completely correct

the user enter both quantities, package & product quantity

the package entered and stored just for printing the labels and to follow up how many ctn has been transferred to the customer

 

[jjturner]

Hmm.. it seems odd to me that user can/should determine both Total Quantity of Product and Quantity of Product per Package...

I would expect the Supplier or Shipper to determine the latter Quantity rather than the Orderer of the Product..

Or maybe the user IS the Supplier or the Shipper and they're inheriting the initial Product Quantity, and now need to allocate that to Packages??

So my initial thought was to add a "threshold" field in your Products table like "max_qty_per_pkg" -- and the value for that would be 2000 for your example.

Please excuse my questions since I haven't dealt extensively with inventory/order systems... so I'm just thinking out loud. But everything boils down to proper table structure. It might help if you could post your table structure...

 

[Zhang]

as we are working on mass production process, so the user enter the quantity per package as it would vary

because the customer issues a purchase order with quantities and we fill this order but not all the quantities required at a time
we send the quantities on stages so the quantity of package would be different from time to time and as the internal quality control checks the units to be delivered so, if the quality control today tested 5000 units on 5 phases then they will package the tested quantity as the QC submitted the quantities to warehouse so the package label would contain the quality controller who tested the quantity, caliber Id,time tested, order id, receiptid, productid, address and quantity

that's to make the whole process clear for you

 

[jjturner]

Sorry, I would need to see your table structure in order to help you.

It sounds like the number of subform records is already the same as the number of packages...

But if you only needed to calculate the number of packages, then you could use the formula:
# Packages = [Total Quantity] \ [Quantity per Package] + [Total Quantity] MOD [Quantity per Package]
(Note the direction of the division operator)

 

[Zhang]

Hi,

I created a counter table and using a query we will get the required labels

thank you