Results 1 to 9 of 9
  1. #1
    rjgriffin46 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2014
    Location
    New Jersey
    Posts
    128

    Tracking Changes to a Field

    In the Students table of our student database it looks like we will need to track changes to our Status field. This is in order to know if a student was previously a Prospect and/or an Applicant before being Accepted into our Food Service Training Academy. Currently we only create a Students table record when an applicant is Accepted (and given that status) but we now want to track Prospects to see if they become applicants and applicants to see if they are accepted.



    Searching the internet I've see 2 approaches that seem like they would work. The first appears to allow you to select specific fields to track while the second writes copies of the entire record. They are:


    1. http://www.fontstuff.com/access/acctut21.htm
    2. http://allenbrowne.com/AppAudit.html

    It isn't necessary to include the user ID although it would be nice to know and I would also like to capture the date the change was made.

    It seems that it is only possible to track changes via a form which presents another possible issue I've been thinking about posting. Student Details is the form that would need to have the change tracking capability. There are actually 2 almost identical versions of the form, 1 that is a Navigation form sub-form and one that is a "stand alone" form. There are 2 forms because of references to a control that has to be done differently when referring to a Navigation sub-form. If there is a method of avoiding this I would appreciate some guidance on it.

    I would like to know if either of the 2 methods would be better or for my purposes some other approach. For example, could I just create a Student Status table as a child of the Students table so that whenever updating status, instead of just picking a status for the status field from a combo box using the Status table, the form would create a new Status record for the student? This does seem, though, pretty much equivalent to an audit log.

    As always, any thoughts and guidance would be appreciated.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tell us more about your company's interest in information related to

    Applicant
    Prospect
    Student


    If Pat Sjoberg (arbitrary person) is involved, what do you record in various tables etc? Using Pat as example, what info do you need; when would it change; where does status fit (simulate the business process(es) involved)?

    Good luck.

  3. #3
    rjgriffin46 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2014
    Location
    New Jersey
    Posts
    128
    As I mentioned in my post, we currently only create student (table) records when a student is accepted into the training program (a 16 week Food Service Training Academy run by the Community FoodBank of New Jersey). We are now going to create a student record when a prospective student is made know to us (ie., a student source, often a halfway house supervisor, tells us that Pat Sjoberg will apply to become a student). We want to track how many prospects each source sends us and out of those how many apply, get accepted and graduate.

    We only have 1 status field at this time but even so there are certain things that are known by definition. Looking backward through the student life cycle, I know that a student with Graduate/Alumni status (changed to upon graduation) once had an Accepted status since that is the status we give to all applicants who are accepted into the program. I also know that if the student has a Graduate/Alumni or an Accepted status (which they have while in the program until graduation) they also had at one time an Applicant status since one can only be accepted if one applies. One can't go from Prospect to Accepted status without having an Applicant status in between. So even with just one status field that gets changed with each event, we know at the end of the class that a student had a status of Applicant, Accepted and Graduate/Alumni if they graduated. Note that the status for an applicant who is not accepted would stay "Applicant" (alternatively we might change it to "Rejected"). And once accepted there are various other possible final status' other than Graduate/Alumni for those who do not graduate (eg., Cancelled, Dismissed, Resigned).

    The one thing that would not be known, however, as far as I can see, is whether or not they ever had a Prospect status because one does not have to have been a prospect to become an applicant. So, while logging all status changes as I was proposing in my original post would be a way to know if a student ever had a prospect status, it could be done another way. One possibility, as I see it, is to add another field to the student table that would simply indicate whether or not there was ever a Prospect status. It could be a Yes/No field which is set to Yes upon a change of status to Prospect and never updated again regardless of what further status changes occur (although there would need to be a way to change the Yes/No field if the update to Prospect status was a mistake).

    I have made very little use of either VBA or Macro, which I think are required. I did experiment with calculated table fields but could not think of an expression that didn't always change the Yes/No field. I created an Iff expression that said that if Status = Prospect, then Yes/No = Yes (the true part) but I didn't know how to do the false portion of the Iff so that it would do nothing if the Status was changed to something other than Prospect. I think I even tried in the false portion to set Yes/No = to itself but Access wouldn't save the expression because it referred to itself.

    I think I need an Event procedure to set the Yes/No to Yes if a status is changed to Prospect and to bypass this step if status is changed to another value.

    I would appreciate your thoughts on the merits of this approach (vs the audit log) and any coding suggestions.

    Thanks for your interest.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    it looks like we will need to track changes to our Status field. This is in order to know if a student was previously a Prospect and/or an Applicant before being Accepted into our Food Service Training Academy.
    before you jump to using a simple yes/no box, ask yourself why you are doing this? - what will the information be used for?

    Rather than using a yes/no field, consider using a dropdown. In some of the CRM type db's I have created, all contact records are the same but I have a field which can be called many things depending on context but basically means 'status at first contact' (a bit like the 'where did you hear of us?' type question when creating an online account) - it is a dropdown list of where the contact details came from. it may be the contact details were supplied by a referral, web, phone enquiry, researched list etc (again, depends on context). There may be a second field for date of first contact - usually a timestamp field. Once completed, it is never changed by users. A second status field can then be used to indicate current status, although this is often not required since other fields (or records in other tables) will make this clear anyway - but you might want another field to indicate why other fields/records are not completed - i.e. enquirer did not proceed because....

    This may be a bit 'off piste' for your requirements but just for information. In marketing parlance, before prospects, you have suspects. Suspects are basically contacts you know of but you have never contacted or been contacted by. Typically their details many have come from your own research or you have acquired a list from another organisation and often used for mailing lists etc (together with prospects and customers). It may be something you should also consider.

  5. #5
    rjgriffin46 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2014
    Location
    New Jersey
    Posts
    128

    Prospect Status Tracking

    Quote Originally Posted by Ajax View Post
    before you jump to using a simple yes/no box, ask yourself why you are doing this? - what will the information be used for?

    Rather than using a yes/no field, consider using a dropdown. In some of the CRM type db's I have created, all contact records are the same but I have a field which can be called many things depending on context but basically means 'status at first contact' (a bit like the 'where did you hear of us?' type question when creating an online account) - it is a dropdown list of where the contact details came from. it may be the contact details were supplied by a referral, web, phone enquiry, researched list etc (again, depends on context). There may be a second field for date of first contact - usually a timestamp field. Once completed, it is never changed by users. A second status field can then be used to indicate current status, although this is often not required since other fields (or records in other tables) will make this clear anyway - but you might want another field to indicate why other fields/records are not completed - i.e. enquirer did not proceed because....

    This may be a bit 'off piste' for your requirements but just for information. In marketing parlance, before prospects, you have suspects. Suspects are basically contacts you know of but you have never contacted or been contacted by. Typically their details many have come from your own research or you have acquired a list from another organisation and often used for mailing lists etc (together with prospects and customers). It may be something you should also consider.
    I appreciate your thoughts and while this function is very much like a CRM, its only a part of the database's purpose. The rest has to do with recording absences, test scores, evaluations and job placement. Do you have any thoughts or examples of coding that could be used to permanently record the fact that the student had a Prospect status at one time?

    Thanks again.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    rjgriffin46,
    Before adjusting the physical tables and fields etc, tell us more about the
    student life cycle
    .
    Identify clearly what exactly you want to retrieve/report from the data.
    when a prospective student is made know to us (ie., a student source, often a halfway house supervisor, tells us that Pat Sjoberg will apply to become a student). We want to track how many prospects each source sends us and out of those how many apply, get accepted and graduate.
    Along the line of what Ajax suggested, identify what "status" really means, and how many values are appropriate, and consider what might happen if you were to get a few new requests (new status values).

    Based on your post, you'll need a table for "studentSource" and some processes to add/edit values from the table.

    Do a data model, get your tables and relationships set up, then test it with some real data. Investigate and reconcile every "issue" that arises. Then test again. Once you are happy with scenarios and your model, and you can record and retrieve the required info, then adjust your physical database as per your model.

    As for student classes/attendance etc see the free data models at http://www.databaseanswers.org/data_models/index.htm for ideas. These are not (necessarily) meant as ultimate models, but do reflect the most common entities/attributes and relationships. These can be reviewed and used in whole or in parts with key tables and relationships extracted to suit your needs. Here is one re student roster


    Good luck with your project.

  7. #7
    rjgriffin46 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2014
    Location
    New Jersey
    Posts
    128

    Recording Student Prospect Status

    Quote Originally Posted by orange View Post
    rjgriffin46,
    Before adjusting the physical tables and fields etc, tell us more about the .
    Identify clearly what exactly you want to retrieve/report from the data.


    Along the line of what Ajax suggested, identify what "status" really means, and how many values are appropriate, and consider what might happen if you were to get a few new requests (new status values).

    Based on your post, you'll need a table for "studentSource" and some processes to add/edit values from the table.

    Do a data model, get your tables and relationships set up, then test it with some real data. Investigate and reconcile every "issue" that arises. Then test again. Once you are happy with scenarios and your model, and you can record and retrieve the required info, then adjust your physical database as per your model.

    As for student classes/attendance etc see the free data models at http://www.databaseanswers.org/data_models/index.htm for ideas. These are not (necessarily) meant as ultimate models, but do reflect the most common entities/attributes and relationships. These can be reviewed and used in whole or in parts with key tables and relationships extracted to suit your needs. Here is one re student roster


    Good luck with your project.
    I assumed you would know from my posting history that this database is fully developed and functional and for that I apologize. I would simply like some guidance on the objective of permanently recording the status of Prospect. If you can help with that let me know. Otherwise I will close this thread.

    Thanks.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I guess we're not on the same wavelength--- I was trying to help with a plan as to where Prospect Status (and other statuses) fit in your overall business, and what changes, if any, would be required to record the status or statuses assigned to an individual student as they participate in your facility in your database.

    Good luck.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Do you have any thoughts or examples of coding that could be used to permanently record the fact that the student had a Prospect status at one time?
    only thought to use a flag since you do not need to record the history of the record as it changes. I was merely suggesting that as a first step, before going for a single yes/no field, consider using a combobox as a more flexible solution. As for the rest of it, if you have been requested to create this additional functionality it is worth understanding why - presumably for analysis? And the thing about analysis is that once they analyse that information 'what' are they going to do with it? so other questions will come back like 'how long did they remain a prospect before coming an applicant?' or 'why don't prospects become applicants?' So my point is 'why' do they want this information and this should guide you as to 'how' to implement the requirement.

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

Similar Threads

  1. Job tracking DB
    By Homegrownandy in forum Sample Databases
    Replies: 2
    Last Post: 10-12-2015, 01:54 AM
  2. DB Tracking
    By hinkwale in forum Access
    Replies: 2
    Last Post: 01-02-2015, 07:19 PM
  3. Tracking Changes to DB
    By CementCarver in forum Database Design
    Replies: 3
    Last Post: 02-08-2013, 12:51 PM
  4. Production Tracking
    By old_chopper in forum Access
    Replies: 2
    Last Post: 10-11-2010, 12:12 PM
  5. tracking
    By mugziegumz in forum Access
    Replies: 0
    Last Post: 11-20-2008, 10:11 AM

Tags for this Thread

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