Using MS Access/Merge Unrelated tables
I'm trying to find a way to link two totally unrelated tables into one so I can use it as a "bridge" to pull info from each.
Without going into major detail, I have an employee table. Only each employee's contact information is stored in the Employee table, with a primary key auto number field. Each employee can work at multiple locations. However, they must be certified at each site individually, so they will have a different unique personal identifier at each site to track their certification processes (it's required). I am trying to build this database to track the employee certification status at each site.
The locations are stored in a location table that only gives contact information about the specific locations. It also uses an auto number field as the primary key.
Both sets of data have their own multiple respective sub-tables to maintain more specific information, but those are irrelevant to this question.
Employee A may work at sites 2,3 and 5. The employee would have a different unique identifier at each site to track their certification(s).
Employee B might only work at site 1 and have another unique ID.
Employee C may work at 1, 2, and 4, and would also have a unique ID at each site.
Also, as other sites are added (or removed) I need to be able to send employees there as well, with another unique ID. I don't know if a union query would work, as each person can work at any of the sites or only a few.
One other thing: an employee can lose their certification (or let it lapse) and then would later need to be re-certified at any site(s). The re-certification would use the SAME identifier as they previously used at the specific site, but the data from the original certification would need to be retained somehow.
I need to find a way to link each employee to all sites they actually are being certified at (one employee to either one or many sites) and utilize the Primary keys for each table. I was thinking of somehow building a linking table that I could use as a sort of bridge between the two master tables, but I can't figure that part out if there is no common information.
Once I get past this obstacle, I believe I can use the bridging table to access everything I need from each master table and their respective subsidiary tables.
Thanks for any help.
Thank-you for your patience, this week has been extraordinarily hectic.
I hope I have understood your question correctly. If so, I would set it up as can be found here:
<a href = "https://www.dropbox.com/s/o0fkpcdh1u3b27g/TableRelationships.pdf?dl=0" >
It was not clear if the unique id had to be specific (i.e. they are generated elsewhere and have to be used), I have presumed not, i.e. the database creates the unique ID.
One table each for:
Then a fourth table that brings these all together. 'Tracking' table
The EmployeeID is linked to the EmployeeFK (Foreign Key) same for LocationID > LocationFK and CertificateIS > CertificateFK.
I have used the combination of the EmployeeFK, LocationFK and CertificationFK (IDs) to make the unique ID for each employee in the tracking table.
I would then use the tracking table, to create a form, to add the employees, locations and certifications to. Each of the fields, EmployeeFK, LocationFK and CertificationFK would be drop down boxes.
Probably best to have as a continuous form, so you can easily check to see if you have entered the combination of Employee, Location and Certification previously. As per this example:
<a href = "https://www.dropbox.com/s/8npl6vcjc78q7sc/frmTrackerDesign.pdf?dl=0" >
<a href = "https://www.dropbox.com/s/3ot25pky92gok5v/frmTrackerView.pdf?dl=0" >
If you then needed to display the Unique ID you can combine the Primary Keys together i.e.
=[Employee] & "-" & [Location] & "-" & [Certificate]
Would display it as 1-2-1 for example. You can play around with the formatting to meet your needs.
And the database (sometimes there is an issue with downloading databases due to security and versioning, thus why I included the images).
<a href = "https://www.dropbox.com/s/wnjh6hxa4b95z6y/Database11.accdb?dl=0" >
I have presumed a bit of knowledge on your behalf. If you need greater explanation about anything then let me know.
Also this is unlikely to be the ideal answer, as I do not know your situation fully. Once again if anything needs to be tweaked. Let me know.