Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Duplicated Records Update (Access 2000)

    Hello, I have a database that I am working on that is used for a truancy program. There are three main tables, Address, Bio, and Truancy. The truancy table is a one to many relationship with the Bio table and holds records for each school year that the student is truant. However, at the end of the year, we have several students that were referred and a record was made, but will be working on them the following school year and a new record needs to be made. I tried to do an append query to append the students old info then, an update query to update the duplicated old record with the new school year, but it will update both records to the new school year. Is it possible to have it update only one of the records?

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Duplicated Records Update (Access 2000)

    Rachel,

    Interesting scenario, however, you sort of lost me with your request. If I understand you correctly, you have three tables. The Bio table holds information regarding a person. For each person that is truant, the truancy table holds each truancy record that occurs. So If I was in your system, the Bio table would have one record for Gary and if I was truant 25 times, the truant table would hold 25 records, one for each time I was truant. I assume the Address table is a one to one to the Bio table and hold addresses???


    Now at the end of the school year, if a person was referred (not sure what you are implying by this), where and how is the record made? (i.e., what data is applied to what table?)

    I guess I am sort of confused by your setup. Any chance you can clarify your database structure and what you are trying to do.
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Duplicated Records Update (Access 2000)

    Yes,for every one student, they could have many truancy records. The Bio table holds the info for each student, the address table holds the contact information for each student (is a one to many -- student may have several contacts) and the truancy table holds the records baised on school year for each students.
    ie, Sally was truant during the 2003-2004 school year and the school reported her to my office (a referral), a record is then made via a form that lists the bio info, the contact info, and the truancy info. Now, (Hope I haven't lost you) Say it is now the 2004-2005 school year, and Sally was not able to participate in our truancy program for the last school year, We then (for statistical purposes ) have to make a new record for the new school year since Sally will be participating in our program during the new school year.
    We have aprox 500 students that this scenario fits and were trying to make an easier way to duplicate the info from the last school year and make a new record but change the school year to the new school year. so that the truancy table would then show a record for the 2003-2004 school year and a record for the 2004-2005 school year.

    Every student is only listed once for each school year in the truancy table, howvever, they could be listed may times if they are referred to our office school year after school year. I hope this makes sense.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicated Records Update (Access 2000)

    Rachel:

    At the risk of butting it, there may be an easier solution than duplicating the records.

    Options:

    1) Use a boolean field to designate Active vs. Inactive participants in the Truancy Program. Such a field would be entered in the Bio table.

    or

    2) Add a "Membership" table. Table would consist of a field for the member ID (whatever the key field is in Bio) and the school year. The key field would be the combination of the two fields.

    I would like to avoid the duplication of extraneous data. I assume that the members' address doesn't vary much from year-to-year. If the address changes, do you track the address change history?

    Long story short, the boolean field or the Membership table would seemingly reduce the amount of record duplication.

    BTW, nice bubblegum!! :-)

    -Mike

  5. #5
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Duplicated Records Update (Access 2000)

    I am all about not having to duplicate anything..... I apologize, I am a self taught access database designer, Just started working with VB and code, and I am still trying to fix a lot of bugs from the first several hundred attempts of designing a database. :-)

    This Membership table, sounds good but, how exactly would it work? I would make a table with BioID (key field from Bio table) then as students are referred to the office how does the school year play into the Membership table? Will it still allow me to do crosstab queries and such to run stats on the group of students? So Instead of running stats by school year, I should run them by who is a member??

    Addresses and Bio info usually does not change from year to year. We haven't been tracking the change history of the addresses, but that has been on my mind for some time, just hasn't been top priority. I suppose that I would set up a table similar to the address table and call it address change history so when we change a address, we can go back and see what it was before?!?

    Anyways thank you for your post.. I completely love your motto! Fits me to a tee!

  6. #6
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicated Records Update (Access 2000)

    Rachel:

    For purposes of example, suppose I had 200 members in my Truancy Club for the 2002-2003 school year. By the end of the year, 20 students no longer needed my services. I marked them as inactive. All 200 had entries in the Membership table, so that I could track when they were members.

    When the 2003-2004 school year started, I ran a routine that copied (or added) the 180 active members' IDs into the Membership table with the school year set as 2003-2004. As the year progessed, I added new Bio records, and subsequent address, and membership records for my new members.

    Does this make any sense?

    -Mike

  7. #7
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicated Records Update (Access 2000)

    Rachel:

    I forgot to answer at least one of your questions....

    Yes, you may still run cross tab queries against a query that would join the tables (as opposed to a single table).

    -Mike

  8. #8
    Star Lounger
    Join Date
    May 2003
    Location
    Kansas City, Kansas, USA
    Posts
    76
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Duplicated Records Update (Access 2000)

    Thank you so much this totally makes sense. I never thought of doing it that way! I appreciate you taking the time to help me with this....

  9. #9
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Cincinnati, Ohio, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicated Records Update (Access 2000)

    You're welcome.

Posting Permissions

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