Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Nov 2006
    Location
    Phoenix, Arizona, USA
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create an Index (2007)

    I have a mid-sized table - 3,600 records, each with several fields. Each record has a plain-text Memo field and many of them are maxed-out to 65,000 characters. I use the table to store, access and manipulate information from books, large written reports, newspaper and magazine articles, etc.

    My question is: Is there a way to create an index, the kind used in books, for this table? It would need to contain information from two fields: the large Memo field and the ID number field. The Memo field would be the source of the "keywords" used in a traditional index. The ID number would serve the same purpose as a page number.

    The result I'm looking for would look something like this:

    New York
    321, 586, 587, 2002, 2346
    Thomas Jefferson
    18, 75, 1898, 3523

    I hope that makes sense.

    If there's no way to do this efficiently in Access, my Plan B is to create a Report from the table, export it to MSWord, and then have Word create a traditional Index from that.
    Thanks, in advance.

    PS If anyone knows of 3rd party software to index an Access table, I'd appreciate hearing about it.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create an Index (2007)

    You could create a table tblKeywords with 2 fields:KeywordID (AutoNumber, Primary key) and Keyword (text).
    Next create a query based on your data table and on tblKeywords.
    Add the ID and KeywordID fields to the query grid.
    Also add a calculated column N: InStr([MemoField], [Keyword])
    Clear the Show check box for this column, and set the criteria to >0.
    This query will return the IDs whose memo field contain a keyword corresponding to KeywordID.
    You can use this query for further manipulation.

    If this turns out to be too slow, you could use a table instead, and populate it using code.

  3. #3
    Star Lounger
    Join Date
    Nov 2006
    Location
    Phoenix, Arizona, USA
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create an Index (2007)

    Thanks, Hans.

    Doesn't the new table, tblKeyword, have three fields: ID, KeywordID and Keyword?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create an Index (2007)

    That's possible, but redundant. KeywordID is sufficient because you can look up the corresponding keyword in tblKeywords.

  5. #5
    Star Lounger
    Join Date
    Nov 2006
    Location
    Phoenix, Arizona, USA
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create an Index (2007)

    OK, thanks Hans!

Posting Permissions

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