Can this be done elegantly in Access SQL? Or I must use VBA?

I have a table with visits, SampleAllData, where each line is a visit, with one field being the Doctor’s unique identifier, NPI. I have another table, with NPIs, SampleNPIs. I am looking to find a random 10% sample of each provider’s visits from SampleNPIs, in SampleAllData. Each doctor can have 1 to 1000+ visits.

SELECT TOP 10 PERCENT Rnd(Len([Provider National Provider Identifier (NPI)])) AS RandomNumber, SampleAllData.[Provider National Provider Identifier (NPI)] FROM SampleAllData WHERE (SampleAllData.[Provider National Provider Identifier (NPI)])="23497695845";

The above correctly gives 10%of visits for 23497695845, which is what I need for a list of providers. I thought the query below would do it. But it doesn’t, it really returns 10% of the entire intersection of SampleNPIs and SampleAllData

SELECT TOP 10 PERCENT Rnd(Len([Provider National Provider Identifier (NPI)])) AS RandomNumber, SampleAllData.[Provider National Provider Identifier (NPI)] FROM SampleAllData WHERE (((SampleAllData.[Provider National Provider Identifier (NPI)])=(SELECT Distinct SampleNPIs.[S-Provider National Provider Identifier (NPI)] FROM SampleNPIs WHERE (((SampleNPIs.[S-Provider National Provider Identifier (NPI)])=[Provider National Provider Identifier (NPI)]))))) ORDER BY SampleAllData.[Provider National Provider Identifier (NPI)];

So is this even possible in SQL, or I need to add VBA and loops? Thank you..