create inventory in Access (1 Viewer)

[mabino79]

thanks for your valuable advise, have done all the changes as advised.

 

[GinaWhipp]

Will each Order only be one line? Is tblPOfolio the Orders? Will the Orders only have one line?

 

[mabino79]

Yes

Will each Order only be one line? Is tblPOfolio the Orders? Will the Orders only have one line?

Yes

 

[Pat Hartman]

Is each PO for a specific client? If not, Gina is correct, you need to look at other models. Generally PO are separate from Orders. PO's add items to inventory and Orders take items from inventory. For the reserve, I would use an Order with a future ship date.

 

[arnelgp]

For the reserve, I would use an Order with a future ship date.

there is no such thing as reserved.
either there is a Sale or a Sale Order (this is the reserved. need to be delivered or to be picked-up?)

 

[GinaWhipp]

So a Client gives you a Sales Order and you use that for the Purchase Order for the Supplier? How? You may want to order more than just the one Client ordered.

If you change the quantity on the Order than it also changes it for the Client. You also have to consider trying to to Stock-on-Hand. How will you know what is part of Inventory and what is actually sold.

 

[Pat Hartman]

Why are the last four attributes of the folio table on each item rather than on the PO header?

 

[rainbows]

In my opinion You need supplier orders. you need goods inwards , which books in part shipments , reject parts by the QC Department , you need stores to book in the inspected parts in . you need sales that allocated the parts from inventory for that order. you can have allocated ( pending suppler orders due in )

 

[rainbows]

this is how I set mine up

 

[mabino79]

have done with below query

SELECT tblProducts.*, tblVendor.VendorName, tblFolio.*, tblFolioType.*, IIf([tblFolioType]![TypeofFolio]="Purchase",[tblFolio]![Qty],-[tblFolio]![Qty]) AS ActualQty
FROM tblVendor INNER JOIN (tblProducts INNER JOIN (tblFolioType INNER JOIN tblFolio ON tblFolioType.FolioID = tblFolio.TypeofFolio) ON tblProducts.ProductsID = tblFolio.Product) ON tblVendor.VendorID = tblProducts.Vendor;

 

[GinaWhipp]

have done with below query

SELECT tblProducts.*, tblVendor.VendorName, tblFolio.*, tblFolioType.*, IIf([tblFolioType]![TypeofFolio]="Purchase",[tblFolio]![Qty],-[tblFolio]![Qty]) AS ActualQty
FROM tblVendor INNER JOIN (tblProducts INNER JOIN (tblFolioType INNER JOIN tblFolio ON tblFolioType.FolioID = tblFolio.TypeofFolio) ON tblProducts.ProductsID = tblFolio.Product) ON tblVendor.VendorID = tblProducts.V

I'm not seeing how that query is going to give you quantity-on-hand. Is that the problem?

 

[mabino79]

I'm not seeing how that query is going to give you quantity-on-hand. Is that the problem?

have created in Report

can you tell me how can i get the sum of the same products row selected from Sub Form on main form

 

[GinaWhipp]

have created in Report

can you tell me how can i get the sum of the same products row selected from Sub Form on main form

View attachment 96887

Not with that tables you have. That is what everyone has been trying to explain. You can't put a 3,000 sq. ft. house on a 2,000 sq. ft. foundation.

 

[mabino79]

Not with that tables you have. That is what everyone has been trying to explain. You can't put a 3,000 sq. ft. house on a 2,000 sq. ft. foundation.

than can you suggest, how can i complete this

 

[oleronesoftwares]

there is no such thing as reserved.
either there is a Sale or a Sale Order (this is the reserved. need to be delivered or to be picked-up?)

seems reserved means booked, so as u suggested it should go to sales order, not sales, but the question is how is the reserved treated in store

1. Does it reduce the quantity in store?
2. The booked quantities must have a grace period for the buyer to pick up or make payment to confirm booking

 

[mabino79]

seems reserved means booked, so as u suggested it should go to sales order, not sales, but the question is how is the reserved treated in store

1. Does it reduce the quantity in store?
2. The booked quantities must have a grace period for the buyer to pick up or make payment to confirm booking

it will be same like sale, but the invoice details will added later only

 

[GinaWhipp]

than can you suggest, how can i complete this

In post #14 @arnelgp posted a sample you could build on. In post #20 I posted an example you could build on. You can also have a look at...

Allen Browne - Inventory Control: Quantity on Hand

How do calculate the quantity-on-hand for inventory applications, rather than rely on a stored value.

allenbrowne.com

 

[oleronesoftwares]

than can you suggest, how can i complete this

You have to re-draw the flow of the inventory process

Make sure tables exists for each of the processes
Make sure relationships exists between tables that have overlap functions with each other.

Also look at the samples posted by other users on this thread.

 

[mabino79]

You have to re-draw the flow of the inventory process

Make sure tables exists for each of the processes
Make sure relationships exists between tables that have overlap functions with each other.

Also look at the samples posted by other users on this thread.

in the sample there is only one table for all transactions like Purchase (+), Sale & Reserve (-)

the same i took

 

[mabino79]

the sample was like this