Our forums are currently in maintenance mode and the ability to post is disabled. We will be back up and running as soon as possible. Thanks for your patience!



extracting data from access

By Router boy ·
I have a database that contains thousands of records and the records are sorted by provider and there are only 300 of these. For audit purposes I need to extract a random 5 record sampling from each provider. In the event the provider does not have 5 records it would pull what it does have. I am sure there is a way to get this accomplished but my sql skills are a little rusty. Any help would be greatly apprecciated.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Will this help you?.

From the Access help menu key on Export

Export a spreadsheet to a Microsoft Excel workbook
In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.

On the toolbar in the spreadsheet, click Export to Excel to run or switch to Excel and display the data in a workbook.

Because Excel supports only 256 columns, data in columns IW through ZZ is not exported from the spreadsheet. You can copy this data to an Excel worksheet.

When you export data from a spreadsheet to Excel, only the values and formulas are exported. Hyperlinks continue to work, but links that connect to other data on the Web page or other Web pages are not retained in Excel. For example, if you imported the data for the spreadsheet from a Web page or text file, when you export the data to Excel, no link is maintained to the Web page or text file.

When you export some spreadsheet number formats, the data in Excel is displayed with a custom number format. For example, the Yes/no, True/false, and On/off spreadsheet formats are displayed with custom number formats in Excel. For information about custom number formats in Excel, see Excel Help. Learn about spreadsheet number formats.

Please post back if you have any more problems or questions.

Collapse -

This doesnt quite work

by Router boy In reply to Will this help you?.

The DB table has 11 columns in the table and one of those columns will contain the variable it will be sorted by. The variable will be the servicing providers ID and there will only be 300 possible IDs. The data is sorted by this field.
What i am trying to do is extract 5 random entries under each provider ID for an audit. I am not quite sure how what you propose is accomplishing that.

Collapse -

Here you go

by Tony Hopkinson In reply to extracting data from acce ...

is how to select random records, your problem is a bit more complex.

This is ugly

Create one query to get distinct provider, so now you have all 300 of 'em

Create another to do the select top five order by random

No do a join on them by provider.

That should do the top 5 by random for each of them.

Don't like it at all, but it should work I think.

Related Discussions

Related Forums