Help pulling multiple years multiple projects

[DEllis]

Here is the situation. I have an access database with a table that has projects, some of which have information in consecutive years in two and some only have in one year. What I am trying to do is pull previous and current year, current and next year and just current year projects. I do not want projects that were from last year and ended in last year. If the project was started last year it may have carried over to this year and therefore can still be edited. Those projects that are only last years, I don't want to pull in when the dashboard is opened.

I need help. I've tried all types of creative queries and can't seem to hit the nail on the head.

Here is an example of the data.

Pro_Num Year Data
1 2010 ABC
2 2010 ADX
2 2011 ADX
3 2011 CFG
4 2011 GHI
4 2012 HGI
5 2012 ZZZ


So you see, what I would like to pull when the project owner opens up his/her dashboard is only those projects that are from the previous year that rolled over to current year, those that are only in the current year, and those that are in the current year with carry over in next year. So from this list the query would pull in projects 2 3 4 but not 1 and 5. 5 would be pulled in next year as would 4, but not the rest.

Is there a way to do this?! ::Scratching head, losing hair:: Please help!!!

 

[revans]

This

SELECT tblProjects.Pro_Num, tblProjects.Year, tblProjects.Data
FROM tblProjects
WHERE (((tblProjects.Pro_Num) In (SELECT Pro_Num FROM tblProjects WHERE Year=2011)));

retrieves this
<TABLE border=1 cellSpacing=0 bgColor=#ffffff><CAPTION></CAPTION><THEAD><TR><TH bgColor=#c0c0c0 borderColor=#000000>Pro_Num</TH><TH bgColor=#c0c0c0 borderColor=#000000>Year</TH><TH bgColor=#c0c0c0 borderColor=#000000>Data</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#c0c0c0 align=right>2</TD><TD borderColor=#c0c0c0 align=right>2010</TD><TD borderColor=#c0c0c0>ADX</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>2</TD><TD borderColor=#c0c0c0 align=right>2011</TD><TD borderColor=#c0c0c0>ADX</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>3</TD><TD borderColor=#c0c0c0 align=right>2011</TD><TD borderColor=#c0c0c0>CFG</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>4</TD><TD borderColor=#c0c0c0 align=right>2011</TD><TD borderColor=#c0c0c0>GHI</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0 align=right>4</TD><TD borderColor=#c0c0c0 align=right>2012</TD><TD borderColor=#c0c0c0>HGI</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

hth,

Rich