Excel Offset + Match in Access? (1 Viewer)

[illusionek]

Hello!!!

I got a fairly complicated Stock Cover calculation in Excel that I am trying to replicate in Access.

Unfortunately this calculation in regular Excel uses a combination of Offset and Match to get right values from matrix tables. I was doing some reading online but so far I was unable to replicate my specific scenario. I came across an issue with running totals as my demand values repeat. Not to mention I am not able to replicate Offset + Match scenario :banghead:

I was wondering if anyone could help me out.

I attached a sample Excel file with offset + match functions to show how it is currently done. I have also attached an example of Access database with the same data in normalized tables where I am trying to reflect this calculation.

Please help

http://www.filedropper.com/excel-samplefile

http://www.filedropper.com/newmicrosoftaccessdatabase

 

[CJ_London]

cannot access your filedropper link - suggest zip and upload the files to this thread (use the advanced editor)

you would use a query - offset + match is just a more efficient and flexible Dlookup - and in a database the equivalent of dlookup is a query.

 

[illusionek]

Hi

I attached the database and Excel file. Sadly I cannot use DLookup, I tried and failed :banghead:

Below is Excel formula, which can be found in attached Excel file. As you can see it is not a simple one.

I am trying to replicated this in Access.

=(A4-(OFFSET(R4,,(MATCH(A4+Q4,R4:X4,1)-1),,)-Q4))/OFFSET(G4,,MATCH(A4+Q4,R4:X4,1))+MATCH(A4+Q4,R4:X4,1)

 

[CJ_London]

please zip and upload - my browser won't open files directly without a lot of kerfuffle

 

[CJ_London]

Also, please supply an English translation of what the offset/match is supposed to be doing, regret I don't have time to figure it out

 

[illusionek]

Both files in a zip file now

Cell A4 provides opening stock for current w/c (23/05/2016) = 15823

Columns C to M provide Forecast for each week

Columns N to X provide Cumulative Forecast

The Stock Cover calculation is fairly complicated, so I hope my below explanation makes sense, if not please let me know.

I appreciate all the help I can get Of course I would be happy to change the way Stock Cover is calculated providing new solution gives the same figure.

Stock Cover is in B4 and I am trying to replicate this formula in Access:

=(A4-(OFFSET(R4,,(MATCH(A4+Q4,R4:X4,1)-1),,)-Q4))/OFFSET(G4,,MATCH(A4+Q4,R4:X4,1))+MATCH(A4+Q4,R4:X4,1)

Below function looks at cumulative forecast starting from 30/05/2016 (next week after the current week; 23/05/2016) and checks how many weeks can be fully fulfilled based on Opening Stock after fulfilling current week forecast (23/05/2016). Below function returns value 2.

=(MATCH(A4+Q4,R4:X4,1)-1)

Using the above function below returns value 35560, which is in T4. So this is two columns to the right from 30/05/2016.

=OFFSET(R4,,(MATCH(A4+Q4,R4:X4,1)-1),,)

Below returns forecast for the week that cannot be fully supplied from the Opening Stock

OFFSET(G4,,MATCH(A4+Q4,R4:X4,1))

Below returns 3, so this is current week + 2 weeks that can be fully supplied from the Opening Stock

=MATCH(A4+Q4,R4:X4,1)

So the total calculation is:

((15823-15240)/5080)+3

 

[CJ_London]

OK, you can just copy and paste this code into a new query sql window to run.

 SELECT TOP 1 OS2.[Item Name], OS2.[W/C], FS.C.[W/C], FS.CumForecast, DateDiff("w",[os2].[w/c],[F2].[W/c])+([Opening Stock]-[CumForecast])/[Demand] AS StockCover, F2.[W/C]
FROM Forecast AS F2, [Current Week Opening Stock] AS OS2 INNER JOIN ([COLOR=red]SELECT OS.[Item Name], OS.[W/C], C.[W/C], Sum(F.Demand) AS CumForecast
FROM Forecast AS F, Calendar AS C, [Current Week Opening Stock] AS OS
WHERE (((F.[W/C])<=[c].[w/c] And (F.[W/C])>=[os].[w/c]) AND ((C.[W/C])>=[os].[w/c]))
GROUP BY OS.[Item Name], OS.[W/C], C.[W/C][/COLOR])  AS FS ON OS2.[Item Name] = FS.[Item Name]
WHERE (((FS.CumForecast)<=[os2].[opening stock]) AND ((F2.[W/C])=[c.w/c]+7))
ORDER BY FS.CumForecast DESC;

I've used aliases for table names to keep the amount of typing down and to differentiate when a table is used more than once. I also recommend you lose the spaces in your table and field names and most certainly the / in W/C - you should only use alphanumeric characters. Google 'Access reserved words and characters' to find out more.

The code in red is what is called a subquery, copy and paste just the bit in red into a new query so you can see more clearly in the query grid what it looks like - if you want to, save the query with a suitable name, then replace the bit in red above (plus the two brackets) with the name of the query.

This works with the data provided but I don't have any context in which you are using this (your excel has hardcoded cumulative figures and the current week value in B1 is not referenced, so you have hard coding in your calculation as well) and how your tables will be populated so may need incorporating into another query. It could be adapted for example to show a moving stock cover figure if you included stock received as well.

Note that it will report the wrong figure if your demand figures are not dated far enough ahead.

In your openstock table, change the week to 20/6 and your stock cover reduces to 2.51 which I believe to be correct.

 

[illusionek]

You are a magician! Your query works like a charm

Thank you also for your comments, I am aware of issues with hard coding and need to address them as well. Funny enough the end goal is to make your query to become a moving stock cover I just need to figure out some other bits and pieces.


Thanks a lot for your help!

 

[CJ_London]

if you are going to include stock in as well as stock out, better to store in one transaction table - something like

tblStockMovements
MovementPK - autonumber
ItemFK - long - link to item table for name/specs etc
MovementDate - Date
MovementType - Text or number to indicate stock In/Stock Out/Stock Adjustment/Stock Take
Qty - Long - enforce positive for stock in, negative for stock out, adjustment can be either

It will make subsequent querying much easier. You can use the Stocktake figure as the equivalent of your current week opening stock - i.e. it 'cuts off' any pre dated movements