First of all, thank you for helping me with my other queries. In recent weeks I have learned a lot by just reading this forum. I'm still a total newbie when it comes to access and, as you can imagine, my knowledge is very fragmented. I have already described the situation a few weeks ago in another thread but thought I'd start a new one as now I've got a draft database and some specific questions. To quickly recap:
Our parent company runs a "proper" database and provides us with monthly extracts/snapshots (a set of spreadsheets). As there's not much flexibility in terms of reporting, we decided to set up our local access database that will be fed the spreadsheets and we'll add additional information / queries (not present in the main database).
In the attached database, the tables based on imported spreadsheets are:
P01-Learners
P01-Enrolments
P02-Learners
P02-Enrolments
These are Period 1 (P01), Period 2 (P02) = Month 1, Month 2 and this is the type of data that we are given every month/period.
The tables called: Latest-Learners and Latest-Enrolments in the db are the same as P01 tables. The difference between P01 and P02 are new records or changes in some values. This would have been easier if it was only new records but the existing records get updated as well, which makes it more complicated.
All the queries/forms, etc are based on Latest-Learner and Latest-Enrolments (which is identical with P01 tables at the moment). Now, imagine the situation is that we've just received P02 reports. Is it just a question of deleting existing Latest-Learners and Latest-Enrolments tables and importing P02 tables calling them Latest-Learners and Latest-Enrolments?
There's most probably tens of other things that are wrong with that database in terms of the general design/concept. Please advise and any improvements/suggestions/solutions.
----------------------------------------
Additional issues:
Tables: Fees and AllocatedTeachers are created from scratch by me. The Append query will be run every month to add any new enrolments and then update the Fees/Teachers information via one of the forms. These are the only data entry tasks we'll be doing in our local database. The rest of for reporting purposes
P01, P02 - I still need to have individual tables for each period (not just the latest one) as I'll be running some reports on trends over time.
Thank you very much.
Our parent company runs a "proper" database and provides us with monthly extracts/snapshots (a set of spreadsheets). As there's not much flexibility in terms of reporting, we decided to set up our local access database that will be fed the spreadsheets and we'll add additional information / queries (not present in the main database).
In the attached database, the tables based on imported spreadsheets are:
P01-Learners
P01-Enrolments
P02-Learners
P02-Enrolments
These are Period 1 (P01), Period 2 (P02) = Month 1, Month 2 and this is the type of data that we are given every month/period.
The tables called: Latest-Learners and Latest-Enrolments in the db are the same as P01 tables. The difference between P01 and P02 are new records or changes in some values. This would have been easier if it was only new records but the existing records get updated as well, which makes it more complicated.
All the queries/forms, etc are based on Latest-Learner and Latest-Enrolments (which is identical with P01 tables at the moment). Now, imagine the situation is that we've just received P02 reports. Is it just a question of deleting existing Latest-Learners and Latest-Enrolments tables and importing P02 tables calling them Latest-Learners and Latest-Enrolments?
There's most probably tens of other things that are wrong with that database in terms of the general design/concept. Please advise and any improvements/suggestions/solutions.
----------------------------------------
Additional issues:
Tables: Fees and AllocatedTeachers are created from scratch by me. The Append query will be run every month to add any new enrolments and then update the Fees/Teachers information via one of the forms. These are the only data entry tasks we'll be doing in our local database. The rest of for reporting purposes
P01, P02 - I still need to have individual tables for each period (not just the latest one) as I'll be running some reports on trends over time.
Thank you very much.