Void transaction, return product to inventory in access db modeled after Northwind Traders

[KowalD]

I have used the Northwind Traders template to develop a database for a fruit tree nursery. Have fought through most of the problems I've encountered but can't find my way past this... If someone could show me how to solve it in the Northwind Traders db I would sure be grateful.

Can enter a new customer purchase and all goes well.... But if a customer wants to cancel or change an order before pickup, I can cancel, but allocated stock is not returned to inventory. I'm assuming VB code with record set wrapper is how I need to address this, and that several tables (products, orders, order details and inventory transactions) are involved, but I just can't think my way through this.

Anyone who could show me how to change the code (or add) to the Northwind Traders db so I could see the process would have my undying gratitude!

Thanks, in advance....

[June7]

I've never used any Northwind template. How does it track inventory? Is inventory balance calculated from raw data? Or does it actually modify a saved balance value? The latter is approach I always advise against.

How do you 'cancel' - do you delete record?

[orange]

As June suggested, many on the forum are not necessarily familiar with the workings of the Northwind database. There have been iterations of the "Northwind" over the versions of Access. Many have used it (them) for learning concepts - but not as an application in itself.

Here are some thoughts for consideration ( I don't have/use Northwind)
-When you have an Order do you/could you mark the Products/Items involved such that they are pending pickup?
-I'm thinking probable Order vs physically completed Order
-If the Order is cancelled, perhaps add logic to remove the mark and Product/Item show it in Inventory
-It seems you immediately allocate/move Product/Item from inventory to the Order

-Have you created a relationship diagram/data model showing tables and inter-relationships That is often a good place to start-it gets you "intimate knowledge" of tables, fields and relationships.
-Another technique is to map out your business processes using data flow diagrams(DFD) showing the logic involved and the data movement and stores. You could map out the logic (and the data requirements) for your "OrderCancelledBeforePickup" process.

All of this is for consideration --not necessarily a direct answer to your specific question.

There are short, humorous free videos on analysis and design including DFD from BA_Experts.

Here is info on database design and models.

Good luck.

Note: It could be helpful to readers if you could post a copy of your db (with minimal data and nothing private). We are interested in structure, not your data. Also, could you identify which Northwind version you have.

Also: I found this article on Northwind https://theaccessbuddy.wordpress.com...ase-explained/ which says--

It contains the following detailed information :

Suppliers/Vendors of Northwind – who supply to the company.
Customers of Northwind – who buy from Northwind
Employee details of Northwind traders – who work for Northwind
The product information – the products that Northwind trades in
The inventory details – the details of the inventory held by Northwind traders.
The shippers – details of the shippers who ship the products from the traders to the end-customers
PO transactions i.e Purchase Order transactions – details of the transactions taking place between vendors & the company.
Sales Order transaction – details of the transactions taking place between the customers & the company.
Inventory transactions – details of the transactions taking place in the inventory
Invoices – details of the invoice raised against the order.