Excel Training Message Board › Brain Teaser - Example Database Problem
|A former member||
Free Training Website DownloadsHi Everyone,
In a day or so we will be sending you a short questionnaire sending out a questionnaire to learn what you are most interested in.
In the meantime I know many of you are probably wondering how VBA can help you in Excel or Access. Yesterday I was asked to create a function to update the member codes in a database based on birthdate and acceptance date. Those two values; age and membership years, determine what membership level a member has and therefore the membership fees they pay.
With every new billing cycle the members must be checked to see if they qualify for an upgrade in membership based on their age and the numbers of years they have belonged to the organization. In the database there are over 6000 members so you can see what a daunting task this would be if done manually.
The criteria and some sample data (about 300 rows to make it interesting) will be uploaded to the website. If you are interested in learning how to program something like this let us know (via a new poll or an email) and I’ll be happy to set up a class on it.
There are two ways, one being a series of if statements and the other utilizing a table of values that the members data is compared against to see what the code should be.
If you want a challenge feel free to try to find a way to find the code yourself.
Order Member Code Description Criteria
1 7 Life Age 65 and Over , 35+ years of membership
2 5 Gold Age 70 and Over, 35+ years of membership
3 4 Silver Age 70 and Over, 20+ years of membership
4 8 Bronze Less than 65 Years old, 40+ years of membership
5 2 Junor Less than 65 Years old, 35+ years of membership
6 1 Member 21 and Over, Years in NA
7 0 Youth Less than 21, Years in NA
Life members have a lower rate than Gold members and so on. So although someone might fall within two categories the one you want to keep .
The age must be calculated by using an expression with the birthdate and the current date.
Hint: To find the years between two dates in Excel use DateDif(date1, date2, “y”)
Hint: The function for the current date in Excel is TODAY()
You will find the file at http://www.free-excel...