Access JumpStart 2.0 | Blog

A Rapid Development Framework for Microsoft Access

When entering an order query for how much inventory is available

by | Sep 11, 2020 | MS Access Queries | 0 comments

Presented with the problem:

You have a stock of inventory of items you are renting.

You know how many you have purchased.  You have what customers have ordered for particular dates.

You want to know while entering an order, how many items are available for a given date range for leasing / returning the item in question.

If the orders overlap at all, we are assuming that the item will not be available for that rental order.

Important Elements of Data Structure:

RentalInventory: PartID, PartDescription, AssetQty

RentalOrders: OrderID, SetupDate, ReturnDate

RentalProducts: OrderID, PartID, Qty (This is the line items table for orders)

Solution:

We need to know what item is going to be rented, when it would be leased and when it would be returned.  Given this information we need to determine how many parts are going to be left in our available leasing inventory.

  1. First we need to consider all the ways an existing order might overlap our new order we are creating.
    • Let’s use a chosen setup date of 3/1/2020 and a chosen return date of 3/8/2020.
    • If our order setup date is on or before 3/1/2020 and our order return date is on or after 3/8/2020 then that quantity is not available. (Setup <= 3/1/2020 AND Return >=3/8/2020)
    • If our order setup date is between 3/1/2020 and 3/8/2020 then that quantity is not available. (Setup Between 3/1/2020 and 3/8/2020)
    • If our order return date is between 3/1/2020 and 3/8/2020 then that quantity is not available. (Return Between 3/1/2020 and 3/8/2020)
    • Any of the above conditions will result in the part quantity being unavailable during the chosen ssetup and return period.
  2. Now that we have determined the conditions of the orders that will overlap our chosen setup and return date, how would we write a query with conditions that would return the sum of the quantity of all orders for that part?
    • First we would open the query builder and create a new query.  Add the RentalOrders table, the RentalProducts table, and the RentalInventory table.
    • Make sure to link the Rental Orders OrderID to the RentalProducts OrderID field and the RentalProducts PartID field to the RentalInventory PartID field.
    • Add the PartID field from RentalProducts, the Qty field, the AssetQty field, the SetupDate field, the ReturnDate field, and finally add the PartID field from RentalProducts again.
    • Right click on any of the fields in the grid below and choose the Totals option to add the Total line.
    • For the first PartID field choose Total: Group By, Qty choose Sum, AssetQty choose Group By, SetupDate choose Where, ReturnDate choose Where, and for the last PartID choose Where
    • Under the PartID in the Criteria row, add [ChosenItemID] in brackets and copy to the next 2 rows, it should be in 3 rows total.  This will prompt us for the PartID when running the query and only use that PartID
    • Under SetupDate we are going to add 2 entries under the criteria.  On the first line enter <= [ChosenSetupDate], on the second enter Between [ChosenSetupDate] And [ChosenReturnDate]
    • Under ReturnDate we are going to add 2 entries, skipping a line between them.  On the first line (next to the SetupDate entry) enter >= [ChosenReturnDate], leave the second line blank, and on the third line enter Between [ChosenSetupDate] And [ChosenReturnDate]

Resulting query in design view:

Resulting query SQL:

SELECT RentalProducts.PartID, SUM(RentalProducts.Qty) AS SumOfQty, RentalInventory.AssetQty
FROM (RentalProducts INNER JOIN RentalOrders ON RentalProducts.[OrderID] = RentalOrders.[OrderID]) INNER JOIN RentalInventory ON RentalProducts.PartID = RentalInventory.PartID
WHERE (((RentalOrders.SetupDate)&lt;=[ChosenSetupDate]) AND ((RentalOrders.ReturnDate)&gt;=[ChosenReturnDate]) AND ((RentalProducts.PartID)=[ChosenItemID])) OR (((RentalOrders.SetupDate) BETWEEN [ChosenSetupDate] AND [ChosenReturnDate]) AND ((RentalProducts.PartID)=[ChosenItemID])) OR (((RentalOrders.ReturnDate) BETWEEN [ChosenSetupDate] AND [ChosenReturnDate]) AND ((RentalProducts.PartID)=[ChosenItemID]))
GROUP BY RentalProducts.PartID, RentalInventory.AssetQty;

 

At this point, I can go to Datasheet view for the query and enter 3/1/2020 for the ChosenSetupDate prompt, 3/8/2020 for the ChosenReturnDate prompt, and B30 for the PartID.

This results for me in a display of:

PartID: B30, SumOfQty: 242, AssetQty: 498

At this point I can use math to calculate the needed numbers in any VBA code or calculated field.  I now know that the number of qty available right now for this part for this order is: 498 – 242 = 256.

Submit a Comment