Results 1 to 8 of 8
  1. #1
    ptrodd
    Guest

    Auto Yes/no record entry (Access 97)

    I'm still working on my Local Plan representations database, users will need to indicate if a representation has been made within a 6 week period from the start of any stage as these are the valid representations although all have to be logged. I want to do this automatically to eliminate any user error so I want to be able to populate a yes/no field depending on what is entered into two date fields. I have a table that holds the local plan stages data including the start date for the stage and in my representations table I have a field that holds the date received. I need to know how I can get Access to determine if the date received is within 6 weeks of the stage date and then how to use this to automatically enter a yes in the yes/no field for the six week period. Can any body help me?

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Yes/no record entry (Access 97)

    When the record gets the focus use the DateDiff function to check the span of the dates. Then check the Yes/No field to see if it is correct based on the date difference. If it is not correct then set it to the desired value.

  3. #3
    ptrodd
    Guest

    Re: Auto Yes/no record entry (Access 97)

    Unfortunately I have only just started to even think about learning VB (currently working through Beginning Access 97 VBA Programming by Robert Smith and David Sussman, published by WROX). I thing that has come to mind since posting my first question is the fact that the table that an objection can be made at the second stage and therefore the six week period field in the representations table will need to queried against a new date in the local plan stages table. I think I may split the database up so that there will be representations for different stages in separate stage representation tables.

    I suppose I'm looking for more guidance as to what the code may be and how I am to use it, do I use it on the field where the yes/no will be needed (six week field) or some other way. Sorry to be such a dunce my line manager has been dragging his heels over using SQL for database design and therefore I have not done any Access training for some time ( it's a long time since I did my intermediate training).

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto Yes/no record entry (Access 97)

    I wouldn't advise splitting your data up that way. It will create tremendous maintenance problems if you do. Instead, create a separate table joined to the main table and put your stage-specific records in there with a foreign key to their parent record in the other table. That will allow you to handle as many stages as you need. Use a field that identifies the particular stage as well. That gives you the ability to either filter records to a particular stage or view an entire history simply by joining the two tables in a query and entering a parameter.
    Charlotte

  5. #5
    ptrodd
    Guest

    Re: Auto Yes/no record entry (Access 97)

    My model includes a table for the local plan stages which has an auto ID a stage field and a field for the stage start date. This table is used in the representations table for picking the stage. My main question is how do I fill in the yes/no return automatically for a field that indicates if a response was made within 6 weeks of the start date of a particular stage using the date recieved from the main table and the stage start date from the local plan stages table. My indication of splitting the tables was not for the local plan stages table but having a main table for each stage which is something I don't really want to do anyway. I've attached a sample from my model (as discussed with my line manager who is the principal analyst). The fields I in red are the relevant fields from the two tables with the one named sixeek being the yes/no field I want to have an automatic return set up for, hope this makes it clearer.

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Yes/no record entry (Access 97)

    Just an aside. This yes/no field is in reality a duplication of data as the value is 'included' in your 2 date fields. Good database design speaks against a duplication of data. If you need to check against this field in a query then you could always check against the date span instead.

  7. #7
    ptrodd
    Guest

    Re: Auto Yes/no record entry (Access 97)

    Are you saying that I shouldn't have a field that indicates the rerpresentation is within the six week period and use a query to only include records within a date span? I suppose I could set the query up to include records between the stage date and the end of stage date which could be held in the stage table and any record that has a recieved date between these two would be included in the query. You have given me another anevue of thought to work with Andy, thanks for that, this may also prove to be the simplest method.

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Yes/no record entry (Access 97)

    I'm saying that as you want to populate the field automatically then it is something that you could calculate on the fly. Storing duplicate information is more prone to errors in the database. Duplicate the contents of fields at your own risk. By duplication I also mean calculated fields such as this yes/no field will be.
    I can't (and won't) dictate how you design your database as you have to support and maintain it later. I also don't have the knowledge of the use and requirements for the database that you have.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •