Product Unit definition in MS access

[siamandm]

Hello All
If I need to add product to the table product using this Form below, each product will have different unit:

Screenshot 2024-05-19 152853.png

in this example for the productID =1 we have three different unit Vial,box and cartoon as shown and this units i have saved them in a different table(ProductUnitT)
now we when make a purchase of an item i have to choose one of these units, and when i sell them I may sell them in different unit
for example we my buy the product in Cartons lets say 1 Carton which is 50Box as i defined , and when we sell them we may sell them in boxes , first time 20 second time 30 boxes

How do I use the ProductUnit table with the Purchase Details table to manage this? Additionally, the system needs to check the stock and issue a warning if there isn't enough in stock.

Screenshot 2024-05-19 153352.png

T

[Gasman]

Perhaps have a calculated field that informs you of the actual units, or set the amount when you add new stock?

[siamandm]

I think we have to do some modification to the table designs as well, and create relation between PruchasedetailsT or ProdutLotT table with ProductUnitT table,
for this I need someone to guide me
I have found this article online, someone asked same question https://stackoverflow.com/questions/664 ... and-retail

[EileenStifone]

The types of packaging can only be:

• single product

• box

• carton

[siamandm]

after doing some search online, i come across with this design below,
but still i need help

Screenshot 2024-05-20 102258.png

[EileenStifone]

But you didn't answer the question in the post dated May 20, 2024, 9:16 am

[siamandm]

The types of packaging can only be:

• single product

• box

• carton

Depend on the type of the product, but in general it will be small unit to medium to big
Like vial, kits/25 and box
Another example, vial, stripe and capsule

[siamandm]

Finally, I have come up with the design below. Please provide your input on whether it needs corrections or improvements.

Screenshot 2024-05-21 105607.png

Thank you.

[HansV]

Siyamand has now asked in I need support in my database Design..
Please continue the discussion there. I will lock this thread.