Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Join table on not equal

Status
Not open for further replies.

BrianWen

Programmer
Jun 8, 2009
102
DK
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.
 
As table is a reserved word:
Code:
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
 
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.
 
Could you, please, copy'n'paste the real SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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
 
What is the data type of MI_installed_capacity_growth_rates.year ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top