How to Match Goods received note value Vs Purchases order value in Ms Access

Dear All;

See how you can assist on the issue below:

Background

We place purchases orders to vendors every month, now it is the policy of management to match the goods received note with the purchase order so that in the event where some material are not received as per purchase order management can easily track that information. Therefore , I'm required to create a VBA code to do the matching so that those unfinished  purchase orders can be properly managed.

Here I want to be matching the Purchase order value vs goods received note value so that those that completely  received should show zero and those not finished show the remaining balance any Idea?

Regards

Chris
LVL 3
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel PineaultPresident / Owner CARDA Consultants Inc.Commented:
Your inventory could include a PO number and ins vs outs will be positive values vs negative values so you could easily create a query to perform a summation based on POs and extract those that have a difference (not equal to 0) as those would be the POs that were not respected.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Most Purchase Order systems have some sort of "Receiving" functionality as well. Generally this is handled in a "Transactions" table, which records stock/inventory movements in your system. A PO would not generate a transaction, but the act of Receiving that PO would.

If you record incoming stock, and store the PO associated with that incoming stock, you could then easily compare the amounts received with the amounts expected.

Depending on complexity, you may also have a table to relate those incoming transactions with one or more other objects in the database. For example, you may receive raw stock items to be used on specific Jobs. If so, you may have a 3rd table that stores those relationships.
David BernsteinChief Developer/ Microsoft AccessCommented:
Basically any business system can be managed by having a transaction table. Each transaction has several fields Purchase order date, purchase order number, product number, quantity field. In an accounts receivable system the invoice number, amount, etc. The amount can be a positive or negative number. For instance in the purchase order system, the original amount is a positive and the receipt is a negative (which represents a decrease to the amount on order. Creating a query grouping by purchase order number and product number and summing the quantity field gives you the balance of each product still not received.
This model can be used to model many different types of business systems.
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAuthor Commented:
Thank you so much as per your advise I have sorted out this issue by ensuring that first there is a relationship with the goods received note and the purchase order and base the query you mentioned above on those tables.

Thank you once again to you all

regards

Chris
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.