Access Report that calculates rolling weeks on hand? We have Excel formula that does this.

[mlance]

=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(B1:Z1,,,,COLUMN(B1:Z1)-COLUMN(B1)+1))<=A2))



Where forecast is in row 1, from B to Z representing the weekly buckets
Inventory/Projected Inventory is in row 2, goes from A to Y

So the formula uses the projected inventory in each bucket, and projects out how many buckets in the future that particular inventory will last. And it is rolling so we have additional rows that are for planned production that increases the projected inventory so we can see the impact on weeks on hand.

So we have this working well in Excel, but I am wondering if this functionality is available in Access? We have reports we use that basically show the same information but we have never been able to calculate the weeks on hand in a rolling fashion such as we have in Excel.

Any help is appreciated.

[rpeare]

First and foremost don't try to think of transferring functions from excel to access. The two function in extremely different ways. When you're setting up a database it is NOT just one great big spreadsheet type layout. It is a series of interrelated tables.

For instance

tblCustomer
CustomerID  CustomerName ---> other customer related fields
1           Customer A
2           Customer B

tblProducts
ProductID  ProductName ---->  other product related fields
1          Product X
2          Product Y
3          Product Z

tblInvoices
InvoiceID  InvoiceNumber  CustomerID  InvoiceDate  ---> other invoice related fields.
1          III-001        1           1/1/2014
2          III-002        1           1/7/2014

tblInvoiceDetail
DetailID  InvoiceID  ProductID  Quantity ---> other invoice product related fields
1         1          1          5
2         1          2          10
3         2          3           6
4         2          2          15

This would be a basic structure for usage and receipts of products. With the proper structure what you are asking can be done (a rolling time period for a forecast) but it will be made much, much harder if you do not normalize your data and have a good structure before you even start.

[kagoodwin13]

I've worked a lot with supply chain/manufacturing Access databases.

I've got two questions:
1. What are you trying to do that Excel can't or won't do?
2. Do you have other existing Access databases that you are trying to connect inventory data with?

In my experience, Excel was the superior tool to do rolling inventory forecasts. Not only was it easier to set up the formulas, but it was easier make ad-hoc reports and on-the-fly changes. Also, most management-types have at least a basic understanding of Excel, but nearly zero understanding of Access.

Would you mind to share a version of your Excel file? I would recommend removing the proprietary/identifying data, but make sure that the data structure and formulas are the same. Also please share a sketch or example of the report you would like to generate.

Thanks!

[rpeare]

I would argue Access is the better tool. There are many things I can do with Access I can't do with Excel, and I haven't encountered a single thing I can do with Excel I can't do with Access.

[kagoodwin13]

It depends on the complexity of what you're doing. For smaller information storage, Excel is quick and easy. I used to do "rolling inventory" charts in Excel all the time, but usually it was for 1-10 critical parts. Because my need was small, I didn't need a database.

If I wanted to keep a tab of all parts, then develop reports for parts that are over or under thresholds (over max, under min, running out before next planned shipment, etc.) then I would definitely develop a database. That is a lot of information to compile and monitor in Excel, especially if you are managing hundreds or thousands of parts.

That's why I was asking OP what their need was - no reason to reinvent the wheel if we don't need to. I was hoping they would respond by now... hope they didn't give up!