Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculation of Days Enrolled (2002, SP3)

    I have attached a file with sample data to show what I am trying to do. tblDatesEnr has one or more records per student displaying the date entered and if the student withdrew, the date withdrawn. Some students enter and withdraw several times during the school year. The query qryCountRecords counts the number of entries for each student. My goal is to come up with the number of days of membership per student. The table tblSchCal contains an entry for each school day and a field to be used for counting the days. The query qrySumDays displays the number of days for each student with 1 enter date and 0 dates withdrawn but I can't figure out how to develop a procedure to automatically calculate the membership days for all students no matter how many times they have entered and left. Can this even be done?
    Attached Files Attached Files
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  2. #2
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculation of Days Enrolled (2002, SP3)

    I would think you would first have to calculate the days between entering and leaving for each student for each incident.
    Thanks
    chuck

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation of Days Enrolled (2002, SP3)

    That would be easy to do if each student only had one enter date and either none or one withdrawal date. What I can't figure out how to do is to calculate the membership days for students with multiple enter and withdrawal dates.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  4. #4
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculation of Days Enrolled (2002, SP3)

    First I think the tables need to be redesigned. The first table would have an ID AutoNumber field, PERMNUM, and EnterDate. The second would have ID, and Leave Date. The tables would be joined one to many on the ID field. This would allow the Entry and Leave dates per incident to be isolated and then calculated.
    Thanks
    chuck

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation of Days Enrolled (2002, SP3)

    The data is actually being pulled from the student information software our county uses. I pulled the data into a query and then wrote it to a table for the purpose of displaying what I needed to accomplish. I could have one query pull just the entry dates and another pull just the withdrawal dates and try to join them together but I can't see how the correct withdrawal could be matched with each entry date. If I were designing this data into a table it would definitely be setup differently.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  6. #6
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculation of Days Enrolled (2002, SP3)

    As Paul Harvey would say, "And now for the rest of the story". Well that certainly complicates things a little. I am sure someone will come up with a solution for you before me, but I am going to keep working on it.

    Are you linked to the county data source or do you receive a file periodically?
    Thanks
    chuck

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation of Days Enrolled (2002, SP3)

    I am linked to the data. Thanks for trying to help. I am leaving the office for today but will check back in the morning.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Calculation of Days Enrolled (2002, SP3)

    I would use VBA to run thru the tlDatesEnr table creating another table matching the records 2 at a time and writing one record to a new table (called tblExtract) with both dates in the single record. Where there is only one record by itself just output that record in the tblExtract table.

    Then i would create another table (called tblFinal) with just a date in it as well as the PermNum field, this is generated from each record in the tblExtract table based upon the Enter and Leave dates.

    The table tblFinal will then have a record for each date that a Permnum appears. Then it's just a matter of joining both the tblFinal and tblSchCal on date.

  9. #9
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation of Days Enrolled (2002, SP3)

    Thanks for responding. I was unable to connect to the lounge yesterday so I kept working on it and finally along with a fellow worker was able to solve the problem through a series of queries.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Calculation of Days Enrolled (2002, SP3)

    would you let us know how you solved it though, not just for my benefit

  11. #11
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation of Days Enrolled (2002, SP3)

    Attached is a stripped down version of my solution. It is completely different than what I started with and to reduce the size I left out uneeded fields. There are a series of queries involved in the calculation process. qryEnterDates is a select query to narrow down the data needed for the process from the AENR table. tblDaysEnrolled was created to hold the contents of qryAppendTblDaysEnrolled. Each time the process is run the contents of this table is deleted and the new data is appended. tblMembership was created to hold the calendar counts from qryAppendTblMembership. Each time the process is run the contents of this table is deleted and the new counts are appended. The delete and append process is used rather than make table queries because the tables are located in the backend and the process is run from the frontend.

    qrySchCal is used to pull only school days from AATC. qryUpdateEnrolledMembership updates the Enrolled field in tblASTU.
    Attached Files Attached Files
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

Posting Permissions

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