Calculated Query

[dichotomous]

I have a data base to manage members of a boat club.

I have a fees table - just used for String. The fees table only has FeesID, Fee.
$72.50, $36.25 $145.00

Members pay fees depending on their category. So, my members table:

MemberID - Autonumber
Name - Shorttext
CardCode# - number
Category - number

If they are a category 1, then they pay the $145.00

If a 2, then, they are a lifetime member and no fee is payable.
If a 6, they are a veteran and pay half price.

Etc.....

I don't want to store fees payable, rather, calculate it in a query.
How can I do this. Where, a category 1 = $145.00.

I thought about an "if and then", procedure, but am probably wrong.

Please help.

thank you.

 

[pbaldy]

If your fees table has the category, you can join the tables together to return the fee. That said, while I wouldn't normally store something that can be calculated, what will you do if and when the fees change? This is something I'd probably store with the transaction.

 

[plog]

You would have a Category field in your Fees table to note which category each value is associated with. Then its a simple JOIN between Members and Fees. However...

You said there's a lifetime membership, which implies the other memberships renew. So that sounds like you need a renewal date. Also, what happens if someone's category changes? Do you want to lose historical data about their membership or retain it?

You need to better think out what you want to capture and all the finer points.