Database Design -MS Access

Hi
I am a bit new in database development field.I want to develop a

database to hold Treasury Bonds details using MS Access.The development

process should be based on an excel spreadsheet that contains Treasury

Bonds records categorized by duration ie 2yrs,5yrs,7yrs and 10yrs .My

concern here is how to design table(s) that will keep all those

records so that they can be well accessible and maintenable.In fact

,am not sure of how many tables should be developed,their

relationships and whether the normalization process should be in

place...Could you please help me in this.
PS.I have attached an excel file with sample data for your

consideration.

Would all fields normally be filled in, or do the rows that are filled in apply to multiple rows?

From what you have shown (assuming fields would be filled in), there isn't much potential for normalisation there.

Kenone:

I have to agree with si the geek in that there does not appear to be any way to normalize such a database. I have also always been of the opinion that Excel was superior to Access for financial analysis situations.

The only distinguishing feature of the information is the length of time; 2 yrs., 5 yrs., 7 yrs., and 10 yrs., and this isn't really sufficient to use for any kind of normalization. Quite the contrary, this information is very distinct.

I don't know what your purpose is for wanting to put the information into a database and it really doesn't matter, but I think if I were going to do so with this information I would create a database similar to the type you create for a Contacts Management Application. By that I mean, I would just put all of the information into a database with one table. I am not aware of any rule that requires a database to have more than one table.

If you make a separate table based upon length of time you will have to duplicate all of the other information in each table which would really defeat any database purpose, or at least I think it would.

If you have just one table you could then create SQL queries to retrieve whatever information you desired. Or search and/or index the table to give you whatever information you desire.

I think that I would include a unique identifying field for each item just in case, at some time in the future, I wanted to add a second table containing some other information that I wanted to be able to relate to the existing table.

I'm not sure this helps you a lot, but just my thoughts on your situation.

Good Luck

Thanks very much for your responses.
Ofcourse my big concern here is as follows:If you Refer to my previous attachment,you might notice that we have a sort of one to many relationship between a purchased Bond and interest payments....in which case ,one Bond have got multiple interest payments(ie dates and amounts) .I have desperately tried to handle these data in one table but I could not figure out how can I handle those blank spaces in MS Access table? (see the attachment).As a try and error I have put duplicate records in each column (to avoid blanks)but as you can expect,data management is a messy.Please advice me on how to accomodate them in one table without these duplicates or any way around.
Waiting to hear from you guys desperately.

It really depends whether you want a manual or an automated system. I've made a simple Access database which just links the table of interest payments to the table of bonds. Open tbl_bonds and expand the subdatasheet (via the cross to the left) to see the interest payments. You can then manually add or edit an interest payment via the subdatasheet or add or edit a bond via the main table. However actually all of the values in the tbl_interest table are calculated. The only significant data is in the main table. What you really need is a table which stores only the bond information, then a front end application which generates a table of interest payments from the data in the bonds table. Manually adding and editing data that should be calculated is inefficient and error prone. However it does do what you asked for.

Quote:

---

Originally Posted by anguswalker

It really depends whether you want a manual or an automated system. . What you really need is a table which stores only the bond information, then a front end application which generates a table of interest payments from the data in the bonds table.

---

Thanks very much anguswalker.
What I want is an automated system.I could get at least an idea from your second suggestion above.Well, I will have a table that holds bonds records only.My concern now will be on how to develop a front end application that generates a table of interest payments for the bonds table.....Could you advice me accordingly please???

kenone:

anguswalker gave you the basic layout for your Access database in the Bonds.zip file. I don't know what changes you may need to make to that database, but once you have the database designed as you want it and add the data to it, then you will need to decide what you want to use to display your information.

You could create your entire application in Access by making a form and then adding some of the controls to it such as text boxes, combo boxes, etc., to use to display the information from the database. I think you would want to add an MSHFlexGrid to your form to display your information. You would then use the properties window of the MSHFlexGrid to set the properties necessary to display whatever information you want to display.

Or you could use something like Visual Basic 6 or Visual Basic.Net to create a front-end that would then connect to your Access database and display the desired information.

I would personally use Visual Basic 6, only because I have used it a lot and know it fairly well. However, if you haven't done any Visual Basic programming of any kind then it would probably be better for you to learn Visual Basic.Net, since it is the latest version. There is also a cost factor involved if you do not have either version of Visual Basic. You could probably find VB6 for a lot less $$$ than Visual Basic.Net. But, if you create the entire application in Access you can use the version of Access that you have.

Do a search in Microsoft Access Help for "Access Projects" and you will find a lot of helpful information if you want to use Access.

Which ever way you go you are going to have to learn some programming, either Visual Basic for Application (VBA) to use with Access or VB6 or VB.Net to create the front-end to connect to your Access database.


Good Luck with your project.

Just for the hell of it I had a go at a bit of front end for you. The form that displays on startup has one record for each bond. When you create a new record and move on from it a little VBA routine will fill out a table of interest payment records for you. These are then stored in tbl_interest and linked back to the relevant bond. Clearly it needs work- I don't know the formula for calculating the interest payments- but it might get you started.

Thanks very much angus..your solution seemed to work fine..