Dependent query

[Alisdairjohnston]

This is an attempt to overcome a mental block!

I am trying to extract 2 sets of Scores from 2 sequential years, from a single table. The result will display side by side in a report (or a word document), showing this year's results and last year's results.

The user will be asked to input a year when they open the query (normally from a button on the menu).

The fields are:

Score ID; ScoreTeam; ScoreYear

I can extract the scores for a particular year by a normal select statement in the ScoreYear field of the query eg; "[Enter Year}.

What I cannot figure out (and it will be obvious to many on here, I guess) is how to return the same information from the year before, in the same query.

 

[Alisdairjohnston]

Do you mean something like "ScoreYearPrevious:[ScoreYear]-1"?

That returns the actual previous year, but not the other data (ie, the score).

 

[Grumm]

First extract the scores for that particular year

select * from ScoreTable where ScoreYear =2016

Then you can perfectly do a new query

select * from ScoreTable where ScoreYear =2016-1

If you want this in one single query, just do where ScoreYear=2016 or ScoreYear=2016-1

(You need to change 2016 by the content of your select year field.)

 

[Alisdairjohnston]

Ah..! Can I use that syntax with a value input by the user, something like:

select * from ScoreTable where ScoreYear =[Enter Year]

(SQL has been a bit of a closed book up till now, but I think I can see the logic)

 

[arnelgp]

select ScoreTeam, Score, T2.Score AS LastyearScore
from ScoreTable LEFT JOIN
(SELECT T1.ScoreTeam, T1.Score FROM ScoreTable AS T1 WHERE T1.ScoreYear=[Enter Year]-1) AS T2 ON ScoreTable.ScoreTeam = T2.ScoreTeam WHERE ScoreTable.ScoreYear =[Enter Year]

 

[Alisdairjohnston]

That looks as it it might do the trick. I will try it in the morning - meantime, thanks!