Hi,
I am a novice Access user and I am faced with a complex data situation. I have a database in this format.
-Policyid is key and one groupid can have many policyid and each policyid has one product type
-The data in the table includes data from report_mnth Dec 2008-April 2009.
-Each Groupid has one renewal month;
What I have to do is:
-Find out if the group switched from one product to another in any two reported month and output that month when they switched, to another column(switched_mnth)
-Also, I would need to know to what product they switched to in a column?(Switched_prod)
-Add a flag if they switched (Y/N)
After addinng those new columns, I also need to finally filter data based on:
If they switched, get their data where the report_month = switched_mnth
If they did not switch, get data where report_mnth = renewal_mnth
Can someone please help? I am not also not sure what is the best method to do it: queries or function? I have spend a good one week on this and still fumbling. Any help will be greatly appreciated. Thanks so much!
Melissa