I presently have an Access application to process student loan applications for multiple schools.
In recommended Access fashion it uses data separation, so the "program" is an Access DB on each user's PC, and the "data" is stored in multiple Access DB's on a server. Each school has their own DB, containing all students, etc. for that school. When the users switch from one school to another, the linked tables in the front-end DB are re-linked to the tables in the database belonging to the proper school.
When considering upgrading to a FileMaker Pro solution, I am thinking all school databases will be combined into one. In preperation for this, I've already added a School_ID field in all tables in all school databases. When we combine the individual DB's into one, this new field will keep data logically separated by school.
Here is the real challenge, and the reason for this post: In addition to the school database, each school also has a Federal Student Aid Database (an Access MDB), which is supplied by the federal government. Each database is unique to the particular school, and contains student aid data for that school only. In my Access solution, when I change from one school to another, I re-link to the proper school DB tables, and also to the proper Federal DB tables. No problems at all.
In the proposed FMP solution, I can't really combine Federal data from different schools in a single set of tables. The supplied Federal MDB's must remain unique and separate. I was hoping to use ESS, but that will not work with Access. Therefore, I'm stuck with some type of data importing. The Federal data will be read-only. I only need to import it occasionally.
The problem is, different users may be processing different schools at the same time. Therefore, I can't just import the Federal data from school A in case somebody else is working with school B or C.
I *think* the proper method would be proper to have an FMP Federal database file for each school, and script the import into the proper school's Federal FMP data database. Is this correct? Are there any suggestions about how to do this? I suppose when switching from one school to another, the script could test whether there is in fact a Federal FMP data file existing for that school, and if not, just create it. If so, I guess it would be proper to first delete all data and re-import all current new data, or simply delete the entire file and re-create it (but what about other users who may be "in" that school?).
I could really use some suggestions for the proper way to handle this situation.
Thanks in advance...