Design Confusion!

Discussion in 'Access Table Design' started by Hatman50, Oct 27, 2010.

  1. Hatman50

    Hatman50 Guest

    I have a very confusing scenario (at least it is for me) - scenario
    follows:

    I have three tables as follows;

    tblSECURITY (as in financial stock)
    SecurityID (PK Autonumber)
    SecurityName (Text)
    Etc…

    tblACCOUNTS
    AccountID (PK Autonumber)
    AccountNo (Number - 9 digits)
    GeoCode (Number - 3 digits)
    Etc…

    tblCONSTRAINTS
    ConstraintID (PK Autonumber)
    Constraint (Text)
    Prefix (Number - 3 digits)


    Here is a brief scenario;
    Accounts are held by Canadian people NOT residing in Canada, and are
    in either Canadian Currency or U.S. Currency. The account table also
    has a GeoCode that designates where the people holding the account
    actually reside.
    Based on government rules, Constraints have to be placed against
    Securities for specific accounts. In other words certain accounts can
    not hold these securities. I want to keep track of what accounts have
    what constraints put on them.
    The constraints consist of certain kinds of Accounts; US dollar accts,
    Canadian dollar accts, Registered accts, Non Registered accts
    These types of accounts are designated by a 3 digit prefix on the
    account number.
    i.e. 530-xxxxx is a Canadian dollar account
    531-xxxxx is a U.S. dollar account
    etc…

    So as an example:
    Security “ABC” can not be held by Canadian Or U.S. Dollar Accounts
    therefore any account that starts with 530 OR 531 must not hold “ABC”
    Currently my tblConstriants consists of 4 records
    CONSTRAINTID CONSTRAINT PREFIX
    1 Cdn Accounts 530
    2 U.S. Accounts 531
    3 Registered 559
    4 Non-registered 561

    Within a form, Upon selecting a Security & the said Constraints, i
    would do a search on the tblAccounts looking for 530* or 531* and add
    each of the associated AccountID's to a forth table as follows.
    I am planning to set up a forth table to hold the ScerurityID,
    ConstraintID, and AccountID.
    So for this example I would have a table with
    SecurityID ConstraintID AccountID
    1 1 55
    1 2 55
    1 1 56
    1 2 56

    Is this correct? Am I on the right path.
    My apologies if the explanation seems convoluted.
    Thank you very much for TRYING to understand my scenario!
    Regards
    BJ
     
    Hatman50, Oct 27, 2010
    #1
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Jackson

    Icon Confusion

    Jackson, Nov 14, 2003, in forum: Access Table Design
    Replies:
    1
    Views:
    71
    Joan Wild
    Nov 14, 2003
  2. Lee Kennedy

    Many to many relationship confusion

    Lee Kennedy, May 23, 2004, in forum: Access Table Design
    Replies:
    4
    Views:
    116
    Lee Kennedy
    May 24, 2004
  3. maarkr

    Table setup confusion

    maarkr, Jul 12, 2004, in forum: Access Table Design
    Replies:
    3
    Views:
    85
    Jeff Boyce
    Jul 13, 2004
  4. Dabaum

    Table Design Confusion

    Dabaum, Oct 9, 2006, in forum: Access Table Design
    Replies:
    4
    Views:
    145
    Dabaum
    Oct 10, 2006
  5. Ren

    Combo Box Confusion!!

    Ren, Mar 1, 2007, in forum: Access Table Design
    Replies:
    1
    Views:
    100
    Amy Blankenship
    Mar 2, 2007
  6. dee

    Naming Conventions Confusion

    dee, Jul 31, 2007, in forum: Access Table Design
    Replies:
    16
    Views:
    258
    Maurice
    Aug 2, 2007
  7. jenniferspnc

    Multiple Tables Confusion

    jenniferspnc, May 8, 2008, in forum: Access Table Design
    Replies:
    11
    Views:
    169
  8. Mr C
    Replies:
    5
    Views:
    137
Loading...