Questions

extracting data from access

Tags:
+
0 Votes
Locked

extracting data from access

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.
  • +
    0 Votes

    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.
    Notes

    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.

    +
    0 Votes
    Router boy

    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.

    +
    0 Votes
    Tony Hopkinson

    http://support.microsoft.com/kb/287682

    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.

  • +
    0 Votes

    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.
    Notes

    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.

    +
    0 Votes
    Router boy

    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.

    +
    0 Votes
    Tony Hopkinson

    http://support.microsoft.com/kb/287682

    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.