Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2007
    Posts
    5

    Question Unanswered: Please help me figure this out how to do this...

    Hi,

    I'm creating a new database for an investment brokerage company. The company invests clients' funds for a specific length of time and pays out a return on their investment on a weekly basis for the duration of the investment.

    This is my first real database so I'm gonna need all the help I can get. The company has some pretty huge requirements but right now I'm focusing on phase 1 which will be very simple.

    Right now I'm working on the database design but there's been one thing which has been bugging me for a while so I figured I'd ask you guys to help me get my head around this.

    Let's say we have a table called Investments. This table will include data on the amount invested, the number of weeks invested for, and the ROI percentage. For example...

    Investment Record Example
    Amount: 10,000
    Weeks: 10 weeks
    Percentage: 10%

    One of the requirements of the database is to produce a report that will list each week of the cycle and the amount paid to the client. This will require calculations in order to produce this data. Here's an example...

    Payment Report Example
    Week # | Payment Date | Payment Amount | Notes
    1 | 1/1/01 | 100 | Enjoy your first payment! :-)

    I can see how this can be easily done by producing a query and then a printing a report based on it. However, here's the tricky bit... for various reasons, the payment date or the payment amount for a certain week may need to be modified, and a note value may need to be added for particular weeks.

    Of course, I could just create another table called Payments to contain this information, but I don't want to have to enter the data manually. It would be a lot more productive to have the database calculate these values, but somehow let the user modify these values, if necessary. That's what I need to figure out!

    Can someone advise me how this can be accomplished? I've been reading up on the Make Table feature, will that do the job? Note that I do NOT want to create a payments table for every client so I'm quite sure.

    I look forward to reading your replies.

    Thanks in advance.

    Warmest,
    Zahid

  2. #2
    Join Date
    Feb 2007
    Posts
    348
    Append query
    Have a table called payments that gets appended to on a regular basis. Then you could let people modify that.

  3. #3
    Join Date
    Nov 2007
    Posts
    5
    Thanks Starkmann. Been doing some reading up and I think I need to use make-table function to create a new table for each client that lists the weekly payments, or alternatively a table for each week showing how much each client will be paid.

    However, I'm not sure if this is the most efficient way of doing this? If we're going to have a table for each client or week that could be in the 000's. Should I create a sub-database to hold these?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by zsaddique
    Thanks Starkmann. Been doing some reading up and I think I need to use make-table function to create a new table for each client that lists the weekly payments, or alternatively a table for each week showing how much each client will be paid.

    However, I'm not sure if this is the most efficient way of doing this? If we're going to have a table for each client or week that could be in the 000's. Should I create a sub-database to hold these?
    why do you think you need to create a separate table for each client and or each time period?

  5. #5
    Join Date
    Nov 2007
    Posts
    5
    Healdem, the reason I need to record every individual payment for each client is because sometimes a particular payment for client would have to be adjusted and this information needs to be recorded.

    An example of this is a client getting a double payment in week 1 as an incentive, for example, or an advance payment such as when a client is paid 10 weeks worth of payment in week 1 for some reason.

    From this table we will then print off a document to post to the client which shows the payments he or she will be receiving during his investment term, and it will be used to create monthly statements which go the clients at the end of each month showing the payments received up to that date.

    Do you think I am going about this the correct way, or do you have an alternative suggestion?

  6. #6
    Join Date
    Nov 2007
    Posts
    5
    Okay, been reading up more on normalization and playing around with sample databases and I've realised that I don't need to create a seperate table for each client or time period.

    Instead I have one payments table with the following fields...

    tblPayments
    PaymentID
    ClientID (Foreign key)
    PaymentDate
    PaymentAmount

    This will mean that all payments can go in this one table. For example, if a client is investing for 1 year and therefore will get 52 weeks of payments will have 52 records in table with his client ID value to link those payments to him.

    As Starkmann suggested I can use an append query to add these records to the payments table every time we add a new client to the databse.

    Would this work well?
    Last edited by zsaddique; 11-28-07 at 08:56.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by zsaddique
    Okay, been reading up more on normalization and playing around with sample databases and I've realised that I don't need to create a seperate table for each client or time period.

    the penny drops.. good to hear. rudy's site has a good spiel on db design issues form Paul Litwin, well worth a read

  8. #8
    Join Date
    Feb 2007
    Posts
    348
    Quote Originally Posted by zsaddique
    Would this work well?
    That's exactly it.

  9. #9
    Join Date
    Nov 2007
    Posts
    5
    Awesome!

    Will I have to write SQL code to do this or can I use the Query designer?

    Basically, I need to be able to take the start date, investment amount, investment term (10 weeks, for example), and the return percentage, and then add a record for each week with the weekly return for that client to the Payments table.

    Thanks for your help guys!

  10. #10
    Join Date
    Feb 2007
    Posts
    348
    Query designer will do it. Work with the formula builder. It will take a while to get exactly right but once it is done, it is done. You can run it all day long.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •