URGENT: Can anyone help to fix Access SQL - Kudos if you have great skills

[buntybunty]

tblProducts Table
tProductID Text - PK


tProductName Text


tblProductsPrice Table
tProductID Text - FK (VAlues will be like OJFDKERWER)
iProductDate DateTime
lProductNPrice Number Double


I am storing the values of the tables as below -


tblProducts
OJFDKERWER, AcerLaptop
WPTRWJWERK, HP Laptop


tblProductsPrice
OJFDKERWER, 01-Sep-15, 500
OJFDKERWER, 02-Sep-15, 500
OJFDKERWER, 03-Sep-15, 500
OJFDKERWER, 04-Sep-15, 525
OJFDKERWER, 05-Sep-15, 495


Suppose today is 05-Sep then the query should list the price change from previous day (only if lower than before)


Please suggest me the SQL query which will run in access 2013 (not in SQL Server)


Many Thanks

[ranman256]

make query Q1 that pulls the current prices,
make Q2 that pulls the NEW prices and join to Q1 on [tProductID]
Q2 will be an update query, where the criteria will be: (if Q2<Q1) update price

field : Q2.[lProductNPrice]
citeria: <Q1.[lProductNPrice]

set: Q1.[lProductNPrice] = Q2.[lProductNPrice]

[buntybunty]

Thanks, can you pls help me with the access query - many thanks struggling urgent

make query Q1 that pulls the current prices,
make Q2 that pulls the NEW prices and join to Q1 on [tProductID]
Q2 will be an update query, where the criteria will be: (if Q2<Q1) update price

field : Q2.[lProductNPrice]
citeria: <Q1.[lProductNPrice]

set: Q1.[lProductNPrice] = Q2.[lProductNPrice]

[June7]

To pull data from another record of same table - review http://allenbrowne.com/subquery-01.html#AnotherRecord

To retrieve the most recent record for each product - review http://allenbrowne.com/subquery-01.html#TopN

Exactly what do you need to do? If tblProducts does not store the price, then no UPDATE action is needed.

Do you want to retrieve the most recent price for each product? Do you want to calculate the change in price?

[buntybunty]

Hi -Yes, I want to calculate change in price (dont want the recent price)

ie. if there is a price change today, I need to be alerted (subsequent actions I can take via web page) but the list of records wherein price has increased or decreased. There is no update action in the tbproducts as it does not store price info

To pull data from another record of same table - review http://allenbrowne.com/subquery-01.html#AnotherRecord

To retrieve the most recent record for each product - review http://allenbrowne.com/subquery-01.html#TopN

Exactly what do you need to do? If tblProducts does not store the price, then no UPDATE action is needed.

Do you want to retrieve the most recent price for each product? Do you want to calculate the change in price?

[June7]

To find out if there is a price change, need to pull the two most recent records (today and yesterday - TOP 2 ?) for each product and calculate the difference. Seems to me will need both of the examples I provided links to.

[buntybunty]

Can give me sql pls

to find out if there is a price change, need to pull the two most recent records (today and yesterday - top 2 ?) for each product and calculate the difference. Seems to me will need both of the examples i provided links to.

[June7]

Exactly why do you need to do this? Why would this be useful? What do you want to do with the results? What if the data is like:

tblProductsPrice
OJFDKERWER, 01-Sep-15, 500
OJFDKERWER, 02-Sep-15, 500
OJFDKERWER, 03-Sep-15, 500
OJFDKERWER, 04-Sep-15, 525
OJFDKERWER, 05-Sep-15, 495
OJFDKERWER, 06-Sep-15, 510

The latest value on 06-Sep is an increase but is less than the value of 04-Sep.