I'm fairly new to Access, and am building my first database, so am seeking some guidance in building this database.
Background: My team is responsible for tracking Training Certifications for select resources in our organization. I work for an enterprise corporation of nearly 200,000 resources, but will only need to report on North American resources (roughly 50,000 resources). We need to keep records of resources who obtain certification, and also need to keep track of resources who are not certified.
I need to create a "master file" that shows all resources in NA, and allows me to update resource records as they obtain certification.
We obtain 2 sets of data from HR, 1 excel file contains a list of resources and their personal information (ID, Name, Email, Location, etc.), but their are a subset of resources that are NOT captured on this file, which is why we have to use a 2nd excel file that contains the missing resources from the first, however there are duplicate records of resources that are captured on the first file. It is also worth mentioning that the columns do not match in both data sources.
How I am envisioning the database is as follows:
Table 1 - Resource List 1
Table 2 - Resource List 2
Query to combine the Tables 1 & 2 together, and removing duplicates.... not sure what is the best way to do this.
Table 3 - Certification Records
Query to join Certification Records to the Resources Query above
Form - which allows the user to search for Resources by Name or ID# from the Resources Query, and then fill in the fields for the Certification Records table.
Please provide any guidance in designing this database, it is much appreciated.