Solved Archive old records

[Mercy Mercy]

Hi everyone. I am making students database. Is there a way to archive records of students who have left school e g a school has grade 1 to grade 6. When a child finishes grade 6, the records are to be archived. Should not be active in existing learners. Thanks in advance.

 

[bob fitz]

Hi everyone. I am making students database. Is there a way to archive records of students who have left school e g a school has grade 1 to grade 6. When a child finishes grade 6, the records are to be archived. Should not be active in existing learners. Thanks in advance.

You could add a "LeavingDate" date field to one of your tables.

 

[arnelgp]

if you can identify which students (by any Flag field) have finished, then yes you can.
you can even archive them on a separate db.

 

[The_Doc_Man]

As noted by bob fitz and by arnelgp, all you need is an indicator in the record. There is an old rule here: If you know you are eventually going to ask a question with Access, make sure ahead of time that the answer will be available in the record. SO you get to pick how you do it.

1. bob fitz's answer: Add a date field to the record
2. arnelgp's answer: Add a "finished" flag to the record
3. my answer: EITHER 1 or 2 (don't need both) OR take advantage of an existing date field or other information already in the record that implies what it is that you need to make the decision.

When you do have a "metric" (a way to identify the records to be archived), do it in four steps. FIRST, backup the database as a precaution. SECOND, export the records identified by the metric. THIRD, delete the records you just exported. FOURTH, verify by inspection that it did what you wanted. If it succeeds, you can discard the backup. If not, you have a way to start over again with no data loss.

 

[theDBguy]

Just curious, how big (file size) is your BE file now?

 

[Mercy Mercy]

As noted by bob fitz and by arnelgp, all you need is an indicator in the record. There is an old rule here: If you know you are eventually going to ask a question with Access, make sure ahead of time that the answer will be available in the record. SO you get to pick how you do it.

1. bob fitz's answer: Add a date field to the record
2. arnelgp's answer: Add a "finished" flag to the record
3. my answer: EITHER 1 or 2 (don't need both) OR take advantage of an existing date field or other information already in the record that implies what it is that you need to make the decision.

When you do have a "metric" (a way to identify the records to be archived), do it in four steps. FIRST, backup the database as a precaution. SECOND, export the records identified by the metric. THIRD, delete the records you just exported. FOURTH, verify by inspection that it did what you wanted. If it succeeds, you can discard the backup. If not, you have a way to start over again with no data loss.

Thanks so much.

 

[Mercy Mercy]

As noted by bob fitz and by arnelgp, all you need is an indicator in the record. There is an old rule here: If you know you are eventually going to ask a question with Access, make sure ahead of time that the answer will be available in the record. SO you get to pick how you do it.

1. bob fitz's answer: Add a date field to the record
2. arnelgp's answer: Add a "finished" flag to the record
3. my answer: EITHER 1 or 2 (don't need both) OR take advantage of an existing date field or other information already in the record that implies what it is that you need to make the decision.

When you do have a "metric" (a way to identify the records to be archived), do it in four steps. FIRST, backup the database as a precaution. SECOND, export the records identified by the metric. THIRD, delete the records you just exported. FOURTH, verify by inspection that it did what you wanted. If it succeeds, you can discard the backup. If not, you have a way to start over again with no data loss.i

I am requesting for a sample database. Thanks in advance.

 

[bob fitz]

I am requesting for a sample database. Thanks in advance.

Not sure what you want a sample of but MS has a Student template file.
Post a copy of your db if you want an example of how you might flag a record.

 

[Pat Hartman]

A date field always provides more information than a flag so I would use a flag. Then your queries need to include "LeavingDate" in their criteria. So when you want active students, you would use:

Where LeavingDate Is Null

and when you want inactive students, you would use:

Where LeavingDate Is Not Null

So, the date works exactly the same way a flag would work except that you also know when the student left.

 

[Gasman]

Missed a NOT out there Pat?

so I would use a flag

 

[Pat Hartman]

Yep, Oh, for the eyes of the young

 

[Gasman]

Yep, Oh, for the eyes of the young

Well, if you call 69 young, so be it.

 

[Cronk]

You could add another field -Reason (for leaving) ie Expelled, Transferred, Graduated

 

[tonez90]

Hi everyone. I am making students database. Is there a way to archive records of students who have left school e g a school has grade 1 to grade 6. When a child finishes grade 6, the records are to be archived. Should not be active in existing learners. Thanks in advance.

Hi an easy way would be to add two fields to the student records. One dealing with a completion date or similar and then a yes/no (checkbox) to signify an archive of the record. When you then display or search you could then exclude or include the archived records if you wish. I am developing a community organisation database which does this sort of thing for its volunteers so that if they ever come back I can simply take off the archive and then all I would do is verify the information stored (for up to date accuracy).

This just one option. Another option is to simply have a tick box on the record form which if ticked would move the data from the student table(s) to an archive table. Then you would need to remember this old data in your queries by looking at two tables instead on one. Cumbersome and more work but it also works.

Hope this provides some ideas.
Tony

 

[GaP42]

Is there a way to archive records of students who have left school e g a school has grade 1 to grade 6. When a child finishes grade 6, the records are to be archived.

The suggestions above are all valid, however you need to look carefully at implementation meeting your needs. As far as archiving students who have left school at the ned of grade 6, there would apear to be some clear criteria to use as a basis for this - I recall your need to promote students from one year to the next. It might be at this point you could set the date/flag for those to be archived. By archiving of students what will you do about the course history/enrolment information? If you need to preserve this your archive process will need to move the child records needed and delete the otherwise orphaned records in the used active tables in the archive process.

However you may also have a need to mark student records as enrolment progresses. Not all students remain enrolled throughout a year. A leaving date and archive flag can be set and used to hide these "inactive" student records from queries/views/reports. And when the archive process is executed these records that are not in the year 6 to leaving process can also be processed with them.

 

[Mercy Mercy]

Thanks so much. Date field is working.

 

[Mercy Mercy]

A date field always provides more information than a flag so I would use a flag. Then your queries need to include "LeavingDate" in their criteria. So when you want active students, you would use:

Where LeavingDate Is Null

and when you want inactive students, you would use:

Where LeavingDate Is Not Null

So, the date works exactly the same way a flag would work except that you also know when the student left.

You are very genius. I am making Ms access applications because of your input. Thanks so much.

 

[Pat Hartman]

You're welcome