question

MishaAM avatar image
MishaAM asked

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

Hello. 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% of each provider’s visits in 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)];
random-tag3
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.