find price for given month

[Rainbowhawk]

I have a table of upcoming price increases by item / valid from date

Item Price ValidFrom
1234 10 01/01/2014
1234 20 01/06/2014
3456 15 01/01/2014
3456 25 01/06/2014
3456 35 01/01/2015


I also have a table of upcoming sales


Item Month Qty Price
1234 Feb 14 20 ????
1234 July 14 30 ????
3456 Sept 14 25 ????


How do I calculate the correct price in a query


All help appricated


Tim

 

[jdraw]

I would like to see the data model (tables and relationships) for your database to help put this request into context.

I'm not sure where the upcoming Sales fits.

With Products,Orders and OrderDetails applications I find it better to record the CurrentPrice of a Product in the Product Table, then for each Order/OrderDetails keep the AgreedToSellingPrice and the QuantityOfProduct with the OrderDetail. This lets you change a ProductPrice without affecting historic values. The AgreedToPrice is the actual selling price of that product to that customer on that day/transaction. The AgreedToPrice can accommodate Negotiations, Clearance, Sales, Loyalty Programs etc.

See this thread for additional info.

I hope it's helpful to you.

 

[Rainbowhawk]

The output of this is forecasted revenue, the selling price varies sale to sale and is not finalised until after the event, we are forecasting based on pending orders, these are then set against a price which is an average of previous sales, however we also have planned increases coming up, and need to increase this figure to ancipate this.

Will see if I can create a shell holding some examples

 

[jdraw]

Tim,

I am not an Excel person, but it seems you are describing more of a "what-if scenario" than database. It may be something better suited to spreadsheet than database. Perhaps someone(s) proficient in Excel could comment.

 

[Rainbowhawk]

It would work well in excel, however the bigger picture is requiring a full access solution,

Think the solution involves finding when next price starts and using that a between query, however not sure how to obtain this, as this may or may not exist when query is run

 

[jdraw]

Can you give us a complete scenario of the between query you're seeing?
This is general syntax for using Between in a query:

Select * from your table
Where myDate
Between StartDate and EndDate

and in Access Between INCLUDES the end points

 

[Rainbowhawk]

The current query no looks like

[SIZE=3][FONT=Calibri]SELECT Forecast.Article, Forecast_Detail_Working.FYear, Forecast_Detail_Working.FPeriod, Forecast_Detail_Working.Qty, First(DateLookup.CalDate) AS FirstOfCalDate, ASPProjected.NewASP AS Price, ASPProjected.FromDate[/FONT][/SIZE]

[SIZE=3][FONT=Calibri]FROM ((Forecast INNER JOIN Forecast_Detail_Working ON Forecast.F_Id = Forecast_Detail_Working.F_Id) INNER JOIN ASPProjected ON Forecast_Detail_Working.Article = ASPProjected.Article) INNER JOIN DateLookup ON (Forecast_Detail_Working.FPeriod = DateLookup.Period) AND (Forecast_Detail_Working.FYear = DateLookup.FinancialYear)[/FONT][/SIZE]

[SIZE=3][FONT=Calibri]GROUP BY Forecast.Article, Forecast_Detail_Working.FYear, Forecast_Detail_Working.FPeriod, Forecast_Detail_Working.Qty, ASPProjected.NewASP, ASPProjected.FromDate[/FONT][/SIZE]

[SIZE=3][FONT=Calibri]HAVING (((First(DateLookup.CalDate)) Between [fromdate] And #1/1/2050#));[/FONT][/SIZE]

The date

#1/1/2050#

needs to be the day before the [fromdate] of the next date range for that article

 

[jdraw]

What exactly is the question in context? Does the query not work? We don't know your environment as well as you, so any info you can add would be helpful to readers.

Could you explain the Tables involved? You mentioned Forecasted Revenue, PendingSales, Average of PreviousSales, Selling Prices and Upcoming Increases.

Can you tell readers in plain English WHAT your SQL is doing/suppose to do?

Where does Fromdate come from?
Have you considered using MIN() instead of First()?

I have reformatted the SQL for readability

SELECT 
Forecast.Article
, Forecast_Detail_Working.FYear
, Forecast_Detail_Working.FPeriod
, Forecast_Detail_Working.Qty
, First(DateLookup.CalDate) AS FirstOfCalDate
, ASPProjected.NewASP AS Price
, ASPProjected.FromDate

FROM
((Forecast INNER JOIN Forecast_Detail_Working ON Forecast.F_Id = Forecast_Detail_Working.F_Id) 
INNER JOIN ASPProjected ON Forecast_Detail_Working.Article = ASPProjected.Article) 
INNER JOIN DateLookup ON (Forecast_Detail_Working.FPeriod = DateLookup.Period) AND 
(Forecast_Detail_Working.FYear = DateLookup.FinancialYear)

GROUP BY 
Forecast.Article
, Forecast_Detail_Working.FYear
, Forecast_Detail_Working.FPeriod
, Forecast_Detail_Working.Qty
, ASPProjected.NewASP
, ASPProjected.FromDate

HAVING (((First(DateLookup.CalDate)) Between [fromdate] And #1/1/2050#));

What does the Query NOT do that you were expecting?
Do you have some sample tables and data for readers to understand the issue?