I'm building a database to record membership details. I have one table for people, each with a unique person ID as the PK. I have another table which records the membership periods. These will generally run for 1 year.
The membership periods table has the person ID, a membership period id (PK)and a membership number.
Generally the membership number will stay the same for a person, but there is a chance that one person may need to be issued with a new membership number. What I want to do, is open a continuous form which will display all the membership periods relating to a particular person ID and from this I want to be able to add a new membership period. I can open the form to display just that person's membership period but what I'm struggling with is as follows.
If someone doesn't have an exisiting membership number, automatically assign them the next membership number available in the membership period records. If someone does already have a membership number, automatically fill the membership number field for the new membership period with the most recent number that person has had assigned to them.
I hope that makes sense, but if you have any questions, let me know.
The membership periods table has the person ID, a membership period id (PK)and a membership number.
Generally the membership number will stay the same for a person, but there is a chance that one person may need to be issued with a new membership number. What I want to do, is open a continuous form which will display all the membership periods relating to a particular person ID and from this I want to be able to add a new membership period. I can open the form to display just that person's membership period but what I'm struggling with is as follows.
If someone doesn't have an exisiting membership number, automatically assign them the next membership number available in the membership period records. If someone does already have a membership number, automatically fill the membership number field for the new membership period with the most recent number that person has had assigned to them.
I hope that makes sense, but if you have any questions, let me know.
