Results 1 to 4 of 4
  1. #1
    DonB is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    1

    Use data in one lookup to take you to another lookup table

    I know the title sounds crazy but best i could come up with on spare of moment. Problem is with a financial database I am building for family use. My form will input to a transaction table but the list of possible accounts was so numerous i SPLIT THE ACCOUNT CODES IN TABLES FOR "INPUT", Reserve" etc. I want to specify the type of account I am looking for, then go to that lookup table to find the account to deposit or deduct from. I could do it if all codes were in one lookup, but that won't work well especially for reporting where you want only one type of account.
    Is there a simple way, or do i need to get up close and personal with VBA?



    Thanks

    B T W iused to work quite bit with access when it was fairly new but not for decades now and holy cow the improvements?

    Thanks again

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure I get that (maybe it's the terminology). You don't post data to lookup tables beyond the initial static data you want to "look up". Sure, you add additional lookup values, but this isn't something you do on a regular basis. Then there is the "possible accounts was so numerous". How many is that - 100,000? Even that should not matter. Those and the fact that you now have multiple tables for the same type of entity suggests you have lack of normalization issues. VBA will not solve design flaws, if that's where you are at.

    Lookup tables are typically for types of things (entities) wherein their primary use is to restrict the input of types in a record to those that are in the table.
    At the minimum you might need tables for accounts, types of accounts and one for transactions (unless you also want one for transaction types). The concept is to relate all of those as pairs by having properly designed common fields between them.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Sounds like cascading comboboxes could be useful. So have one table of Accounts with fields for AccountName and AccountType (Expense, Income, Asset, Liability, etc.)

    First combobox selects Type and code applies filter to Accounts combobox to only show accounts of that type.

    Very common topic.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I agree with using cascading combos for this.
    I have an example using that approach at http://www.mendipdatasystems.co.uk/c...xes/4594455723
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 3
    Last Post: 06-02-2017, 03:49 PM
  2. Replies: 15
    Last Post: 11-11-2016, 10:41 AM
  3. Replies: 3
    Last Post: 05-08-2015, 11:08 AM
  4. Accessing Data from a Lookup Table
    By TastyMouse in forum Queries
    Replies: 3
    Last Post: 05-08-2013, 11:26 PM
  5. Replies: 3
    Last Post: 10-16-2012, 10:34 AM

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