Subquery to pull data from same table at earlier time

[ymeaga1n]

I need some help guys. I'm pretty new to SQL and I don't know if what I'm trying to do is allowed. I'm running a query on an Access datatable.

I want query to display 3 columns to me.
Column 1 = Date.
Column 2 = Total Sales on that date.
Column 3 = Total Sales from year ago.

I ran the following query but I get a blank third column. I've highlighted that part of the code with blue. Any ideas as to why it's not working, or what I could do to make it work?

SELECT data_bdat AS [Sales Date], data_sls AS [Total Sales], (select fv.data_fv_sls from table1 where data_bdat = data_bdat - 364) AS [Last Year Sales] FROM table1;

 

[ymeaga1n]

Wow, I hit submit by accident...now I don't know how to modify it. I made a mistake in the code I posted, it is supposed to be...


SELECT data_bdat AS [Sales Date], data_sls AS [Total Sales], (select data_sls from table1 where data_bdat = data_bdat - 364) AS [Last Year Sales] FROM table1;

 

[dhookom]

Try:

SELECT data_bdat AS [Sales Date], 
       data_sls AS [Total Sales], 
      (select Sum(data_sls) from table1 A where A.data_bdat = table1.data_bdat - 364) 
        AS [Last Year Sales] 
FROM table1;

I assume you know 364 isn't very reliable. You might consider using DateAdd("yyyy",-1,table1.data_bdat)


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rac2]

What happens with this

SELECT a.data_bdat AS [Sales Date], 
       a.data_sls AS [Total Sales], 
       ( select data_sls 
         from table1 
         where DateDiff("d", data_bdat, a.data_bdat) = 364
        ) AS [Last Year Sales] 
FROM table1 a;