remaining qty on recurring shipments

[Dymondjack]

Thanks in advance...

I have a Scheduled shipments report that lists all parts that need to be
shipped, by date. Included in this report is the Qty On Hand, which is
pulled from a parts master table. Many (most) parts, have multiple shipping
instances across the report, and I am trying to come up with an easy way to
calculate the remaining qty after previous shipments would have been made.

For example:
Part Number 55555-55 has three shipments of 25pcs, dated 1/9/09, 2/9/09, and
3/9/09, and currently there are 68pcs in inventory. So, my report shows this:

1/9/09 25pcs 55555-55 68OH
....
....
2/9/09 25pcs 55555-55 68OH
....
....
3/9/09 25pcs 55555-55 68OH

and I would like it to read this:
1/9/09 25pcs 55555-55 68OH
....
....
2/9/09 25pcs 55555-55 43OH
....
....
3/9/09 25pcs 55555-55 18OH

Each recurring part would take into account shipments that are scheduled to
be shipped before that, and adjust the On Hand Qty accordingly.

I have a way to do this, but I don't believe it will be very efficient at
all, and am curious if there might be some built-in functionality I can
utilize to accomplish the task?

My thoughts at this point in time are to use a temporary table when the
report runs and run a check for every record in the Report. If I were to
append each shipment and the qty to a temporary table, I could check each
record in the report, and if it finds like records in the temp table,
subtract the ship qtys from the temp table from the inventory quantity in the
parts master table.

I believe this will work fine (I haven't tried it yet, but don't see any
reason why not), but I do not expect it to be quick by any means.

I don't have a ton of experience performing calculations like this, and am
hoping that access has some function or query that will do this that I have
yet to be introduced to.

Thanks much!
-jack

 

[Steve Sanford]

Hi Jack,

To me, this sounds like a running sum problem (actually a running
subtraction problem).... Here is what I came up with.

I created two test tables:

table name: Inventory
fields: InvID (AN) (PK)
PartNo (text)
Desc (text)
QOH (number - Integer)

table name: Shipping
fields: ShipID (AN) (PK)
InvID_FK (number - long) (FK)
ShipDate (date)
Qnty (number - Integer)


The record in table Inventory:
InvID PartNo Desc QOH
1 55555-55 Part 1 68


Records in table Shipping:
ShipID InvID_FK ShipDate Qnty
1 1 1/9/2009 25
2 1 2/9/2009 25
3 1 3/9/2009 25


---------
I created a stardard module named "MyFunctions" and pasted in the following
function:

Option Compare Database
Option Explicit '<= should *always* have this line

Function fncRunDiff(PartNoID As Long, QOH As Long) As Long
'QOH = quanity on hand

'Variables that retain their values.
Static lngID As Long
Static lngAmt As Long

If lngID <> PartNoID Then
'If the current ID does not match the last ID, then (re)initialize.
lngID = PartNoID
lngAmt = Nz(DLookup("QOH", "inventory", "InvID=" & PartNoID), 0)
Else
'If the current ID matches the last, keep a running diff for the ID.
lngAmt = lngAmt - QOH
End If

'Pass the running sum back to the query.
fncRunDiff = lngAmt
End Function

---------
Now create a query for the report recordsource:

SELECT Shipping.Shipid, Shipping.InvID_FK, Inventory.PartNo,
Shipping.ShipDate, Shipping.Qnty, fncRunDiff([InvID_FK ],[Qnty]) AS RunDiff
FROM Inventory INNER JOIN Shipping ON Inventory.InvId = Shipping.InvID_FK
ORDER BY Shipping.ShipDate;

Save the query with whatever name you want.

---------
Create the report, adding these fields from the query to the detail section:
"ShipDate", "QTY", "RunDiff"

Don't forget to open the Sorting and Grouping dialog box.
The first field should be "InvID_FK", Ascending, Group header = YES. In this
group header, put the field 'PartNo".
The second field should be "ShipDate", Ascending.
Close the dialog box.

---------
If you only have one Part shipping (multiple days) the running Diff column
doesn't reset (to 68 in your example); it keeps subtracting. To fix this,
when the report closes, we force the static variables to zero.

The Report_Close event code looks like:

Private Sub Report_Close()
Dim tmp As Long
'force the Part Number ID and Amount to 0 on form close
'not supposed to have to do this, but ......
tmp = fncRunDiff(0, 0)
End Sub

--------------
The report looks like :

Part: 55555-55

ShipDate QTY RunDiff
1/9/09 25 68
2/9/09 25 43
3/9/09 25 18



HTH

 

[Dymondjack]

Static variables... hadn't thought of that. Unfortunately I have to group
the report by date rather than by part, so it will take a little bit of
modification from your suggestion to get this to work. I think though, that
I should be able to calculate the qty the way you described, but save it as
a query rather than a doing the calculation for the report. Then I should be
able to take the saved query and change the sort order for the actual report.
(something along those lines anyway...)

I think that should work, it should be a lot less code-intensive than my
initial thought. I'll get around to trying it out during the week here.

THanks for the advice. I'll post the finished product when I have it
working correctly in case anyone else is interested.
-jack

Hi Jack,

To me, this sounds like a running sum problem (actually a running
subtraction problem).... Here is what I came up with.

I created two test tables:

table name: Inventory
fields: InvID (AN) (PK)
PartNo (text)
Desc (text)
QOH (number - Integer)

table name: Shipping
fields: ShipID (AN) (PK)
InvID_FK (number - long) (FK)
ShipDate (date)
Qnty (number - Integer)


The record in table Inventory:
InvID PartNo Desc QOH
1 55555-55 Part 1 68


Records in table Shipping:
ShipID InvID_FK ShipDate Qnty
1 1 1/9/2009 25
2 1 2/9/2009 25
3 1 3/9/2009 25


---------
I created a stardard module named "MyFunctions" and pasted in the following
function:

Option Compare Database
Option Explicit '<= should *always* have this line

Function fncRunDiff(PartNoID As Long, QOH As Long) As Long
'QOH = quanity on hand

'Variables that retain their values.
Static lngID As Long
Static lngAmt As Long

If lngID <> PartNoID Then
'If the current ID does not match the last ID, then (re)initialize.
lngID = PartNoID
lngAmt = Nz(DLookup("QOH", "inventory", "InvID=" & PartNoID), 0)
Else
'If the current ID matches the last, keep a running diff for the ID.
lngAmt = lngAmt - QOH
End If

'Pass the running sum back to the query.
fncRunDiff = lngAmt
End Function

---------
Now create a query for the report recordsource:

SELECT Shipping.Shipid, Shipping.InvID_FK, Inventory.PartNo,
Shipping.ShipDate, Shipping.Qnty, fncRunDiff([InvID_FK ],[Qnty]) AS RunDiff
FROM Inventory INNER JOIN Shipping ON Inventory.InvId = Shipping.InvID_FK
ORDER BY Shipping.ShipDate;

Save the query with whatever name you want.

---------
Create the report, adding these fields from the query to the detail section:
"ShipDate", "QTY", "RunDiff"

Don't forget to open the Sorting and Grouping dialog box.
The first field should be "InvID_FK", Ascending, Group header = YES. In this
group header, put the field 'PartNo".
The second field should be "ShipDate", Ascending.
Close the dialog box.

---------
If you only have one Part shipping (multiple days) the running Diff column
doesn't reset (to 68 in your example); it keeps subtracting. To fix this,
when the report closes, we force the static variables to zero.

The Report_Close event code looks like:

Private Sub Report_Close()
Dim tmp As Long
'force the Part Number ID and Amount to 0 on form close
'not supposed to have to do this, but ......
tmp = fncRunDiff(0, 0)
End Sub

--------------
The report looks like :

Part: 55555-55

ShipDate QTY RunDiff
1/9/09 25 68
2/9/09 25 43
3/9/09 25 18



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Thanks in advance...

I have a Scheduled shipments report that lists all parts that need to be
shipped, by date. Included in this report is the Qty On Hand, which is
pulled from a parts master table. Many (most) parts, have multiple shipping
instances across the report, and I am trying to come up with an easy way to
calculate the remaining qty after previous shipments would have been made.

For example:
Part Number 55555-55 has three shipments of 25pcs, dated 1/9/09, 2/9/09, and
3/9/09, and currently there are 68pcs in inventory. So, my report shows this:

1/9/09 25pcs 55555-55 68OH
...
...
2/9/09 25pcs 55555-55 68OH
...
...
3/9/09 25pcs 55555-55 68OH

and I would like it to read this:
1/9/09 25pcs 55555-55 68OH
...
...
2/9/09 25pcs 55555-55 43OH
...
...
3/9/09 25pcs 55555-55 18OH

Each recurring part would take into account shipments that are scheduled to
be shipped before that, and adjust the On Hand Qty accordingly.

I have a way to do this, but I don't believe it will be very efficient at
all, and am curious if there might be some built-in functionality I can
utilize to accomplish the task?

My thoughts at this point in time are to use a temporary table when the
report runs and run a check for every record in the Report. If I were to
append each shipment and the qty to a temporary table, I could check each
record in the report, and if it finds like records in the temp table,
subtract the ship qtys from the temp table from the inventory quantity in the
parts master table.

I believe this will work fine (I haven't tried it yet, but don't see any
reason why not), but I do not expect it to be quick by any means.

I don't have a ton of experience performing calculations like this, and am
hoping that access has some function or query that will do this that I have
yet to be introduced to.

Thanks much!
-jack