creating and relating tables

[Guest]

I'm fairly new to Access and would like some help with creating and relating
tables in my database. I'm creating a database with the following in mind:

I have a table with a bunch of securities in it and each security can have
an Issuer, Servicer, or SyntheticAssetCP or any combination thereof. Each
Issuer, Servicer, or SyntheticAssetCP can be rated by SP, Moodys or Fitch or
any combination thereof. Each SP, Moodys or Fitch rates the Issuer, Servicer,
and Synthetic Asset CP with the same fields (i.e. all three agencies give the
security a long term rating, a short term rating, etc etc). Sometimes they
won't give a rating in one of the fields.

I'm struggling to figure out how to relate these all together. Any help
would be much appreciated. Thanks!

 

[jahoobob via AccessMonster.com]

I would have the following tables with at least the fields listed:

tblSecurities
SecuritiesID - Autonumber Primary key
Issuer - Long Integer data type
Servicer - Long Integer data type
SyntheticAssetCP - Long Integer data type


tblSAS
SASID - Autonumber Primary key
SASType
LongTermRating
ShortTermRating
Rater - Long Integer

tblRaters
RaterID - Autonumber Primary key
RatingCompany

I would have a form, frmSAS, to enter/edit each Issuer, Servicer, etc. and
their ratings and a combo box, backed by tblRaters, that would capture the
RaterID to store in Rater (in tblSAS.)
My Securities form would have all the securities info and three combo boxes
based on three queries of tblSAS that provided the three different SASTypes.
This way, if a Servicer's rating changes and that Servicer is associated with
100 different securities, you would have to only change the Servicer's rating
in one place to have it show with all 100 securities.
Also, if say a Servicer was a company, XYZ, and changed its name to ABC, you
would have to change it in only one place also.

Are relational databases great or what?
Hope this helps,
Bob

 

[Guest]

Thanks! Let me clarify...

The Rater field in the tblSAS is a FK to tblRaters right? and when you say
the combo box on the frmSAS would "capture" the RaterID, who do I do that? I
assume it would display the name of the rater, but actually store the RaterID
number?

How would I set up the three queries of tblSAS that you mention for the
combo boxes in the securities form? Wouldn't that just be three options:
Issuer, Servicer, or SyntheticAssetCP? Perhaps I wasn't clear in specifying
that many of the securities will have different issuers, different servicers
and different synthetic asset CP, though in some instances some securities
may have the same issuer, or servicer, or synthetic asset cp. I'm not sure I
see a field to specify the name of the Issuer, Servicer, or Synthetic Asset
CP in your table structure. I'm assuming that SASType is to specify whether
we're talking about an Issuer, Servicer, or SyntheticAssetCP, right? Thanks
so much for the help. I am, indeed, starting to see how great relational db's
are!

 

[jahoobob via AccessMonster.com]

The Rater field in tblSAS is a FK to the RaterID field in tblRaters.

The combo box on the frmSAS would have as its record soirce, Rater (see above
for the why.)

The query for Issuer would be:
SELECT tblSAS.SASID,tblSAS.SASName,tblRater.RatingCompany
FROM tblSAS LEFT JOIN tblRater ON tblSAS.Rater = tblRater.RaterID
WHERE ((tblSAS.SASType)="Issuer";
Substitute the other types for Issuer for the other two queries. This should
answer your question about SASType. As you see, you don't store the
RatingCompany in tblSecurites.

I hope this answers your further questions. If not thenask again,
Bob

Thanks! Let me clarify...

The Rater field in the tblSAS is a FK to tblRaters right? and when you say
the combo box on the frmSAS would "capture" the RaterID, who do I do that? I
assume it would display the name of the rater, but actually store the RaterID
number?

How would I set up the three queries of tblSAS that you mention for the
combo boxes in the securities form? Wouldn't that just be three options:
Issuer, Servicer, or SyntheticAssetCP? Perhaps I wasn't clear in specifying
that many of the securities will have different issuers, different servicers
and different synthetic asset CP, though in some instances some securities
may have the same issuer, or servicer, or synthetic asset cp. I'm not sure I
see a field to specify the name of the Issuer, Servicer, or Synthetic Asset
CP in your table structure. I'm assuming that SASType is to specify whether
we're talking about an Issuer, Servicer, or SyntheticAssetCP, right? Thanks
so much for the help. I am, indeed, starting to see how great relational db's
are!

I would have the following tables with at least the fields listed:

[quoted text clipped - 43 lines]