To simplify this, instead of using two tables for comparison, I've created a query to bring in 2008 Terms, so now I will be working in one query to compare columns of 2008 Terms against 2009 Terms to get the necessary results below.
qryAllTerms
Field: 2008Terms - could consist of terms: Net 30 Days, Net 60 Days, 1% 10, Net 30, Net Cash, etc.
qryAllTerms
Field: 2009Terms - could contain the same terms as tbl2008Terms.
My goal is to be able to compare field 2009Terms against field 2008Terms to see if terms have changed. I need to show change in terms for 2009, e.g., 2008Terms for ABC company were Net 30 Days, but now for 2009 they have been extended to Net 60 Days.
I would need results to return as "Extended" if there is an extended term in 2009, e.g., Net 30 to Net 60, or if we lost terms, e.g., 2008 was Net 30, but 2009 is now Net 15 Days.
Results should return to a field called "Terms Status" as either "Increased Terms" or "Extended Terms"
Hope I explained it a little better.
Thank you.