How to update to most recent set of data (1 Viewer)

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).


Have you considered expressing to your "parent company" your requirements for reporting? Duplicating a real database and concocting a local version with additional info can lead to various errors. Synchronization is the most common issue --everyone claims their data is accurate, BUT no one knows for sure.
From experience identify and agree upon the authoritative source; then build your local database to always get its latest data from that authoritative source;then adjust your local files/tables for reporting (if necessary). Finger pointing rarely resolves a design flaw. Take time to design and vet your processes and data - parent, local, refresh and reporting.
I still recommend that the parent provides you with the data you need to do your job. Local synched tables are a last resort.
Good luck.

jdraw said:

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).


Have you considered expressing to your "parent company" your requirements for reporting? Duplicating a real database and concocting a local version with additional info can lead to various errors. Synchronization is the most common issue --everyone claims their data is accurate, BUT no one knows for sure.
From experience identify and agree upon the authoritative source; then build your local database to always get its latest data from that authoritative source;then adjust your local files/tables for reporting (if necessary). Finger pointing rarely resolves a design flaw. Take time to design and vet your processes and data - parent, local, refresh and reporting.
I still recommend that the parent provides you with the data you need to do your job. Local synched tables are a last resort.
Good luck.

Click to expand...

Thank you for your response. I share the concerns you mentioned, unfortunately, this has already been explored and sadly that's where we are. The SLA does not cover some of the information that need to be inputted locally. We are not going to change ANY of the data provided to us, only complement it so there's no question of having two different sets of data. There are a lot of ad hoc reports to support staff members that (due to the need for timely response) are better done locally.

Have to make this short: didn't look at your posted db but your situation is not all that uncommon IMHO. Where I worked, there was a copy of a huge production db that those with permissions were granted access to, but forget about asking for any changes to it. So everyone mined the data with Access for their own purposes, and sometimes this involved local tables populated with what was otherwise read-only data. Each day, queries appended new records based on criteria but they also updated from the same source. You could do the same against local tables using spreadsheets as the source. Whatever hasn't changed doesn't get edited by update query. The append query was just that. Anything that would violate uniqueness was ignored in the process.

Yes I agree with Micron's comments.
I have seen users creating local complementary databases and using them in a variety of ways-- usually undisciplined. Set up some guidelines and procedures for the management of the local database(s) and data changes -- and monitor well.

Good luck with your project.

Thanks. Hopefully, it'll all work out fine.

Would really appreciate some comments on the actual db file. Being a new Access user, not sure if I'm going in the right direction (with what dataset I have been given).


Will need to read more on update queries. Can they spot the differences between two tables?

to Update and at the same time Add new record you need to use Right join on your Query:

If the periodic downloads are complete, in that they contain data about all the people you need to report on, then the best solution is a complete replacement each time you get a new file. I'm sure there is a unique ID in the download somewhere and you would use that as the PK for your additional data table. Your additional table will contain ONLY the unique ID from the download plus whatever additional data you need.

The question then becomes - what happens to people when they are no longer enrolled in a program? Do they remain on the file but just have no active enrollment? If so, you're good to go. If not, then, you will need to clean up your table since it will end up containing orphans.

Would really appreciate some comments on the actual db file

Click to expand...

Not much to go on so IMHO, comments can only be in general terms. If the sheets are guaranteed to be consistent in layout, that will help a lot. Whether or not you do a complete replacement of the sheets as seems to have been suggested, might depend on whether or not it is best that you repeatedly import as tables or link as tables. In the latter case the sheet name would have to remain the same. It seems the plan so far is to have a table for every period, but if you're going to have local tables that you populate/edit from sheets, that sounds un-normalized.