Track a files history.

[DB_Don]

Hello all. New member here. I have searched so forgive me if this has been covered or I've posted to the wrong thread.

First, I'm new to Access and databases (self taught) in general. I work for a school, and what I am trying to do is set up a table that will keep track of every time we send or receive a students records. Then I want to be able to enter a students I.D. number to get a complete record of requests, whether to or from our school, but I don't want a record overwritten.

I'm looking to acheive something like this:

ID Last Name First Name Date Sent To From Comment
002548 Student Ima 10/01/18 Franklin HS Records request from Franklin
002548 Student Ima 10/08/18 Franklin HS Records returned to Austin HS
002548 Student Ima 12/11/18 Raunson Acadamy Student in junvinile detention for 2 weeks



The way I have my Db setup now, everytime a students ID is entered, the To/From info is overwritten. I want to have To/From info on the first table overwritten and a second table that is added to each time new info is entered to acheive something similar to the above. AND I would like to do it from the same form. Does this make sense? Is this something too advanced for a beginner? Has my grasp exceeded my reach? Thanks for any info or advice you are able to provide.

[ssanfu]

Welcome to the forum.....


Would you post your dB? Only needs a few example records.... change any sensitive data.

First make a copy of the dB.
Using the copy, do a "Compact & Repair", then Zip (compress) it and attach to a post.

[orange]

I agree with Steve that seeing what you have done so far would be helpful.
Here is a link to some info on Database Planning and Design in various formats.

Good luck.

[ArviLaanemets]

You need tables
tblStudents: StudentID, FirstName, LastName, ...;
tblPartners: PartnerID, PartnerName, ...;
tblActivityTypes: ActivityTypeID, ActivityTypeText, ...;
tblActivityLog: ActivityID, StudentID, [ActivityTypeID], ActivityDirection, ActivityDate, PartnerID, ActivityComment.

I assume all PK ID's for tables are autonumeric;
ActivityType and tblActivityTypes are optional. When you need only register sending/receiving documents, you can drop them, but using them you can do more than that;
ActivityDirection will have 2 possible values, eg. 1 for incoming and 2 for outgoing.

You need a form based on tblStudents ( let's it be ParentForm), where you select a student (use an unbound combo on form for selecting student and for activating according record in tblStudents;
In same form, you need an unbound combo (let's it be cbbSelectActivityType) to select activity type (in case you use activity types, of-course);
In same form, you must have a subform based on form (let's it be ChildForm) which has table tblActivityLog as source. Subform is linked to Parent form - tblActivityLog.StudentID to tblStudents.StudentID [and ltbActivityLog.ActivityType to ParentForm.cbbSelectActivityType];
In ChildForm, the controls for ActivityID, StudentID [and ActivityTypeID] must be hidden;
In ChildForm, you can set the default value for control linked to ActivityDate as current Date/DateTime (NB! For control, not for table field!). When you set the default value for control, you may allow to edit the date or not - as you like.

With such setup, whenever you select a student in ParentForm, all activities [of selected type] for this student are displayed in subform. Any new activity entered into subform will be attached to student [and activity type] currently active in ParentForm.

Another way to get a list of activietis for student will be designing a Report for this (you can have a button in ParentForm, which generates a report for active student).