Subquery to select value based on calculated date

[Scotbot]

Hi and thanks for taking the time to help with my question.

I have one table with two fields:

Date (dd/mm/yy)
Sales (number eg 17)

I have a query that returns both columns and hence the sales on each date.

I would like to add another field to the query that returns the sales on the previous day, called 'previous'.

I think I need to use a subquery but I'm not sure of the syntax. I know how to use dateadd to get for example the date of the previous day but this needs to be worked into a subquery that uses it to lookup the sales field against the returned date from dateadd.

Any help would be much appreciated!

Cheers, Scott.

 

[stopher]

See attached example (see qryResult). Please ask questions if it doesn't make sense.
Chris

 

[Scotbot]

See attached example (see qryResult). Please ask questions if it doesn't make sense.
Chris

Hi Chris,

Thanks for uploading the file but when I open it I can only see a table, no queries? I'm using Access 2010 if that's important.

Cheers, Scott.

 

[stopher]

Hi Chris,

Thanks for uploading the file but when I open it I can only see a table, no queries? I'm using Access 2010 if that's important.

Cheers, Scott.

Given the day I've had that wouldn't surprise me

Have you tried the drop down to switch from listing tables to listing queries?

Chris

 

[Scotbot]

Given the day I've had that wouldn't surprise me

Have you tried the drop down to switch from listing tables to listing queries?

Chris

Apologies, I'm so used to all my databases showing all Access objects that I didn't even know you could filter the navigation pane!

Based on your demo I've been able to create a weeklay sales uplift matrix for a 2 year period, thanks for your help - much appreciated!