Extract a percentage of data

[Trevor Howard]

Hello,

I hope you could advise. Is it possible to only show a percentage, like 5% of records using a query? Ideally i would like my query to show a random 5% sample from a table, that would be great.
Is this sort of query possible?

 

[pr2-eugin]

In VBA I should say yes !

Get the total count of records in the table, then use basic Match to calculate the rounded figure to 5%, then use that obtained number into the SELECT TOP value.. Finally use the DoCmd.TransferSpreadsheet method to export !

 

[Trevor Howard]

Thanks pr2-eugin.

I thought it might be a bit tricky with a query. This is for monthly audits sampling a small number of studies, so really needs to be off a query so it can be dynamic rather than exported.
Thanks again for your advice.

 

[pr2-eugin]

Maybe I misunderstood when you said "extract" data, well you still can do this.. Create a Normal Query that will include whatever fields you need.. Then using the QueryDef, you can edit the SQL in RunTime, or Create dynamic Queries and still achieve what you are after.. Is this something you might be interested?