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

Ask a Question

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.