Auto Create Linked Tables

[jparks2010]

So, I have a problem. I am working with a database that has many relationships but after a set time I want it to split the tables and archive the old data. I have been researching ways to do this because for this case the end user cannot do it as they dont know how. For preview my database is set up as follows:

Table1 is teachers:
2 Fields: index (Primary) & Last Name
Table2 is students:
5 Fields: ID # (Primary), Name, Grade, nickname, notes text field
Table3 is Tracking Info:
6 Fields: ID # (Retrieved with DLookup from Table2) not a stored value
Name (Entered via form)
Date (Automatically set to current date on form)
Academic (Lookup field based on typed in data not table)
College Adv. (Lookup field based on typed in data not table)
Mentor Info (Lookup field based on typed data not table)
Field Trips (Lookup field based on typed data not table)
Multiple Con (Lookup field based on typed data not table)
Advisor (Lookup column based on Table1 via index and it stores the value from LastName in the field)

Table 3 is the main table storing the everday info. Data Entry is simplified via a Form with each of these fields (Excluding ID #)

My database also contains other unrelated tables, forms, macros, reports and such. The users who need to work with this table want to create a new table each semester to speed table load time and archive data based on semester. They dont know how to do this and I cannot find an easy solution. So, I am looking for advice as to the best way to go about this.

 

[Adam Caramon]

The users who need to work with this table want to create a new table each semester to speed table load time and archive data based on semester. They dont know how to do this and I cannot find an easy solution. So, I am looking for advice as to the best way to go about this.

It would seem that the logical answer is to use a query to pull data by semester. If the number of records being displayed at one time is what is causing the slower load time, then having a form based on a query that only pulls a limited number of records should speed this up.

 

[Isskint]

Archiving can be done with a set of Make Table queires (to export out of the DB) OR Append query (to keep archive in the DB) and Delete queries.
Set up a Make Table (or Append) query either with preset criteria (semester id, older than so many days etc) OR reference an Archive form that supplies the criteria. Then set up a delete query that will delete data based on the same criteria as the Make Table query. Package these in a macro to run in sequence.