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
|
Similar Threads
