Date Criteria

[CHaythorne]

Hello All,

I've got a query that is supposed to bring 2 tables together.

I have the same date field in both tables that is linked together and then brings back a figure that relates to the date.

I need a formula that says that if there is no date that matches then look to the most recent date for the figure.

So far I have the following;

IIF ([Cumulative Sales Forecast Total]=Null, <[W/E Despatch Date])

I'm guessing this is totally wrong as it doesn't do anything like what I require.

Also, I'm guessing I put this formula in the criteria field below the "W/E Despatch Date"?

Any help would be appreciated.

Cheers.

Chris

 

[billmeye]

If you are using the date fields to link the two tables make the table that always has the date the From side table and left join to the table that doesn't always have a date. Then, you will need to something like you were beginning to do in the column for returning the date you will use for your formula,

FormulaDate:IIf(isnull([tbl2].[fldDate]),DMax("[fldDate]","tbl2","[fldDate]<#" & [tbl1].[fldDate] & "#"),[tbl2].[fldDate])

 

[CHaythorne]

Thank you for that.

There's always a date present they just sometimes don't match up as the forecast may finish before the best before date of the product.

In this situation I wan't it to look for the last forecast that was present and take that away.

Apologies if that isn't very clear.

I can't get the formula to work. What are the hashtags supposed to represent?

Thanks again.

 

[billmeye]

You can't link two tables together if both tables do not contain exactly the same data in the field you are linking them on. Are you saying that the Date in table 1 will not match the date in table 2 but somehow you want to return a previous record in table 2 when that happens?

 

[pr2-eugin]

CHaythorne, billmeye is trying to help.. and you should give more information as he has requested.. Throw in a few examples, it always helps us see what you mean..

 

[CHaythorne]

Hi,

Apologies for the delay.

Yes Billmeye, that's correct.

Basically every item has a forecast and every item has a best before date.

I have one table with the products and best before dates in and I have another table that creates a forecast by week with a column that creates a cumulative forecast, in essence it adds each weeks forecast so that should theoretically be how much stock is consumed.

Sometimes the forecast will finish before the best before date. In this situation I want it then to look at the last forecast present and bring back the last cumulative figure.

I will then minus this figure from the stock to find out how much excess stock we have that we need to sell.

Is this any help? Feel free to ask more . . .

Many thanks for you help so far.

Chris

 

[billmeye]

If you are trying to return a row from table 2 where the Date is either equal to the Date in table1 or the next nearest Date prior to the Date in table1 then I would not include table2 as a table in the query, just use an equation in the column to find the latest Date from table2. The only other concern I have is in table2 you only use a Date once, there can not be multiple occurrences of that Date? Dates really are not the best way to tie information from two separate tables, that is what an autonumber primary key and foreign key are intended for. If you do have a field you are using to identify records than that would need to be included in the criteria for the DMax().

LatestDateTable2: DMax("[fldDate]","table2","[fldDate]<=#" & [table1].[fldDate] & "#")

 

[CHaythorne]

You've made me think about this a little bit deeper now and you're right the dates do occur more than once as it's by sales channel (e.g commercial, internet, retail etc.) but there's a column at the end of each row that's adding all of these together so no matter which occurence of the date it looks at it will always have a, the cumulative forecast and b, the forecast by channel. Does this leave any potential risks?

Really appreciate your help, shame I can't buy you a beer or two!

 

[billmeye]

What information is unique to table1 that is also stored in table2? If you don't have that you will not likely get good consistent results.

 

[CHaythorne]

I've tried using the following and it has doesn't return what's required.

Q_Residual_ShelfLife_Positive: DMax("[W/E]","T_ODP_CumulativeForecast","[W/E]<=#" & [Q_Residual_ShelfLife_Positive].[W/E Despatch Date] & "#")

It seems to return a random date.

With regards to a unique entry, I don't think anything is to be honest. This is a database I inherited in my role that the previous encumbant didn't finish. While I've managed to sort a lot of it out some of it still alludes me.

 

[billmeye]

Would you be able to attach the DB?

 

[CHaythorne]

It's 175mb so I doubt it or did you mean a screenshot?

 

[billmeye]

If you could pare it down to just the table (remove most records) query form etc. you are working on I could better see how to accomplish your goal.

 

[CHaythorne]

Okay no problem, I've copied it to my laptop to take home now and I'll strip it down in a few hours and send it on. Email or is there a way to post it on here?

Many thanks.

 

[billmeye]

You can attach it here, look for the paper click in the formatting section above where you type your message and you'll need to zip it up.

Make sure your clicking Post Reply, not the Quick Reply.