Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2013
    Posts
    3

    Unanswered: Calculating Membership Fee from a field in another table?

    Hi, I've made a membership database for an imaginary leisure centre as part of my A Level coursework - only after more or less finishing my project, I've realised that I haven't provided a way for the end users to calculate fees for members.
    I really have no idea how to do this, I suppose the calculation I would have to do is multiply the Length of Membership (days) field on the Membership Opportunities by Cost per Month on the MembershipTypes table.
    How do I do this?! The problem really is that the cost is in months, and the length of membership is based on days, and I cant change the Length of field because the renewal date field relies on it.
    These are the relevant tables and I've also attached my database (the password is "password" for any of the users):

    http://i.imgur.com/DivGgOc.png
    http://i.imgur.com/InpjikL.png
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I can't open your file with 2007, but basically, if you have Cost per Month and Length of Membership in days, you have to convert from one to the other. Most people, I believe, would use the simple expediency of assuming a month to be 30 days. And then:

    Either

    Cost = Length of Membership * (Cost per Month/30)

    or

    Cost = (Length of Membership/30) * (Cost per Month)

    If the Cost per Month comes from a separate Table, you'll probably have to retrieve them into the current Form using the DLookup() Function, with the Membership Opportunities as the Criteria or 3rd argument of the Function.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Feb 2013
    Posts
    3
    Quote Originally Posted by Missinglinq View Post
    I can't open your file with 2007, but basically, if you have Cost per Month and Length of Membership in days, you have to convert from one to the other. Most people, I believe, would use the simple expediency of assuming a month to be 30 days. And then:

    Either

    Cost = Length of Membership * (Cost per Month/30)

    or

    Cost = (Length of Membership/30) * (Cost per Month)

    If the Cost per Month comes from a separate Table, you'll probably have to retrieve them into the current Form using the DLookup() Function, with the Membership Opportunities as the Criteria or 3rd argument of the Function.

    Linq ;0)>
    Hi thanks for the reply!
    I can try to convert the database to 2007 if you'd like?
    I'm having trouble with the Dlookup function, could you please show me the expression I would use? I was not able to use it in a table or a query.

Posting Permissions

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