Sage 100

Question for a sql or excel guru. Table 1 is similar to Sage's Inventory Transaction table. Has Date, item, qty & cost. THere are days with more than one transaction, days with none. Table 2 is simply every date in 2017 & 18. Assume we have another table or sheet that has the opening balance as of 1/1/2017. Want to run a report or query or excel sheet that shows the inventory balance, , for every day starting at 1/1/2017 through today, as of each day, with a running total of the balance on hand per day. IF a day has no transactions, just pull the balance forward, if it has say 10 transactions, sum them up and show one row. I know the output could be large given there will be 365 rows per item, but a client needs this for an inventory analysis project. When I try and use excel to relate the date table to the inventory table it says it can't because there are multiple transactions on the same date in the inventory table which means the key isn't unique. It tells me to reverse the relationship and I do. It then works. However, I get no records for the days that have no transactions...

Not a guru in either one, but barring any guru stepping forward, I would summarize the transactions with a pivot table. On the date side, do a vlookup pulling from the pivot so that you have all dates required. Might have to create a unique index by combining the item number and date. Again, probably @DanBurleson might have a slick way in Access or @ClarkWalliser might have some power BI insights or @JohnnyPabian or @SteveIwanowski know a trick or two in SQL

I know you can do it with the Power Pivot data model function in Excel 2013 or later. Maybe this link will help. If you need more help. You can email me at clarkw@dsdinc.com https://www.dummies.com/software/microsoft-office/excel/hide-or-show-pivot-table-items-without-data-on-your-reports/

Thinking out loud, you'll probably want to create a Date Dimension table of all days (https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/) and then perform a CROSS JOIN and running total CTE/windowing/partition by function. Check out the attached ABX sample and maybe these links to get some ideas: https://www.sqlshack.com/running-running-totals-sql-server/, https://stackoverflow.com/questions/19075098/how-to-fill-missing-dates-by-groups-in-a-table-in-sql

This can be done using Crystal Reports and a sub report. It might take a few years to finish. I tested using ABC. First, in the main report summarize the ItemCodes in IM_ItemTransactionHistory then use a sub report at the Group level with the date table, IM_ItemTransactionHistory and ItemCode balance forward table. Hopefully, this is Premium as you may need two left outer joins. There might be a work around for SOTAMAS90. :-)

@DanBurleson - I was thinking you would whip up a solution in Access.....

@JeffSchwenk Who uses Access anymore? ;-) When I said ""it might take a few years to finish"", I meant running it against a live company with substantial data. @MarkChinsky The attached report used a UDT for a single month's worth of dates and ABC data. For the beginning balance I used a random field in CI_Item. Easily adapted to your environment.

I'd do this with a SQL view... date, item, beginning balance, YTD Qty, YTD $... using sub-query sum formulas for the YTD numbers. All you need is a table with all the dates, outer join to CI_Item, as a starting point.