Calculating Membership Fee from a field in another table?

[elitehass]

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?!
These are the relevant tables and I've also attached my database (the password is "password" for any of the users):

[June7]

There is no table for recording the monthly obligation.

There is no table for recording payments received.

[elitehass]

There is no table for recording the monthly obligation.

There is no table for recording payments received.

The membership opportunities table has a field for recording whether a member has paid, I realise this database isn't exactly perfect but I really don't have time
What do you mean by recording monthly obligation?
Do I need these tables for the calculation?

[June7]

Most people pay their expenses month-to-month, such as athletic club membership. Sign up for membership at some level of participation, club sends you a bill each month, pay bill. A normal database would have tables to record these monthly billings and receipts.

I can't look at your db until later tonight. Will see what I can come up with.

[elitehass]

Most people pay their expenses month-to-month, such as athletic club membership. Sign up for membership at some level of participation, club sends you a bill each month, pay bill. A normal database would have tables to record these monthly billings and receipts.

I can't look at your db until later tonight. Will see what I can come up with.

Thanks for the help! I understand what you mean and how it is a big flaw in the database, and I will try to correct it if I have time.
I think what I have to do for the Renewal Fee field is to convert the Length of Membership (days) field into months and then multiply it by the cost per month in a query, but how to do the functions and expressions etc I have no idea.
Cheers again,
Elitehass

[June7]

Building queries with or without table joins and calculations in queries are basic Access functionality. Review:

http://office.microsoft.com/en-us/ac...aspx?section=8

http://office.microsoft.com/en-us/ac...005188023.aspx

[elitehass]

Building queries with or without table joins and calculations in queries are basic Access functionality. Review:

http://office.microsoft.com/en-us/ac...aspx?section=8

http://office.microsoft.com/en-us/ac...005188023.aspx

Thanks again, but you see the problem is that the Renewal Fee field is the fee the members owe on their Renewal Date. What they owe is determined by how long their membership was (Length of Membership (Days) field) multiplied by the Cost per month field. For example, if member 1 was on a Gym membership for 30 days starting 10/05/2013 then they would owe £30 on the 09/05/2013. I just don't know how to do this because the Length of Membership field is in days and if that is calculated by the cost per month the result will be wrong. This is why I need to convert the field to months.

[June7]

I am not quite sure what you want to calculate. Do the members pay monthly or do they pay for their full membership all at once? In other words, do the individuals committed to a 365-day membership pay for the whole year in one shot? Since the rates are monthly I think that yes, should show months instead of days. Then join tables in query and multiply months by rate.

[Beetle]

You have a few choices;

1. Since you charge by the month, store the length of membership in months, not days.
2. Keep the Length Of Membership as is and convert it to months in the query by dividing by 30.
3. Store the Cost per Month as Cost per Day instead.
4. Convert the Cost Per Month to Cost per Day in the query by dividing by 30

As far as creating the query goes, you simply join the two tables in a single query, return the fields you need from each table, then add additional fields to perform the necessary calculations.

BTW - I recommend you avoid calculated fields in tables (i.e. your Renewal Date field). First, the calculated results are not reliable. If the calculation is changed after data has been entered, existing records may not get updated correctly. Second, it makes your database unusable in older versions of Access (A2007 and older). Calculated fields belong in queries, not tables.

I also recommend you avoid spaces in your field names. It causes additional work down the road because you have to bracket them in queries and code.