Hello,
I am in the prcoess of creating an automated report in MS Access that will keep track of my organization's employee expenses that pertain to external clients.
In order to do this I have begun creating a tables for storing all the data records in a CSV format. The data is retrieved from various systems that do not ncecessarily contain the same field names. Each data provider generates a report in a different layout but has some information that can be mapped or linked to the other reports. The common field that can link the data is the staff name and staff id. By using these two common fields I can create a new data base that will have the neccessary information to create a standard report. I was able to create a data base by merging / mapping the information from the different systems onto an excel worksheet. This was a manual and time consuming project.
The challenge I face is creating an automated report in MS Access. I need help with merging 20 tables into one table, and with mapping information from 4 additional tables into one. The 20 tables have the common fields (and may be merged into 1), but the 4 other tables have additional fields that I need but are not common to the fields in the other tables. The only common fields that all tables have are the employee names and their respective ID numbers.
The problem I face is the data mapping for each provider which are currently hundreds. I would like to know if there is a standard way to do this, or if there is a mapping template in MS Access already designed for this. Or should I create a master template table, which would have all the same column names / fields needed to create the the automated report. I was also wondering if the import functionality in MS access allows easy creation of import templates, and how easy it would be to maintain them.
I am in the prcoess of creating an automated report in MS Access that will keep track of my organization's employee expenses that pertain to external clients.
In order to do this I have begun creating a tables for storing all the data records in a CSV format. The data is retrieved from various systems that do not ncecessarily contain the same field names. Each data provider generates a report in a different layout but has some information that can be mapped or linked to the other reports. The common field that can link the data is the staff name and staff id. By using these two common fields I can create a new data base that will have the neccessary information to create a standard report. I was able to create a data base by merging / mapping the information from the different systems onto an excel worksheet. This was a manual and time consuming project.
The challenge I face is creating an automated report in MS Access. I need help with merging 20 tables into one table, and with mapping information from 4 additional tables into one. The 20 tables have the common fields (and may be merged into 1), but the 4 other tables have additional fields that I need but are not common to the fields in the other tables. The only common fields that all tables have are the employee names and their respective ID numbers.
The problem I face is the data mapping for each provider which are currently hundreds. I would like to know if there is a standard way to do this, or if there is a mapping template in MS Access already designed for this. Or should I create a master template table, which would have all the same column names / fields needed to create the the automated report. I was also wondering if the import functionality in MS access allows easy creation of import templates, and how easy it would be to maintain them.
