Dear all,
As I stated already in another post:
The Database I create is projected on another, significantly bigger database! The one I create is a kinda light version for other field offices with only the information they need to have, and additional tables for them to gather their own data and track their own activities. This being said, the light database needs to be updated every month once by the big (mother version). Both DBs cannot be connected!!!
So we will send to the field offices altogether 5 tables in one mdb-file every month. These 5 tables exist with the same structure ( name, field data-types, etc.) in the light version.
I now want to make an automatic update option (by pressing a key and select the "update-file"), i.e. based on the the respective Primary Key of every table the entry in the smaller DB should be updated!
Update for me means the following three things (Assume big DB is A, small DB is B):
1) If Primary Key (PK) exists in both tables of A and B, update the rest of the fields in B belonging to that key based on the specific record of A
2) If PK does not exist in A but in B, delete record from the database B (case: record was deleted in A after last update)
3) If PK does not exist in B but in A, add the record to database B (case: record was added to database A after last update)
I would like to show you what I already found in the internet on solving task 1) but I cannot post links due to my status
(One post from this form: "update one table using another" by kaiwalya1234;
And "Update Data by Using a Query" from the Access Help section of Microsoft)
Both links basically describe how to update one table by another in importing or linking the update-source table in the current DB, create a query with both tables, link the PKs of both and then using the Update To Criteria [UpdateSourceTable].[Field] for each field that I want to update.
But I wanted to ask before I implemented that if I should make a complete different approach since I also have 2) and 3) as tasks....
All insights, ideas, etc. are very very welcomed!!!! Just the actual way of updating the DB is not "discussable"
All the best,
Haakon
As I stated already in another post:
So now, I am almost done with forms (at least I want to believe that). But I ran into an issue I would love to have your comment and different approach on:I'm relatively new to MS Access (using MS Access 2013 but the db should work on 2010, too) and try to develop a database for an NGO I'm working in. [...].
However now I start to create forms and later reports for the actual user. The database will store information about clients and track consultations and assistance the NGO gives to them. There will be around 50.000 to 70.000 clients in the main table. Every client has a specific Individual ID and is member of a family which itself has another specific Group ID.
The Database I create is projected on another, significantly bigger database! The one I create is a kinda light version for other field offices with only the information they need to have, and additional tables for them to gather their own data and track their own activities. This being said, the light database needs to be updated every month once by the big (mother version). Both DBs cannot be connected!!!
So we will send to the field offices altogether 5 tables in one mdb-file every month. These 5 tables exist with the same structure ( name, field data-types, etc.) in the light version.
I now want to make an automatic update option (by pressing a key and select the "update-file"), i.e. based on the the respective Primary Key of every table the entry in the smaller DB should be updated!
Update for me means the following three things (Assume big DB is A, small DB is B):
1) If Primary Key (PK) exists in both tables of A and B, update the rest of the fields in B belonging to that key based on the specific record of A
2) If PK does not exist in A but in B, delete record from the database B (case: record was deleted in A after last update)
3) If PK does not exist in B but in A, add the record to database B (case: record was added to database A after last update)
I would like to show you what I already found in the internet on solving task 1) but I cannot post links due to my status
(One post from this form: "update one table using another" by kaiwalya1234;
And "Update Data by Using a Query" from the Access Help section of Microsoft)
Both links basically describe how to update one table by another in importing or linking the update-source table in the current DB, create a query with both tables, link the PKs of both and then using the Update To Criteria [UpdateSourceTable].[Field] for each field that I want to update.
But I wanted to ask before I implemented that if I should make a complete different approach since I also have 2) and 3) as tasks....
All insights, ideas, etc. are very very welcomed!!!! Just the actual way of updating the DB is not "discussable"
All the best,
Haakon
Last edited: