Inventory Balance Query

[madcats]

Simplified, I have an inventory count and a bill of material. I want to know when inventory will be depleted or go negative by the deductions of our bill of material.

I have a BalanceTable that has InvID, Date, ProductionLineStation, and Count.

The Inventory Counts are in the table as positives and the Bill of Material deductions are in as a negative.

I have a report now that shows me the items that will go negative but it is a running balance report that shows all line items in detail and becomes a very long report.

I would like to develop a report showing only the InvIDs that will be going negative and what Date/ProductionLineStation that this will first happen. A single line for each InvID.

I think I need a running total query that I can then select the earliest date that the item turns negative. Any other suggestions or help on making running total query would be appreciated.

 

[sneuberg]

I would like to develop a report showing only the InvIDs that will be going negative and what Date/ProductionLineStation that this will first happen. A single line for each InvID.

Do you mean the Date/ProductionLineStation of the record just before the record where the Field? went negative?

I suggest show us all of the tables and fields involved, some sample data, and what you want from this data.

 

[jdraw]

I agree that seeing your tables and relationships would be useful. I would also suggest a plain English description of the Business so we have something to compare/critique (business facts vs data model).

Inventory systems can have ReOrderPoint which represents the quantity of a Product that when the quantity on hand reaches the ReOrderPoint ( or ReOrderLevel) a transaction is spawned to order the ReOrderQuantity to "refill" the quantity of product on hand.

These values are usually determined by queries, not by having counts within the records.

See Allen Browne AppInventory for more info.

Good luck with your project.

 

[madcats]

I apologize for my vagueness, I will try to explain further.

First, by no means am I an Access expert, I don't do any coding and use the Macro feature to accomplish most tasks. We are in the manufactured home business and our production line moves 3-4 times per day.

I have an Inventory Table that has records with the fields:
"InventoryID", "InventoryDate", "InventoryTime" and "InventoryCount"

I have a Bill of Material Table that has records with fields:
"BOMModelID", "BOMInventoryID", "BOMStation", "BOMCount"
BOMStation being where in the production line the InventoryID is used, and BOMCount being the amount the InventoryID is used in that Station.

I have a Serial Number Table that has "Serial#", "ModelID"

I have a Production Status Table that has records with fields:
"StatusSerial#", "StatusDate","StatusTime", "StatusStation",

From these tables I have created a Transaction Table with fields TranDate and TranTime, TranInvID, and TranCount with positive counts for inventory and negatives counts for BOM usage. This looks like it is working correctly.

On the same idea as a reorder point, I would like to know the date and time, if and when inventory count goes negative. Next step I think and I don't know how to do this is: a running total in a query that is sorted by 2 fields, TranDate then TranTime. If I could get that I think I could query for the first occurrence if any for a negative TranCount.

Again I don't know coding but am more than willing to try if that is what is needed.

 

[sneuberg]

Could you show us some sample data, especially for the Transaction Table and what this running total output would look like for this sample data. Are there multiple entries per TransInvID? Does the output need to be grouped by the TransInvID?

 

[sneuberg]

The attached database has a query with the following expression

Running Total: DSum("[TranCount]","[Transaction Table]","[TranInvID] = " & [TranInvID] & " And [TranDate] + [TranTime] <= #" & [TranDate]+[TranTime] & "#")

This is the typical way DSum is used to create a running total. Note that this expression assumes that TranInvId is a number and that TranDate and TranTime are Date/Time types. Maybe you can use this to get started or at least as vehicle for discussion.

 

[madcats]

I would like a report showing
CBH7389 was negative on 8/8 1
CBH7397 was negative on 8/8 3
CBH7501 was negative on 8/8 1
CBH7503 was negative on 8/8 1
CDH3113 was negative on 8/8 3

This is not a good representation because they all show going negative on 8/8, some would generally go negative on later dates, just don't have that much data in there.

 

[sneuberg]

The attached database has queries and a report that I believe satisfy what you want. I didn't test it a lot figuring it would be easier for you to test with your data. Assuming the types match you should be able to import your Tran Table into this database for testing.

This turned out to be a little tricky and inelegant because of the way time is represented. Had it been represented as a Date/Time time in which time is a fractional part of a day it would have been easy to combine the date and time and have one thing to deal with. As integers this required more effort. In the query that forms a running total (TranQuery) it was handled with a little logical. In the DSum the condition

 "[TranInvID] = '" & [TranInvID] & "' And ( [TranDate] < #" & [TranDate] & "# Or [TranDate] = #" & [TranDate] & "# And [TranTime] <= " & [TranTime] & ")"

basical specifies if the dates are equal then consider the time otherwise just the dates. Other then being lengthy there weren't any problems here.

On the other hand to get the TransInvIDs from this we want to:

1. Get the records with negative TranCount
2. Group them by TranInvID
3. Select the earliest date/time

Select the earliest date/time is a problem as somehow you have to combine the date and time in some manner where you can use the min aggregate. In the qryFirstNeg I came up with the following Rube Goldberg.

1. I convert the date to a double with the CDbl function (It's just the number of days since 12/31/1899)
2. Divide the time by 1000
3. Add the two together

The result gives me something we can use the min function on. To display the date and time I just undo what I did to get this combined number. Note that this will fail if time goes over 1000 but if you think that will happen this number (1000) can be increased.

I really hate this kludge and hope some other forum member can suggest a better way.

Let me know this this passes you tests or not.