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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|