Results 1 to 7 of 7
  1. #1
    konfis is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    3

    Categorising based on contents of text-field

    Hey folks,
    I would like to build a database and reporting tool to:


    1) gather all my account Statements from different banks,
    2) assign categories to each record set based on a text-field. For this I assume the best solution is a keyword and categories list in a separate table (1:n-relation, one categorie can have many keywords).
    3) produce different reports

    As for the Status of the 3 Points:
    1) I accomplished to automatically import *.csv-statements and manipulate each recordset during importing. Is here already the best point to assign the categories? What happens if I change the categories afterwards?
    2) to assign the categories I thought of putting the keywords and categories in one or more arrays. For each text field I would cycle through the array. I guess there is a better solution you can think of? Please help me out here?
    3) That is something for the future, where I definitely need to educate myself before asking stupid questions.

    I would be really thankful if someone could point a direction how the category assigning is best done in MSAccess.

    Thanks so much alreay! Konfis

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    not clear on your description but I would expect you to have several tables along these lines

    tblBanks
    BankPK
    BankName
    BankAddress

    tblCategories
    CategoryPK
    CategoryDesc

    tblTransactionTypes
    TranTypePK
    TranTypeDesc

    tblAccounts
    AccountPK
    AccountNumber
    SortCode
    AccountName
    BankFK
    CategoryFK

    tblTransactions
    TransactionPK
    AccountFK
    TranTypeFK
    TranDate
    TranDescription
    Tranmount


    The PK's are the parents and link to the equivalent FK's as children

    In your form for tblAccounts you would use a combobox for the CategoryFK field which has a rowsource listing all the categories that the user can choose from.

  3. #3
    konfis is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    3
    Dear Ajax,

    thank you very much for your quick reply.
    If I understand your table set up correctly the accounts are currently categorised, right? However, the transactions itself should be assigned a category based on the contents of the text-field TranDescription. During import the VBA-Code should search in the table tblCategories for keywords and if found assign the appropriate category and write it in the field [tblTransactions].[CategoryFK]. If no keyword is found the user should follow your road and provide the correct category assginment manually.


    I guess the table set up should be like the following then:

    tblBanks
    BankPK
    BankName
    BankAddress

    tblCategories
    CategoryPK
    CategoryDesc

    tblTransactionTypes
    TranTypePK
    TranTypeDesc

    tblAccounts
    AccountPK
    AccountNumber
    SortCode
    AccountName
    BankFK
    CategoryFK

    tblTransactions
    TransactionPK
    AccountFK
    TranTypeFK
    TranDate
    TranDescription
    Tranmount
    CategoryFK

    Any efficient ideas?

    Thank you in advance,
    best regards,
    Konfis

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    konfis,

    You may get some ideas from this thread that relates to database design, modeling and testing the model before building the final database.

    Good luck.

  5. #5
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    if the category relates to the transaction then that is where you would put the CategoryFK -it might or might not replace the description

    With regards importing, yes, search the category field to find the right PK. However how well this works very much depends on the quality and consistency of your imported data

  6. #6
    konfis is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    3
    thanks organe,
    I guess I will spend some time on the videos mentioned in the linked thread.

    @Ajax: Would the search for a keyword within the text-field TranDescription would be VBA search, i.e.
    1) Put keywords/category in array
    2) search TranDescription of each record set for keyword
    3) if found assign the appropriate category

    or can you think of some built in functions?

    Thanks and kind regards,
    Konfis

  7. #7
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    no built in functions as such but if your category control is a combobox you could set the rowsource to something like

    Code:
    SELECT *
    FROM tblCategories
    WHERE [TranDesc] Like '*' & CategoryDesc & '*'
    [TranDesc] would be the name of your description control on the form and it will list all the categories where there is a match so the user has a limited choice. You would also need some code to set the value if the user does not specify one

    Problem would be if there are no matches for the user to choose from or the categories selecte are wrong, so you would need some code in the notinlist event so user can add a new one or to expand the list. All depends on what your descriptions and categories are

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 01-14-2015, 03:46 PM
  2. Replies: 1
    Last Post: 03-27-2014, 06:42 AM
  3. Replies: 0
    Last Post: 03-29-2011, 04:11 PM
  4. Replies: 2
    Last Post: 02-25-2011, 03:56 PM
  5. Replies: 2
    Last Post: 05-05-2010, 02:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums