I am trying to develop a query for average Bond ratings in a portfolio. I believe the best way to do this this would be to assign each bond value a number value, then calculate the aggregate to then have it spit out an average value. I would want the average not to include "nr". Also there will be some reconrds that will have null (blank) records and I would like the query to ignore these in both of these queiries. the field name is called "S&P Rating" The bond rating would be numerically assigned as follows:
Also, in addition to an average value I would like to it produce data similar to the below. The "A" will aggregate all of the types of A bonds, The "BBB" will include the BBB. BBB+, and BBB- and so on.
Any help here would be greatly appreciate! Please let me know if you need additional details
You can then assign your individual bonds a "bond_type_rating_id" which would give you the bond type by way of the bond_type_id, the plain-english representation (AAA, AA+, etc) by way of the description field in the BondTypeRating table, as well as the relative weight by way of the rank field.
This is currently part of a much larger project that we are trying to convert from Excel to Access, so ideally we would like to convert this part to Access as well. Also because the data is pretty expansive.
The idea here is to break up the logical chunks of data in to their own tables, then use relationships to hook everything together. This is what Access is really good at. Excel is a good weapon of choice if you're dealing with a "flat" table like you're describing above. However, approaching Access with the same mindset almost invariably results in unstable, brittle, poorly performing and excruciatingly-painful-to-maintain-and/or-extend solutions.
The values you're trying to assign have a place (hooked up to Rank in BondTypeRating), but I can't caution you strongly enough against trying to put all of this in the same table.
I don't think so. Granted I know very little about exactly what you're working with...
Assuming you have some spreadsheets or text files or some other kind of flat datasource that you're trying to import, you can key off of the "description" field in "BondTypeRating" to properly tag each bond you need to import with the correct rating.
I don't have the time right at this very second to whip up an example, but the rough idea is you create a staging table that mirrors all the fields you want to pull in from your raw datasource. You then look at this table to see if it has any new bonds/what-have-you that need to be imported based on the best identifying information you have available. In this case I'd imagine you get legal bond names or symbols that are fairly reliable.
After you've imported any new bonds, you can then look at the "S&P Rating" field and match it up to the description out of BondTypeRating. That will give you a bond_type_rating_id which you could then use to hook up the rating to a given bond. If you only want to store the current rating for each bond, you could put a bond_type_rating_id on your main Bond table. If you want to store a history of ratings, then you can create a many-to-many table between Bond and BondTypeRating that would track what rating a given bond had at what point in time.
Once you've done this, then you get all the reporting and querying benefits of properly normalized bond rating info.