Windows Vista 
Access 2007
HI I have a somewhat complex task for querying a table.
I have the fields [TreatyName], [Country],[Year],[Action].
-The TreatyName field contains the names of treaties [A,B,C].
-The Country field contains the names of countries [USA,FRN,AUL]
-The Year field contains the year the countries entered or withdrew from the treaty [1900,1980,1985,2000]. *The range of years is 1900-2010 but not all years are represented
-The Action field contains the what the country did for the treaty at a particular year [Signature,Ratified,Withdrew]. *For a given treaty, a country can sign a treaty in one year, ratify the same treaty in same year or another year, and/or in another year, withdraw from the treaty.
I want to treat the Signature and Ratification action of a treaty for a country as the same. A country can sign a treaty but never ratify the treaty but doing both does not matter to me. I am just interested in the first instance a country "enters" into the treaty(either Signing or ratifying or both). Similarly I want to know when a country withdraws from the treaty(if it ever does).
If there there are two records of a country entering the treaty(Signed and Ratified) I want Access to return the the record with the earliest date the country entered into the treaty and count that. Signed or Ratified, whichever comes first, and if applicable, the date it withdrew from the treaty.
For example, USA signed treaty "A", in 1980, then it ratified treaty "A" in 1985, then it withdrew from treaty "A" in 2000.
Thus I should see a query table showing the record when USA signed treaty "A" in 1980 (because it was the earliest) and another record of when USA withdrew from treaty "A" in 2000.
How do I get Access to look at the two records (from 1980 and 1985) and choose the earlier date? I assume I will be needing some fancy iif statements and some type of way for access to choose the earliest records. Any help would be appreciated!
Reply With Quote
