Batches setup in tables

[AndyC121]

I have a batch of products that has one unique number (PK) but this needs to be split into several smaller batches but still be linked to the original batch(PK) for tracking purposes. so say a batch of 20 items is split into 4 = 5 batches. can i have the original (PK) eg: 001 and then the second of this split batch being 001A and so on.


How would i go about doing this and how would it be setup? thanks in advance.

[Homegrownandy]

Rather than trying to find an answer to what you think is the solution, ask a question that describes your requirements. Describe what a product is, why it needs to be in a batch, what the purpose of this system is. If you can show a screenshot of the relationships relating to this problem that would help also.

I feel like you don't need the same key at all. just correctly defined relationships.

[AndyC121]

Hi Homegrownandy
A customer send us their product to be worked on. This product can be 10" machined bars in a batch of 300 but sometimes larger bars or less/more quantity, we unpack it and put it into OUR boxes in smaller batches, depending on product size/amount we can only fit say 20 per box. 300/20 =15 of OUR boxes.
They then go through work areas at our factory being worked on. there are different stages of processes they go through and at times boxes can end up at different areas/stages within this processes. tracking them is difficult as I'm using barcoding, so if say the original batch has a number 001 after splitting the batch into 15 boxes they all have number 001 it wouldn't tell me where all 15 boxes in the factory. And they all still need to relate to the original batch (001) Obviously we have more than 1 customer sending in product so we can have thousands of boxes moving around the factory.
Hope this is alittle clearer.

[Homegrownandy]

so, the amount per box has to be defined by a person I would assume. someone has to say.. only 20 will fit in these boxes? or do you imagine the system can say that depending on some variables such as the size?

do you have different size boxes? will this change?

[Homegrownandy]

This is a rough example for what I think you are after. you still need a way of limiting the max per box.

[ssanfu]

My $0.02.....



The table "ProdRepacks" is YOUR system. Customer batch (300) broken down to your smaller batches (20) in 15 boxes.
The Company batch (001) is now in 15 boxes (barcodes 101 - 115).
You can track each box by the "RepackBatchBarcode" and look up the Customer (main) barcode.....
Or even have both barcodes on the smaller boxes......

[AndyC121]

Hi Steve, can the repack table automatically generate the repack barcode (or numbers in this case) for the boxes needed (barcodes 101 - 115). each customer has a set number that will fit in our boxes, so this could be a table of customers and the set amount to fit into boxes, then generate labels for X amount of boxes numbered 101-115.
Thanks all for your help so far
Andy

[orange]

You may get some ideas from this older post re putting items into boxes.

You have many Customers, and each Customer can provide many Products, and each of your Boxes
can contain a certain number of Products from each Customer??? It seems a bit confusing, you'd have to know how many of each Product (perhaps Customer Product) will fit into 1 of your boxes.

Does each Product have same dimensions/weight etc regardless of which Customer supplies it??
It seems you have only 1 box size --is that true?
Good luck.

[AndyC121]

Yes we only have one box size, all customer product fits length wise into our boxes but some are wider product so certain customers will have x5 per box and some x8 per box etc. This we know and is listed againt each customer and can be put into a table which then can be calculated to work out how many boxes are required.

[ssanfu]

Hi Steve, can the repack table automatically generate the repack barcode (or numbers in this case) for the boxes needed (barcodes 101 - 115). each customer has a set number that will fit in our boxes, so this could be a table of customers and the set amount to fit into boxes, then generate labels for X amount of boxes numbered 101-115.
Thanks all for your help so far
Andy

Sure, why not? I haven't worked with barcodes much, but you could have a form to select the customer/customer ID/ ProdBarcode, have code calculate the number of repack boxes, enter the records into the repack table, assign RepackBarcodes, then print the labels (with all kinds of info). With appropriate error handling code, of course.

It all depends you your table structures. I don't know all of your requirements - my example was just a first pass suggestion.
Draw your design on whiteboard, paper,cardboard, stickies, whatever BEFORE you start creating objects in Access. Try entering data in the design to see if it will work.

Read the referenced link from orange. Might give you other ideas....

[orange]

You will also need some sort of structure and related processes to deal with locations and activities within your org/factory if this is part of your requirement

....if say the original batch has a number 001 after splitting the batch into 15 boxes they all have number 001 it wouldn't tell me where all 15 boxes in the factory....

[AndyC121]

Ive uploaded my database with some test data in it, this was an attempt before I asked questions in this thread. (removed everything but tables and main form)..can this be modified to incorporate splitting the batch? tblAddresses CustomerID is the only unique identifier as POnumber and BatchNo can sometimes be reused by the customer.

[ArviLaanemets]

Returning back to original question.

Let's assume you have a products table tblProducts: ProductID, ... (I can't look at your database currently, as I am on vacation, and I don't have Access at home).
Add a table tblProductVariations: ProductVariationID, ProductID, VariationID, PackingQty (ProductVariationID may be autonumeric. VariationID may be as you wanted, you enter it when the product variation is declared, and ProductID together with VariationID must be defined as unique index to prevent double entries. PackingQty is an integer determing, what is max number of products fitting into your box - when you have severeal different sizes of boxes, it will be more complex, but it looks this is not the case for you.).
Then there is a table tblCustomers: CustomerID, ...
The customer does send some products to you, so p.e. a table tblCustomerOrders: CustomerOrderNo, ... is needed.
Every order has one or several subrows, every one for one specific ProductVariation -
tblCustomerOrderRows: CustomerOrderRowID, CustomerID, CustomerOrderNo, OrderRowNo, ProductVariationID, Qty, ...

I think it's all you need to start at.

[ArviLaanemets]

Another way is to define every part with different dimensions (or other properties) as separate product. And of course for every product you must determine, how much of them you can put into your box. You can define for every such product a product group, where the product belongs to. (Those product groups will be practically, what currently your products are).

[ssanfu]

I looked at your dB.......

So I pretty much redesigned... well most of it. Is this anything close to what you envisioned??

(BTW, I had to uncheck the reference to the barcode ocx)