Solved Query for the balance stock material

[hrdpgajjar]

Hi all,
I am trying to create a purchase order management system.

there are main three stages of it,

1. Create a Purchase order (Create a customer with total quantity)
2. Create a dispatch order from the total quantity entered while making PO or customer. There are several Dispatch orders for one single customer.
3. View balance Stock or pending quantity to dispatch

I have covered first two stages but stuck at third one, i.e. how to find balance or remaining dispatch order quantity by making a query.

I have put the media fire link here as my database file is large (17 mb) so pls bare with me on this.

You can download my database file with the link below,

Purchase Order Management System

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

pls help on this


Thank You,

 

[arnelgp]

you got a wrong code there on "View Total PO Material" button.
you are opening TotalMaterialSubF with a criteria CustomerID.
you should use Criteria: PurchaseOrderNo, because this is Unique.

you can't use CustomerID as criteria, since if you want to create New PO on
same customer, it will show all PO's products, instead of being confined to
that specific PO only.

 

[hrdpgajjar]

you got a wrong code there on "View Total PO Material" button.
you are opening TotalMaterialSubF with a criteria CustomerID.
you should use Criteria: PurchaseOrderNo, because this is Unique.

you can't use CustomerID as criteria, since if you want to create New PO on
same customer, it will show all PO's products, instead of being confined to
that specific PO only.

Hi there,
View Total PO Material query is to verify total order items anytime while making DO. Later my plan is to limit Total DO quantity to not more than entered PO quantity. (I still not able to find logic on how to do it though ) It would be great help if u make that query for me on my attached database file.



Thanks

 

[GPGeorge]

Hi there,
View Total PO Material query is to verify total order items anytime while making DO. Later my plan is to limit Total DO quantity to not more than entered PO quantity. (I still not able to find logic on how to do it though ) It would be great help if u make that query for me on my attached database file.



Thanks

For an example of how one can manage stock for orders, download and study how we did it in the Northwest Developer Edition template.

You can also watch a couple of videos in which two of the team who created the templates explain Orders and Inventory.

 

[hrdpgajjar]

For an example of how one can manage stock for orders, download and study how we did it in the Northwest Developer Edition template.

You can also watch a couple of videos in which two of the team who created the templates explain Orders and Inventory.

It gives an error while opening Northwind Databse developer edition

 

[hrdpgajjar]

you got a wrong code there on "View Total PO Material" button.
you are opening TotalMaterialSubF with a criteria CustomerID.
you should use Criteria: PurchaseOrderNo, because this is Unique.

you can't use CustomerID as criteria, since if you want to create New PO on
same customer, it will show all PO's products, instead of being confined to
that specific PO only.

This query finally solves my problem, thanks for the assist,


SELECT CustomerT.CustomerName, ProductT.ItemCode, ProductT.Description, TotalOrderT.Quantity AS [Total Quantity], DispatchOrderT.DispatchOrderDate, OrderDetailsT.Quantity AS [Do Quantity], [Total Quantity]-[Do Quantity] AS [Remaining Quantity]
FROM ProductT INNER JOIN (CustomerT INNER JOIN ((TotalOrderT INNER JOIN OrderDetailsT ON TotalOrderT.ProductID = OrderDetailsT.ProductID) INNER JOIN DispatchOrderT ON OrderDetailsT.DispatchOrderID = DispatchOrderT.DispatchOrderID) ON CustomerT.CustomerID = TotalOrderT.CustomerID) ON ProductT.ProductID = OrderDetailsT.ProductID;

 

[GPGeorge]

It gives an error while opening Northwind Databse developer edition

What is the error?

 

[hrdpgajjar]

What is the error?

Screenshot attached herewith.

 

[GPGeorge]

Thank you.

Did you put the accdb produced by the template in a Trusted Location? Did you enable macros?