Hello,
I have an Access 2016 database with several join tables. Rather than manually updating these tables, I was wondering if anybody knows of code that I could use to automatically
do the updates.
Thank you for any help
Hello,
I have an Access 2016 database with several join tables. Rather than manually updating these tables, I was wondering if anybody knows of code that I could use to automatically
do the updates.
Thank you for any help
You haven't given us very much detail to go on (what your data looks, what will be populated, how the calculation is done), but possibily take a look at using Update Queries (there are lots of tutorials you can find on these with a Google Search).
If it is more complex, VBA might be required.
Note that if it is something that can be calculated based on the other fields, then you shouldn't update/store it in a table field - just do it in a query in a calculated field. That way it is dynamic and automatic, and you don't need to run anything any time you add or change data.
normally these would be populated automatically by defining relationships and using forms, so presume this is a one time 'catchup' exercise. Agree with Joe, not enough info to provide suggestions
Hello JoeM,
Thanks for the response, sorry about the lack of detail.
I have attached part of the ER diagram which should give you a better idea.
The CountriesStatusJoin table is updated using forms and the StatusMonetarySystemsJoin table is also updated using forms so these are not a problem.
My issue is with the CountriesMonetarySystemsJoin table, I need to manually key the Country_ID and Monetary_System_ID, is there a way to do this automatically or with code ?
Can a country have several monetary systems at same time moment? When not, then tbl_CountriesMonetarySystemsJoin in this design is pointless!
a) When a country has always a single monetary system, and it never does change, or only current system counts, add field CMSJ_Monetary_System_ID into tbl_Countries;
b) When a country has always a single monetary system, but may change and you must know, which system was applied at any time moment, then you need instead a table like tbl_CMS_History: CMSH_ID, CMSH_Country_ID, CMSH_Monetary_System_ID, ValidFrom;
c) Only when a country can have several monetary systems at once, you need your original table, but probably you need to change the design a bit. like tbl_CMS: CMS_ID, CMS_Country_ID, CMS_Monetary_System_ID, ValidFrom, Active.
Now about applying monetary system to country.
1. You need a single form, where user can select a country (fCountries);
2. You need a continuous form based either on tbl_CMS_History, or on tbl_CMS (e.g. fCMS). Controls linked to primary key, and country ID are better set hidden, the control linked with monetary system ID must be a combo which selects monetary system ID from tlbl_Monetary_Systems, and displays its name;
3. You insert continuous form into fCountries as subform (sfCMS). fCountries and sfCMS must be linked through country ID's in respective tables. Whenever you add a new record into subform, it will be linked to active country in fCountries.
When a), then all you need is a combo to select a monetary system ID in fCountries from monetary system ID (like explained in 2. but the form is different).
The case c) is most difficult one - to ensure all entries are made properly, and to read info.