If Statement in a query

[CHaythorne]

Hi,

I want my query to find the cumulative forecast up until a best before date.

This is fine if a forecast goes past the best before date as the query picks it out but if the forecast doesn't continue then it won't match them up.

I need the IF statement to say that IF no forecast is present then look at the last cumulative forecast.

How would I go about this?

Many thanks.

Chris

 

[CHaythorne]

Apologies for this not being clearer. I'm only just getting my head around it myself.

I have two tables one with stock, the other that creates a cumulative forecast up until the forecast ends.

This query is combining the two so that I can get stock and take the forecast off to see what stock is 'at risk'.

The issue is that if the best before date of the stock is after the forecast finishes then it doesn't bring back any value.

I need the IIF statement to say if one doen't match the best before then look at the last cumulative forecast and reduce that from the stock figure.

Does that make more sense?

 

[jdraw]

I would suggest that your question is one of jargon-ese. You know what these terms mean in your environment, but the rest of us are guessing and/or deciphering your terminology.

Can you tell us in plain English what you are trying to do?
You have 2 tables --tblStock and tblForecast -- that are somehow related. It appears that some Stock has a best before date.
Is the forecast date a projected sales date?????

This one is really copnfusing

I need the IIF statement to say if one doen't match the best before then look at the last cumulative forecast and reduce that from the stock figure.

 

[CHaythorne]

Apologies again. I think you're right, something that I see everyday isn't that easy to convey to the outside world.

All of our stock has a best before date. What we're trying to see is if there is enough forecast to consum the stock that we have. This will help us establish if there's any 'at risk' stock that we need to sell off. The forecast is a a weekly despatch forecast for our warehouse. The forecast table creates a cumulative forecast from the weekly forecast so it can subtract this from the stock.

For example;

Week Forecast Cumulative

Wk 1 forecast - 50
Wk 2 forecast - 200 250

Any better?

Really appreciate the efforts.

Cheers.