V
vavroom
I'm building new functionality on top of an existing Access 2010 database for a colleague. This is to manage students sitting exams overseas. I'm trying to figure out the best approach before I start (re)building. I've been reading through the web and groups, but haven't found anything that triggersa "ah ha!" moment. I'd appreciate any thoughts on best approach.
Basic student and exam information is kept in an administration system (non-Access), but doesn't allow modifications and customisation. Using Access permits managing the overseas exam process for these students.
Information is extracted from the administration system in two CSV files: Student contact details, and student exam details.
There are two existing tables in the Access DB: OSStudents and OSPapers
OSStudents has the following fields (only relevant fields listed):
- StudentID
- FirstName
- LastName
OSPapers has the following fields (only relevant fields listed):
- StudentID
- PaperNumber
- Venue
- ExamDate
There are many changes to the overseas exams information. Students move overseas, withdraw from papers, move between venues, or require a date change.At peak exam time, extraction from the admin system and import into Accesshappens once a day, several days in a row.
Currently, a report is run and printed after the first import. The data from the two tables gets deleted before any import/update of information. Changes are manually written on the printed report(!!!). It is, as you can imagine, time consuming and prone to error.
I want to be able to display/highlight student/papers that have had changes- withdrawals, new students, venue changes and date changes.
I am unsure of the best way to handle this. I thought of moving the data into another table, running the import, then comparing the old data to the new. This would work ok except that there are multiple new imports, and I don't want to keep creating new tables. I'm toying with the idea of creating athird table that only keeps track of changes, but I'm not sure how that would work.
Any idea, suggestions, or examples would be greatly appreciated.
Thanks
Basic student and exam information is kept in an administration system (non-Access), but doesn't allow modifications and customisation. Using Access permits managing the overseas exam process for these students.
Information is extracted from the administration system in two CSV files: Student contact details, and student exam details.
There are two existing tables in the Access DB: OSStudents and OSPapers
OSStudents has the following fields (only relevant fields listed):
- StudentID
- FirstName
- LastName
OSPapers has the following fields (only relevant fields listed):
- StudentID
- PaperNumber
- Venue
- ExamDate
There are many changes to the overseas exams information. Students move overseas, withdraw from papers, move between venues, or require a date change.At peak exam time, extraction from the admin system and import into Accesshappens once a day, several days in a row.
Currently, a report is run and printed after the first import. The data from the two tables gets deleted before any import/update of information. Changes are manually written on the printed report(!!!). It is, as you can imagine, time consuming and prone to error.
I want to be able to display/highlight student/papers that have had changes- withdrawals, new students, venue changes and date changes.
I am unsure of the best way to handle this. I thought of moving the data into another table, running the import, then comparing the old data to the new. This would work ok except that there are multiple new imports, and I don't want to keep creating new tables. I'm toying with the idea of creating athird table that only keeps track of changes, but I'm not sure how that would work.
Any idea, suggestions, or examples would be greatly appreciated.
Thanks