A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Calculating an inventory balance at a "point in time"



 
 
Thread Tools Display Modes
  #1  
Old August 19th, 2009, 02:11 PM posted to microsoft.public.access.reports
Joe[_48_]
external usenet poster
 
Posts: 19
Default Calculating an inventory balance at a "point in time"

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
  #2  
Old August 19th, 2009, 04:03 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Calculating an inventory balance at a "point in time"

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


"Joe" wrote:

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

  #3  
Old August 19th, 2009, 04:41 PM posted to microsoft.public.access.reports
Joe[_48_]
external usenet poster
 
Posts: 19
Default Calculating an inventory balance at a "point in time"

On Aug 19, 11:03*am, Duane Hookom
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

--
Duane Hookom
Microsoft Access MVP

"Joe" wrote:
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
  #4  
Old August 19th, 2009, 10:36 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Calculating an inventory balance at a "point in time"

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


"Joe" wrote:

On Aug 19, 11:03 am, Duane Hookom
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

--
Duane Hookom
Microsoft Access MVP

"Joe" wrote:
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

  #5  
Old August 20th, 2009, 03:33 PM posted to microsoft.public.access.reports
Joe[_48_]
external usenet poster
 
Posts: 19
Default Calculating an inventory balance at a "point in time"

On Aug 19, 5:36*pm, Duane Hookom
wrote:
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

"Joe" wrote:
On Aug 19, 11:03 am, Duane Hookom
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


--
Duane Hookom
Microsoft Access MVP


"Joe" wrote:
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

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!
  #6  
Old August 20th, 2009, 06:36 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Calculating an inventory balance at a "point in time"

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

--
Duane Hookom
Microsoft Access MVP


"Joe" wrote:

On Aug 19, 5:36 pm, Duane Hookom
wrote:
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

"Joe" wrote:
On Aug 19, 11:03 am, Duane Hookom
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


--
Duane Hookom
Microsoft Access MVP


"Joe" wrote:
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

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!

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:46 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.