Join table on not equal

[BrianWen]

Hello!

I've have created this query in MSSQL:

SELECT gr.year, gr.col1, s.col1 AS prevyearcol1
FROM table AS gr
LEFT JOIN table AS s ON s.year = gr.year-1
ORDER BY gr.year

Access 2007 crashes when running this. Is it possible to get this result by typing it in another way?

I simply want each record to have a value from the record with the previous year.

 

[PHV]

As table is a reserved word:

SELECT gr.year, gr.col1, s.col1 AS prevyearcol1
FROM [!][[/!]table[!]][/!] AS gr 
LEFT JOIN [!][[/!]table[!]][/!] AS s ON s.year = gr.year-1
ORDER BY gr.year

BTW, please define "Access 2007 crashes"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BrianWen]

table is not the problem....I just wrote table instead of the real tablename

It crashes as soon as I write the -1 in the JOIN, so that it's not a 1 to 1 comparison. Crashing meaning I get a popup saying Access stopped working and I have to start Access all over.

 

[PHV]

Could you, please, copy'n'paste the real SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BrianWen]

I don't know whatever I did before, but no it doesn't crash, it just pops up and says that my JOIN expression is not supported:

SELECT gr.year, gr.installed_mw, s.year AS prevyear FROM MI_installed_capacity_growth_rates AS gr LEFT JOIN MI_installed_capacity_growth_rates AS s ON s.year = gr.year-1
ORDER BY gr.year

 

[PHV]

What is the data type of MI_installed_capacity_growth_rates.year ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BrianWen]

It's a Number. I found out it's because I use LEFT JOIN. It accepts INNER JOIN and then I set up a GROUP BY. I think I have something that works now. However I need to check some of the values to confirm.