Help an Access NOOB with an interesting query?

[Machwon]

Hey folks- I'm new around here, and relatively new to Access in general. I work for a Colorado Assessor's Office and am becoming their query guy and need some help I'm running Windows 7 with Access 2003. We do our querying via Access on our County Assessment database. Without getting too into the boring details here's the situation.

We are needed to find Natural Resource Accounts (ACCOUNTNO = "N*") that were active (STATUS = "A") in 2013 (TAXYEAR = 2013), and then deactivated (STATUS = "D") in 2014 (TAXYEAR = 2014). Those are the only accounts we need- however with my basic skills I am only able to get to the point of having all Natural Resource accounts that were both active and deactivated in both 2013 and 2014. I have attached some screenshots of what we are looking at. Can anyone think of a basic expression or If Then statement that I could use to get only the accounts that were Active in 2013, and deactivated in 2014? Thanks in advance folks!!



The table that is resulting with about 9,000 records- we are looking for less than 100 meeting our criteria.





Thanks everyone!

[ranman256]

1. you can make 2 queries and put them together in a UNION query (3rd qry). This helps you KNOW what you are querying definitively.
Q1 = (STATUS = "A") in 2013 (TAXYEAR = 2013)
Q2 = (STATUS = "D") in 2014 (TAXYEAR = 2014)
Q3 = select * from Q1 UNION select * from Q2

OR

in 1 query
you must make a separate criteria line for EACH different option
1st line like you have but only status A, in 2013
2nd line SAME criteria like <= verstart, etc,.. EXCEPT use : status D in 2014

[Machwon]

Ranman- thank you so much for the quick response. As I have never tried a union query- I attempted the 2nd option (just adding a separate criteria line). It did narrow down the results, however the table is now displaying ALL active 2013s, and all active 2014s... is there an easy way to keep the duplicates to narrow it down?

[ranman256]

Did you try setting the query property: UNIQUE VALUES = YES.
I havent tried with multiple criteria lines, but should work to remove dupes. (tho if you include year, youd get dupes w different years)