Query for selecting a price change in sales table for the past year

[bootu]

I have a table with product ID, Price, and invoice date. I am trying to write a query that will return one or more lines where it ONLY shows the NEW AND OLD PRICE AND WHEN(invoice date) the price has changed in the past year!



Any help is appreciated.

Thanks

[ranman256]

make a form with text boxes to hold the date range, form: frmRpt
txtStartDate, txtEndDate, txtBoxRng

make a query with product ID, Price, and DateRange, qsThisYearData
DateRange = "Jan2016-Dec2016" to show the year range. (you cant use Invoice date, but it IS in the where clause of the query)

select distinct product ID, Price, forms!frmRpt!txtBoxRng as DateRange where [invoice date] between forms!frmRpt!txtSTartDate and forms!frmRpt!txtEndDate

and another query to show the items a year ago
select distinct product ID, Price, "PrevYr" as DateRange where [invoice date] between dateadd("yyyy",-1,forms!frmRpt!txtSTartDate) and dateadd("yyyy",-1,forms!frmRpt!txtEndDate)

now join the 2 queries and show the 2 different years side by side, filter out the ones that equal.

[bootu]

I appreciate the quick reply. I forgot to mention that users will be required to enter the product id and start and end date! Thanks

[ranman256]

then a combo box for the PROD ID, and add the box to the query.

[bootu]

Thank you! I will give a shot and post my results. I appreciate the prompt response.

[bootu]

I have one last question. Instead of users entering one Product_ID at a time, how do I create a form that users can click on as empty table so users can paste numerous product_id's and still do what I originally want to do. Thanks