Calculating an inventory balance at a "point in time"

[Joe]

All,

I have an inventory transaction table that houses all additions and
subtractions of a particular part #. The inventory balance of the
part # at any point in time is simply the sum of all the additions and
subtractions.

I need to create a report that can display the inventory transactions
and balances at a specific point in time.

For example, my transaction table has 4 transactions from 7/1 –
8/15.

Transaction 1: on 7/1 added 100 units of part #1
Transaction 2: on 8/1 added 100 units of part #1
Transaction 3: on 8/5 subtracted 15 units of part #1
Transaction 4: on 8/15 subtracted 75 units of part #1

So my inventory balance as of 8/15 is 110 units of part #1.

Now I wish to run a report that uses some date parameters that require
some “point in time” calculations. For example; I would like to run a
report that says for each transaction listed, show me the balance of
my inventory from 8/4 – 8/15. Each time inventory changed calculate
the new amount of inventory and the # of days that the inventory
balance was that amount.


OUTPUT
Starting balance on 8/4 = 200 units of part #1

On 8/5: 15 units of part #1 were subtracted leaving a balance of 185
units for 1 day (from 8/4 - 8/5 there were 100 units of Part #1)

On 8/15: 75 units of part #1 were subtracted leaving a balance of 110
units for 10 days (From 8/5 - 8/15 (10 days) there were 85 units of
Part #1 (100 – 15))

QUESTION
I am having a hard time figuring out how to get my report to display
the starting inventory balance for the start date of the report. This
date needs to be a parameter value as users may wish to choose any
date and I would like to prompt them for the value they wish to use.

Basically the other line item calculations are easy enough to figure
out but I am not sure how to make the first line item on the report be
the starting date of the report (parameter #1) along with the
inventory at that point in time.

Any help would be much appreciated.
Thanks

 

[Duane Hookom]

Assuming a lot of things regarding your table and field names....
Create a form "frmTransdates" with two text boxes "txtStart" and "txtEnd"
for the user to enter the date range. Then create a query
"qselInventoryTransactions" with SQL like:
SELECT tblInventoryTransactions.PartNumber,
IIf([TransactionDate]<[Forms]![frmTransDates]![txtStart],#1/1/1000#,[TransactionDate]) AS TransDate, Sum(tblInventoryTransactions.Units) AS SumOfUnits
FROM tblInventoryTransactions
WHERE
(((tblInventoryTransactions.TransactionDate)<[Forms]![frmTransDates]![txtEnd]))
GROUP BY tblInventoryTransactions.PartNumber,
IIf([TransactionDate]<[Forms]![frmTransDates]![txtStart],#1/1/1000#,[TransactionDate]);

This query should roll up all previous transactions into a single row. Then
create a report based on this query ordered by PartNumber and TransDate. You
can create a group header on PartNumber to display the beginning balance. Add
code to the On Format event of the detail section:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Cancel = Me.TransDate = #1/1/1000#
End Sub

 

[Joe]

Assuming a lot of things regarding your table and field names....
Create a form "frmTransdates" with two text boxes "txtStart" and "txtEnd"
for the user to enter the date range. Then create a query
"qselInventoryTransactions" with SQL like:
SELECT tblInventoryTransactions.PartNumber,
IIf([TransactionDate]<[Forms]![frmTransDates]![txtStart],#1/1/1000#,[TransactionDate]) AS TransDate, Sum(tblInventoryTransactions.Units) AS SumOfUnits
FROM tblInventoryTransactions
WHERE
(((tblInventoryTransactions.TransactionDate)<[Forms]![frmTransDates]![txtEnd]))
GROUP BY tblInventoryTransactions.PartNumber,
IIf([TransactionDate]<[Forms]![frmTransDates]![txtStart],#1/1/1000#,[TransactionDate]);

This query should roll up all previous transactions into a single row. Then
create a report based on this query ordered by PartNumber and TransDate. You
can create a group header on PartNumber to display the beginning balance.Add
code to the On Format event of the detail section:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Cancel = Me.TransDate = #1/1/1000#
End Sub

--
Duane Hookom
Microsoft Access MVP

All,

I have an inventory transaction table that houses all additions and
subtractions of a particular part #.  The inventory balance of the
part # at any point in time is simply the sum of all the additions and
subtractions.

I need to create a report that can display the inventory transactions
and balances at a specific point in time.

For example, my transaction table has 4 transactions from 7/1 –
8/15.

Transaction 1: on 7/1 added 100 units of part #1
Transaction 2: on 8/1 added 100 units of part #1
Transaction 3: on 8/5 subtracted 15 units of part #1
Transaction 4: on 8/15 subtracted 75 units of part #1

So my inventory balance as of 8/15 is 110 units of part #1.

Now I wish to run a report that uses some date parameters that require
some “point in time” calculations.  For example; I would like to run a
report that says for each transaction listed, show me the balance of
my inventory from 8/4 – 8/15.  Each time inventory changed calculate
the new amount of inventory and the # of days that the inventory
balance was that amount.

OUTPUT
Starting balance on 8/4 = 200 units of part #1

On 8/5: 15 units of part #1 were subtracted leaving a balance of 185
units for 1 day (from 8/4 - 8/5 there were 100 units of Part #1)

On 8/15: 75 units of part #1 were subtracted leaving a balance of 110
units for 10 days (From 8/5 - 8/15 (10 days) there were 85 units of
Part #1 (100 – 15))

QUESTION
I am having a hard time figuring out how to get my report to display
the starting inventory balance for the start date of the report.  This
date needs to be a parameter value as users may wish to choose any
date and I would like to prompt them for the value they wish to use.

Basically the other line item calculations are easy enough to figure
out but I am not sure how to make the first line item on the report be
the starting date of the report (parameter #1) along with the
inventory at that point in time.

Any help would be much appreciated.
Thanks

Duane

Thanks, I havent tried it yet but get the gist of what you are
saying. I am a little confused though how to take the query that
rolls up all the transactions into one line item total and use that as
the basis of the report? Perhaps I am confused by the Private Sub
detail_Format procedure, is this supposed to list the transactions on
the opposite side of the dates that are rolled up?

Thanks

 

[Duane Hookom]

The query groups by the date but all transactions prior to a specific date
are summed together to get a beginning balance. My suggestion displays the
beginning balance in a group header. You could display it in a regular detail
section and remove the Cancel code.
--
Duane Hookom
Microsoft Access MVP

Assuming a lot of things regarding your table and field names....
Create a form "frmTransdates" with two text boxes "txtStart" and "txtEnd"
for the user to enter the date range. Then create a query
"qselInventoryTransactions" with SQL like:
SELECT tblInventoryTransactions.PartNumber,
IIf([TransactionDate]<[Forms]![frmTransDates]![txtStart],#1/1/1000#,[TransactionDate]) AS TransDate, Sum(tblInventoryTransactions.Units) AS SumOfUnits
FROM tblInventoryTransactions
WHERE
(((tblInventoryTransactions.TransactionDate)<[Forms]![frmTransDates]![txtEnd]))
GROUP BY tblInventoryTransactions.PartNumber,
IIf([TransactionDate]<[Forms]![frmTransDates]![txtStart],#1/1/1000#,[TransactionDate]);

This query should roll up all previous transactions into a single row. Then
create a report based on this query ordered by PartNumber and TransDate. You
can create a group header on PartNumber to display the beginning balance. Add
code to the On Format event of the detail section:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Cancel = Me.TransDate = #1/1/1000#
End Sub

--
Duane Hookom
Microsoft Access MVP

All,

I have an inventory transaction table that houses all additions and
subtractions of a particular part #. The inventory balance of the
part # at any point in time is simply the sum of all the additions and
subtractions.

I need to create a report that can display the inventory transactions
and balances at a specific point in time.

For example, my transaction table has 4 transactions from 7/1 –
8/15.

Transaction 1: on 7/1 added 100 units of part #1
Transaction 2: on 8/1 added 100 units of part #1
Transaction 3: on 8/5 subtracted 15 units of part #1
Transaction 4: on 8/15 subtracted 75 units of part #1

So my inventory balance as of 8/15 is 110 units of part #1.

Now I wish to run a report that uses some date parameters that require
some “point in time†calculations. For example; I would like to run a
report that says for each transaction listed, show me the balance of
my inventory from 8/4 – 8/15. Each time inventory changed calculate
the new amount of inventory and the # of days that the inventory
balance was that amount.

OUTPUT
Starting balance on 8/4 = 200 units of part #1

On 8/5: 15 units of part #1 were subtracted leaving a balance of 185
units for 1 day (from 8/4 - 8/5 there were 100 units of Part #1)

On 8/15: 75 units of part #1 were subtracted leaving a balance of 110
units for 10 days (From 8/5 - 8/15 (10 days) there were 85 units of
Part #1 (100 – 15))

QUESTION
I am having a hard time figuring out how to get my report to display
the starting inventory balance for the start date of the report. This
date needs to be a parameter value as users may wish to choose any
date and I would like to prompt them for the value they wish to use.

Basically the other line item calculations are easy enough to figure
out but I am not sure how to make the first line item on the report be
the starting date of the report (parameter #1) along with the
inventory at that point in time.

Any help would be much appreciated.
Thanks

Duane

Thanks, I havent tried it yet but get the gist of what you are
saying. I am a little confused though how to take the query that
rolls up all the transactions into one line item total and use that as
the basis of the report? Perhaps I am confused by the Private Sub
detail_Format procedure, is this supposed to list the transactions on
the opposite side of the dates that are rolled up?

Thanks

 

[Joe]

The query groups by the date but all transactions prior to a specific date
are summed together to get a beginning balance. My suggestion displays the
beginning balance in a group header. You could display it in a regular detail
section and remove the Cancel code.
--
Duane Hookom
Microsoft Access MVP

Assuming a lot of things regarding your table and field names....
Create a form "frmTransdates" with two text boxes "txtStart" and "txtEnd"
for the user to enter the date range. Then create a query
"qselInventoryTransactions" with SQL like:
SELECT tblInventoryTransactions.PartNumber,
IIf([TransactionDate]<[Forms]![frmTransDates]![txtStart],#1/1/1000#,[TransactionDate]) AS TransDate, Sum(tblInventoryTransactions.Units) AS SumOfUnits
FROM tblInventoryTransactions
WHERE
(((tblInventoryTransactions.TransactionDate)<[Forms]![frmTransDates]![txtEnd]))
GROUP BY tblInventoryTransactions.PartNumber,
IIf([TransactionDate]<[Forms]![frmTransDates]![txtStart],#1/1/1000#,[TransactionDate]);
This query should roll up all previous transactions into a single row.. Then
create a report based on this query ordered by PartNumber and TransDate. You
can create a group header on PartNumber to display the beginning balance. Add
code to the On Format event of the detail section:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Cancel = Me.TransDate = #1/1/1000#
End Sub
--
Duane Hookom
Microsoft Access MVP
:
All,
I have an inventory transaction table that houses all additions and
subtractions of a particular part #.  The inventory balance of the
part # at any point in time is simply the sum of all the additions and
subtractions.
I need to create a report that can display the inventory transactions
and balances at a specific point in time.
For example, my transaction table has 4 transactions from 7/1 –
8/15.
Transaction 1: on 7/1 added 100 units of part #1
Transaction 2: on 8/1 added 100 units of part #1
Transaction 3: on 8/5 subtracted 15 units of part #1
Transaction 4: on 8/15 subtracted 75 units of part #1
So my inventory balance as of 8/15 is 110 units of part #1.
Now I wish to run a report that uses some date parameters that require
some “point in time” calculations.  For example; I would liketo run a
report that says for each transaction listed, show me the balance of
my inventory from 8/4 – 8/15.  Each time inventory changed calculate
the new amount of inventory and the # of days that the inventory
balance was that amount.
OUTPUT
Starting balance on 8/4 = 200 units of part #1
On 8/5: 15 units of part #1 were subtracted leaving a balance of 185
units for 1 day (from 8/4 - 8/5 there were 100 units of Part #1)
On 8/15: 75 units of part #1 were subtracted leaving a balance of 110
units for 10 days (From 8/5 - 8/15 (10 days) there were 85 units of
Part #1 (100 – 15))
QUESTION
I am having a hard time figuring out how to get my report to display
the starting inventory balance for the start date of the report.  This
date needs to be a parameter value as users may wish to choose any
date and I would like to prompt them for the value they wish to use..
Basically the other line item calculations are easy enough to figure
out but I am not sure how to make the first line item on the reportbe
the starting date of the report (parameter #1) along with the
inventory at that point in time.
Any help would be much appreciated.
Thanks

Duane

Thanks, I havent tried it yet but get the gist of what you are
saying.  I am a little confused though how to take the query that
rolls up all the transactions into one line item total and use that as
the basis of the report?  Perhaps I am confused by the Private Sub
detail_Format procedure, is this supposed to list the transactions on
the opposite side of the dates that are rolled up?

Thanks

Duane

Thanks, that worked like a charm.

Now I have one last critical step to the success of this project. I
need to find a way to calculate the difference of days and quantity
from one line of output on the report to the previous line. For
example, line 2 needs to contain a calculation that subtracts the
output of line #2 quantity from line #1 quantity and then place that
calculation on line #2. Etc. for each row that follows with Line #1
containing no calculated values or a value of zero.

I have a link to a markup of the report that demonstrates what the
output would look like; http://www.businesstechnologies1.com/report1.

Is this possible? If not is there a creative way around getting the
requested information?

Thanks again!

 

[Duane Hookom]

I would perform this calculation in the report's record source query with a
couple subqueries.

--
Duane Hookom
Microsoft Access MVP

The query groups by the date but all transactions prior to a specific date
are summed together to get a beginning balance. My suggestion displays the
beginning balance in a group header. You could display it in a regular detail
section and remove the Cancel code.
--
Duane Hookom
Microsoft Access MVP

On Aug 19, 11:03 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
Assuming a lot of things regarding your table and field names....
Create a form "frmTransdates" with two text boxes "txtStart" and "txtEnd"
for the user to enter the date range. Then create a query
"qselInventoryTransactions" with SQL like:
SELECT tblInventoryTransactions.PartNumber,
IIf([TransactionDate]<[Forms]![frmTransDates]![txtStart],#1/1/1000#,[TransactionDate]) AS TransDate, Sum(tblInventoryTransactions.Units) AS SumOfUnits
FROM tblInventoryTransactions
WHERE
(((tblInventoryTransactions.TransactionDate)<[Forms]![frmTransDates]![txtEnd]))
GROUP BY tblInventoryTransactions.PartNumber,
IIf([TransactionDate]<[Forms]![frmTransDates]![txtStart],#1/1/1000#,[TransactionDate]);

This query should roll up all previous transactions into a single row.. Then
create a report based on this query ordered by PartNumber and TransDate. You
can create a group header on PartNumber to display the beginning balance. Add
code to the On Format event of the detail section:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Cancel = Me.TransDate = #1/1/1000#
End Sub

:
All,

I have an inventory transaction table that houses all additions and
subtractions of a particular part #. The inventory balance of the
part # at any point in time is simply the sum of all the additions and
subtractions.

I need to create a report that can display the inventory transactions
and balances at a specific point in time.

For example, my transaction table has 4 transactions from 7/1 –
8/15.

Transaction 1: on 7/1 added 100 units of part #1
Transaction 2: on 8/1 added 100 units of part #1
Transaction 3: on 8/5 subtracted 15 units of part #1
Transaction 4: on 8/15 subtracted 75 units of part #1

So my inventory balance as of 8/15 is 110 units of part #1.

Now I wish to run a report that uses some date parameters that require
some “point in time†calculations. For example; I would like to run a
report that says for each transaction listed, show me the balance of
my inventory from 8/4 – 8/15. Each time inventory changed calculate
the new amount of inventory and the # of days that the inventory
balance was that amount.

OUTPUT
Starting balance on 8/4 = 200 units of part #1

On 8/5: 15 units of part #1 were subtracted leaving a balance of 185
units for 1 day (from 8/4 - 8/5 there were 100 units of Part #1)

On 8/15: 75 units of part #1 were subtracted leaving a balance of 110
units for 10 days (From 8/5 - 8/15 (10 days) there were 85 units of
Part #1 (100 – 15))

QUESTION
I am having a hard time figuring out how to get my report to display
the starting inventory balance for the start date of the report. This
date needs to be a parameter value as users may wish to choose any
date and I would like to prompt them for the value they wish to use..

Basically the other line item calculations are easy enough to figure
out but I am not sure how to make the first line item on the report be
the starting date of the report (parameter #1) along with the
inventory at that point in time.

Any help would be much appreciated.
Thanks

Thanks, I havent tried it yet but get the gist of what you are
saying. I am a little confused though how to take the query that
rolls up all the transactions into one line item total and use that as
the basis of the report? Perhaps I am confused by the Private Sub
detail_Format procedure, is this supposed to list the transactions on
the opposite side of the dates that are rolled up?

Thanks

Duane

Thanks, that worked like a charm.

Now I have one last critical step to the success of this project. I
need to find a way to calculate the difference of days and quantity
from one line of output on the report to the previous line. For
example, line 2 needs to contain a calculation that subtracts the
output of line #2 quantity from line #1 quantity and then place that
calculation on line #2. Etc. for each row that follows with Line #1
containing no calculated values or a value of zero.

I have a link to a markup of the report that demonstrates what the
output would look like; http://www.businesstechnologies1.com/report1.

Is this possible? If not is there a creative way around getting the
requested information?

Thanks again!