Results 1 to 6 of 6
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Shortening a Test Table

    I have a 4000-record table I use for testing a database application. I'd like to cut it down to 500 records or so, to facilitate sending it to others.

    Is there some easy way to do that?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Lou,

    1. Create a Select query using Query Design from the Create tab.
    2. Switch to SQL View
    3. Type: SELECT Top 500 * FROM YourTableNameHere;
    4. Run the query to make sure it works.
    5. Return to SQL view.
    6. Click the Make Table icon on the ribbon.
    7. Fill in a name for the new table.
      SQLMakeTable.JPG
    8. Click OK.
    9. Click the Run icon on the ribbon.
    10. Click Yes in the prompt.
    11. Done.


    HTH
    Last edited by RetiredGeek; 2014-11-17 at 20:37.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Lou Sander (2014-11-25)

  4. #3
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Hmmm. I did what you asked, and it only worked part way. Details follow.

    I use a front end database to access data in TestData.accdb. It contains several tables, the main one of which is tblAllLocated. I copied that database and renamed the new one ShortTestData.accdb. Then I did the shortening procedure on tblAllLocated in ShortTestData.accdb

    The older table was named tblTestData.accdb. Access wouldn't let me save the new one with the same name, so I named it tblShortTestData.accdb. So far, so good.

    Then I deleted tblTestData and renamed tblShortTestData as tblTestData.

    Then I relinked my front end database from TestData.accdb to ShortTestData.accdb

    When I opened the front end, everything looked good, but my main form, frmMain, was accessing and displaying data from another table, tblAllHandles, which still contained records for everybody in the original TestData.accdb. The parts of the form that use data from tblAllLocated were, of course, blank for the records that were no longer in that table.

    So what I have is my main form showing me some of the information for the 3,000+ people who have been removed from tblAllLocated, as well as all of the information for the 500 that still remain there.

    I believe the solution to this is, in ShortTestData.accdb, to delete all records from tblAllHandles, except those that are also in tblAllLocated. (Every record in every table in the database has a Handle field, and all Handles are unique.)

    I suppose there is some sort of query that will do this. Also maybe it would work to sort the original tblAllLocated and tblAllHandles by Handle, then get the Top 500 from both of them. I'm thinking that the query method would be better, but I haven't the foggiest about how to do it. Do you have any suggestions?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  5. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    It appears you have a referential integrity issue with your database design. If it was constructed properly, it would not have let you delete the records in tblAlllLocated until you deleted records from tblAllHandles (and perhaps vice versa). If you enabled cascading deletes, that would clean up your database considerably. As it is, you need to create a query the selects all of the records in tblAllHandles that do not have a corresponding record in tblAllLodcatged. That involves using an outer join and a criteria of null for the primary key in tblAllLocated. And there is a query wizard which will help you do that.
    Wendell

  6. The Following User Says Thank You to WendellB For This Useful Post:

    Lou Sander (2014-11-25)

  7. #5
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Sorry, but that is too technical for me.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  8. #6
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Well, I've accomplished what I wanted to do, and everything seems to work.

    RetiredGeek's tip was essential to it all. What WendellB pointed out wasn't useful at first, but I broke out Access 2007 All-in-One Desk Reference for Dummies and brushed up on my basic Access knowledge. With the assistance of the book, I was able to understand his post, and to correct the underlying problems with my database.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •