Financial access points in Mexico
To exemplify the usage of Microsoft Access in the light of database management, a set of data related with financial inclusion has been chosen. For certain analysis, it is important to know, for each municipality (there are 2,456 municipalities in Mexico), state, or region, the number of access points that it has. Two periods of time have been chosen: 2012 and 2013.
The relationships between the table with the main financial data (branches, ATMs, POS, correspondent banking agents) called "Financial info 2012-2013" and the other "catalog" tables are presented in Figure 1. Fields such as "Municipality ID", "Area (km2)", and "Population" are numbers, while other fields such as "Region", "State" and "Municipality" are text. We can interpret from the diagram (and from opening each table) that the "Municipalities and Population" table has the name of the municipalities once, while the "Financial Info 2012-2013" repeats the name of each municipality twice (once per year).
Additionally, the latter one repeats the field "State" more than twice (because a state has several municipalities), so it's only natural that there is also a link between the "Municipalities and Population" table and the "States" one, which only has the name of the Mexican States and their information once.
Figure 1. Relationship between tables.
Afterwards, a report was generated such that, for each state and year, it computed the total financial branches (Commercial banking, Development banking and Microfinance banking).
For this purpose a query was used (Access enables us to do it with a Wizard, but SQL language is actually used) to achieve the task. The purpose of a query is virtually answering "questions" about your data. The SQL language that would generate this report is:
This is, we obtain the "Total branches" for each...