Query to determine the last year in a table

[kdirvin]

I am using Access 2007. My database tracks contracts spanning across multiple years, and data about these years are stored in tbl_Contract Years. Fields include Contract Number, Fiscal Year, Year Number (whether it’s the 1st, 2nd, or 15th year), and Year Cost. Therefore, if Contract A was awarded in FY09 and ended in FY12, Contract A would have four entries in this table, FY09 being 1st and FY12 being 4th.
I need to build a query in which I can enter a fiscal year and display which contracts are on their last year during the specified FY. To return to my example, Contract A would pop up if I queried for FY2012. I am not sure how to go about this. In my thinking, I would prompt the user for the fiscal year, then set the query to display records in which the year AFTER the specified fiscal year is null.
Thank you for any help you can provide!

 

[jdraw]

Be careful with the details

you say

if Contract A was awarded in FY09 and ended in FY12, Contract A would have four entries in this table, FY09 being 1st and FY12 being 4th.

Then say

To return to my example, Contract A would pop up if I queried for FY2012.

No, and I'm being picky, you can not assume that FY12 is the same as FY2012...

As for your database, how have you structured tbl_Contract Years?
How do you identify the StartFY, and the duration or EndFY?
Picky, picky, perhaps, but you're trying to devise a query, and you're overlooking details.

 

[kdirvin]

Hello,

Thanks for your quick response.

In tbl_Contract Years, the Fiscal Year field determines the full fiscal year. This field holds the full four-digit year. When the user first enters the years information into the DB, they select "2011" into the field to designate FY11 or "2012" for FY12, and so on.

For my query, I will design a combo that lists the full four-digit year. That way, the user cannot type his or her own version for the fiscal year - he or she will have to select a year that already matches the type of data in the Fiscal Year field.