I have a table as a sample (AtlasSample_2020901) of practitioners that needs to be audited. Each practitioner has an Attestation ID. The problem I am having is that table I need to use to pull the audit data has assigned the Attestation ID to all the practitioners in one practice. For example Dr. Smith works at ABC Urgent Care and Dr Jones works at XYZ Hospital. I want to pull back data based on the Attestation ID for Dr. Smith and Dr. Jones. What I am getting back instead is data for Dr.Smith and all the other practitioners that work at ABC Urgent Care and info for Dr Jones and all other practitioners that work at XYZ Hospital. How can I set up a unique key based on LastName (this is the only common fields I both tables) and then import data into a new table with just the records that have a unique ID

Here is qry that brings back too any records:

SELECT DISTINCTROW AtlasSample_20200901.ProviderAttestID, PracticeInformation.PracticeName, PracticeInformation.Address, PracticeInformation.Address2, PracticeInformation.City, PracticeInformation.State, PracticeInformation.Zip, PracticeInformation.PhoneNumber, PracticeInformation.FaxNumber, PracticeInformation.EmailAddress, ProviderReference.FirstName, ProviderReference.LastName, ProviderReference.Title, ProviderReference.MiddleName, ProviderReference.EmailAddress, ProviderReference.Specialty_SpecialtyName, ProviderReference.Degree_DegreeAbbreviation, PracticePatientType.PatientType_PatientTypeDescrip tion INTO InfoToCompare
FROM (((((AtlasSample_20200901 INNER JOIN ProviderSpecialty ON AtlasSample_20200901.ProviderAttestID = ProviderSpecialty.ProviderAttestID) INNER JOIN PracticeOtherAddress ON AtlasSample_20200901.ProviderAttestID = PracticeOtherAddress.ProviderAttestID) INNER JOIN PracticeInformation ON AtlasSample_20200901.ProviderAttestID = PracticeInformation.ProviderAttestID) INNER JOIN ProviderReference ON AtlasSample_20200901.ProviderAttestID = ProviderReference.ProviderAttestID) INNER JOIN PracticePatientType ON AtlasSample_20200901.ProviderAttestID = PracticePatientType.ProviderAttestID) INNER JOIN ProviderInfo ON AtlasSample_20200901.[CAQH ID] = ProviderInfo.ProviderID;

This is a far as I got I have another table with a unique field called CAQHID, when I run this only the providers I need show up but I still need to pull in the above data from all those tables!
Access newbie!

SELECT AtlasSample_20200901.ID, AtlasSample_20200901.PROVMEMB_ID, AtlasSample_20200901.[CAQH ID], AtlasSample_20200901.ProviderAttestID, AtlasSample_20200901.NAME, AtlasSample_20200901.LASTNAME, AtlasSample_20200901.FIRSTNAME, AtlasSample_20200901.DEGREE, AtlasSample_20200901.PersonalInfo_Changed, AtlasSample_20200901.Location_Changed, AtlasSample_20200901.Supplemental_Changed, AtlasSample_20200901.New_Location, AtlasSample_20200901.NoChanges, AtlasSample_20200901.Selected
FROM ProviderInfo_20200901 INNER JOIN AtlasSample_20200901 ON ProviderInfo_20200901.[CAQH ID] = AtlasSample_20200901.[CAQH ID];