Results 1 to 9 of 9
  1. #1
    Accessuser is offline Novice
    Windows Vista Access 2003
    Join Date
    Sep 2010
    Posts
    5

    Using function to track change through months

    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

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    is this a homework assignment?

  3. #3
    Accessuser is offline Novice
    Windows Vista Access 2003
    Join Date
    Sep 2010
    Posts
    5
    Actually, no. Its something I am working on at work! Can you help?

  4. #4
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by Accessuser View Post
    Actually, no. Its something I am working on at work! Can you help?
    yes I can, but I had a difficult time reading the original description...I would better understand if you gave an actual scenario as if it were happening to you right now.

    e.g. - someone called in a switched their 'product'...

  5. #5
    Accessuser is offline Novice
    Windows Vista Access 2003
    Join Date
    Sep 2010
    Posts
    5
    ok. Let me try explaning. We are an insurance company and I have a database which has companies( groupid, group name) and their policy description. One company can have many different policies. Each policy number is associated with one product name at one point. For example, policy no 2109 has product name Type1. Companies renew their policy every year. So, if they joined in Jan 2009, their renewal date would be Jan 2010.

    Companies can also switch products mid year. So, they can go from type1 product to type2 product. Their policy number would also change in that case. I have to find out how many such companies are there, what product do they switched to and when did they make that switch. Hope I explained it?

  6. #6
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    and could you briefly explain each of these fields...:



    I know it may seem obvious, but to give some good advice you have to be sure, right? Is that all of the relevant information that you need to analyze?

    It would also be helpful if you hinted at what a single entry to the above table means, and why one would be entered. I assume it's indicate a policy or product change, but you never know...

  7. #7
    Accessuser is offline Novice
    Windows Vista Access 2003
    Join Date
    Sep 2010
    Posts
    5
    Sorry, I mentioned one thing incorrectly in the previous post. When a group changes product, the policy no remains the same as before; it doesn't change.

    The way the database is set up is : It has at least one row for each policy number and for each month from Dec to April. So Policyid 1 would have at least 4 lines for each reported month. (and additional rows if switched to another product).

  8. #8
    Accessuser is offline Novice
    Windows Vista Access 2003
    Join Date
    Sep 2010
    Posts
    5
    Quote Originally Posted by ajetrumpet View Post
    and could you briefly explain each of these fields...:



    I know it may seem obvious, but to give some good advice you have to be sure, right? Is that all of the relevant information that you need to analyze?

    It would also be helpful if you hinted at what a single entry to the above table means, and why one would be entered. I assume it's indicate a policy or product change, but you never know...
    Sure, I can explain that.

    Policyid is the key; the unique identifier for the policy. Group id is the company's id and group name is the company's name. Each company has one unique id but can have more than one policy. The data is arranged so that data for months Dec 09-apr 2010(Report_mnth) are there in the table.

    Every year, the company decides whether they will have the same insurance provider or not. Renewal month is the month when they have to renew their policy. Product type is the type of insurance they have.

    This is an instance of data; so this won't be updated. I am looking at the past data and am trying to analyse trend. So, all I have to find is from Dec 09-Apr 2010, did the companies change their product type, what prod. they changed to and in which report month they did that?

    Thanks for your help!
    Melissa

  9. #9
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by Accessuser View Post
    Sure, I can explain that.

    Policyid is the key; the unique identifier for the policy. Group id is the company's id and group name is the company's name. Each company has one unique id but can have more than one policy. The data is arranged so that data for months Dec 09-apr 2010(Report_mnth) are there in the table.

    Every year, the company decides whether they will have the same insurance provider or not. Renewal month is the month when they have to renew their policy. Product type is the type of insurance they have.

    This is an instance of data; so this won't be updated. I am looking at the past data and am trying to analyse trend. So, all I have to find is from Dec 09-Apr 2010, did the companies change their product type, what prod. they changed to and in which report month they did that?

    Thanks for your help!
    Melissa
    The first thing to do here Melissa, is realize that jumping in and creating the first thing that comes to mind is not advisable. I only mention that because I'm about to say this...

    I would recommend to you a systematic way of sorting data out, because the analysis you need has to be done in many steps. It's simply too complicated to do in one function. (Now, that's not to say it can't be done...in fact it can).

    Here are the steps I would take:

    1) Get your data sorted by group id, then report mnth, then by product type.
    2) use a DAO function call in a query, or create a brand new table from that function with records indicating what you need.


    The issue that I see most relevant here is the fact that you date fields have no accompanying 'year' indicators. That can present a problem when you're coding this because...(how do you know what year is what?). Does that make sense?

    At any rate, I don't really see how a chart can help you here either, because the data is not fully normalized, which compliments a database the best.

    The function is going to loop the records, top to bottom after the records are sorted, record a current record's values and point to those while looping through the rest of the set for comparisons. The function I'm describing will loop through the recordset 'n' times, with each time analyzing a different company. The number of loops will equal the number of different companies that are listed in the table. Analyzing one company per loop will help split the multiple tasks up into one per loop so you don't get lost in variables...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Percent Change between months by group
    By P5C768 in forum Queries
    Replies: 3
    Last Post: 09-15-2010, 06:59 PM
  2. linking audio track to database
    By sean in forum Access
    Replies: 5
    Last Post: 09-21-2009, 06:25 PM
  3. passing along #'s to track packages online
    By Coolpapabell in forum Access
    Replies: 0
    Last Post: 09-11-2009, 08:36 AM
  4. Track users use of Access database
    By booyeeka in forum Programming
    Replies: 1
    Last Post: 02-26-2009, 02:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums