Results 1 to 4 of 4
  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

    Using one Access table to select records from another Access table

    I have a large table that contains all the information about members of our association. There is one record for each member, and each member has a unique identifier called a Handle.

    Sometimes I want to extract the records, for example, of everybody who lives in either Washington, Idaho, or Montana. Another example would be that I have a list of the Handles of 100+ people, and I want to extract their records from the 4000+ that exist in my main table. Once I've extracted these records, I usually export them to Excel, then do with them as I please. (Often this is to put them into a mailing list of some kind.)

    I've been doing this by using the procedure outlined in the attached Word document. It works great, and has served me well since the early days of Access 2003. I'm wondering if there is a better or easier way to do what I need.

    Who can tell me?
    Attached Files Attached Files
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    It seems you are using a IN operator in your WHERE clause. I am not sure if this is causing any performance issues, but this is definitely not the best way to do it. Admitting both tables have the Handle column, the simplest way to this is to create a query that joins both tables on the Handle field (I am also admitting that the Handle field in both tables is indexed).

    To create such a query you just need to add both tables to the query designed and drag the Handle field from one table to the Handle field in the other table. Choose the columns you want, define other details like sort order and such and you're done.
    Rui
    -------
    R4

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

    Lou Sander (2014-11-27)

  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
    This looks good. Thank you. (By the way, I have used the method in the uploaded document dozens of times, and have never had any problems. It is a nuisance to do all that typing in the Criteria field, though.)

    I have used your method where selectiontable is a short list of state abbreviations. I dragged it to the State field in the main table. It worked, even though the latter field is not indexed. Both tables were very short, though.

    What are the implications of failing to index the fields?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  5. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    If there are no indexes in the fields, with a large number of records, the operation may take a bit more to complete. Access generally seems to handle those situations better than other databases, though and I wouldn't expect too big a problem with 4000 records.

    You can add indexes, though. It's just a matter of editing the table, clicking the field and choose Yes for the Indexed property.
    Rui
    -------
    R4

Posting Permissions

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