Hello,
I'm trying to figure out how to run a shortage report for my inventory database. I have tbl_inventory with Total Quantity and Quantity Allocated. The Quantity Allocated is determined by the orders I have in my system. I have tbl_Orders which has the general info for the order, and tbl_Order_Parts which lists all the parts needed for that order. In my application - Quantity Allocated is almost always going to be greater than Total Quantity. I would like to be able to run a report that goes down my list of Orders and tells me which ones I will not be able to fill with current Total Quantity.

I am not sure how to get my report to tell me which orders I cannot fill. If anyone could point me in the right direction for this, I would be most appreciative.

Thanks!

This might be complicated, but it may also be very simple. I don't think people will know really, until you can provide some more specific information about this statement...The Quantity Allocated is determined by the orders I have in my system.Do you have a calculated field in a table? You mentioned that the allocation is a field in a table. Give all the specifics you can about the "Allocated" field (its source, process used to populate it, etc...)

Thanks for the reply. Allocated is in my inventory table as a simple number field, it is not calculated. Each time a new Order is entered, I have a Query that runs called qry_update_allocated_inv which adds the Order quantity to the current Allocated. Hope that explains it!

Yes, that's good enough.

But the fact still remains...I would like to be able to run a report that goes down my list of Orders and tells me which ones I will not be able to fill with current Total QuantityHow do you want to go about deciding which orders you cannot fill? Is it first come, first serve basis? What is the basis for the "on-hand" parts allocation? By date? Still, questions need to be answered before you can query the info.

It will be based on a first come, first serve basis. The data will be sorted by order number, which is a simple AutoNumber. So the lowest order number would get filled first.

ever used visual basic?

Heh, Yes, but its been a while. And I haven't used it with Access.
Are you thinking of creating a loop? That was my inital thought, just not sure how to do it.

Well, then try running this...Function LizLeeTest()

Dim x as Long
Dim str As String
Dim fld As Field
Dim db As Database
Dim tbldef As TableDef
Dim qrydef as QueryDef
Dim rsorders As Recordset
Dim rsinventory As Recordset

Set db = CurrentDb()
Set tbldef = db.tabledefs("orders")
Set fld = tbldef.CreateField("AllocationBalance", dblong)

tbldef.Fields.Append fld

Set rsorders = db.OpenRecordset("orders")
set rsinventory = db.openrecordset("inventory")

rsorders.movefirst
rsinventory.movefirst

with rsorders

do until rsinventory.absolutepostion + 1 = rsinventory.recordcount

x = rsinventory!TotalQuantity

do until .eof

if !partname = rsinventory!partname then
.edit
!AllocationBalance = x - !orderquantity
.update
x = !AllocationBalance
end if

.movenext

Loop
.movefirst
rsinventory.movenext

Loop

End With

set rsorders = nothing
set rsinventory = nothing

set qrydef = db.createquerydef("OrdersThatHaveShortages", _
"SELECT DISTINCT orders.orderID, any other fields FROM orders " & _
"WHERE orders.AllocationBalance < 0"

DoCmd.openquery("OrdersThatHaveShortages")

End FunctionOnly problem with this, is that it assumes that the order quantities for all the parts on an order are sitting in the orders table. From reading your posts, I don't think that is the case. It will obviously have to be modified if the part quantity information (per order) is sitting in the "order parts" table instead. But regardless, this should give you a starting point!

Thanks for the help! I will give this a try and see how it goes.