Changing Field in One Table based on Date in Another

I have a customer table and a membership table. A customer can have many
memberships. Most memberships expire after 10 weeks or so. Is there a way
to have an Active checkbox in the customer table uncheck or become false
when all the customers memberships have gone past their expiration date?

The reason I want to do this is because I enter visits on a form into the
visit table. I get the info from a sign in sheet. If I'm entering visits
and a customer doesn't have an active membership I need to know about it so
I can speak with them about renewing. My logic is: Only Active customers
will be available in the pull down list for customers on the visit input
form.

Thanks for any answers or advice.
Steve

On Sat, 18 Feb 2006 17:06:22 -0800, "scs"
wrote:

I have a customer table and a membership table. A customer can have many
memberships. Most memberships expire after 10 weeks or so. Is there a way
to have an Active checkbox in the customer table uncheck or become false
when all the customers memberships have gone past their expiration date?

The reason I want to do this is because I enter visits on a form into the
visit table. I get the info from a sign in sheet. If I'm entering visits
and a customer doesn't have an active membership I need to know about it so
I can speak with them about renewing. My logic is: Only Active customers
will be available in the pull down list for customers on the visit input
form.

Thanks for any answers or advice.
Steve


It's not necessary to store this information in a (redundant) field in
the membership table.

Instead, you can base the combo box on a Query joining the membership
table to the customer table; select only active memberships. Uncheck
all the fields in the membership table in the Show checkbox on the
query designer, and set the Query's Unique Values property to True.


John W. Vinson[MVP]

Thank you. I'll give it a try. I guess the sky's the limit on what can be
done.

"John Vinson" wrote in message
...
On Sat, 18 Feb 2006 17:06:22 -0800, "scs"
wrote:

I have a customer table and a membership table. A customer can have many
memberships. Most memberships expire after 10 weeks or so. Is there a
way
to have an Active checkbox in the customer table uncheck or become false
when all the customers memberships have gone past their expiration date?

The reason I want to do this is because I enter visits on a form into the
visit table. I get the info from a sign in sheet. If I'm entering visits
and a customer doesn't have an active membership I need to know about it
so
I can speak with them about renewing. My logic is: Only Active customers
will be available in the pull down list for customers on the visit input
form.

Thanks for any answers or advice.
Steve


It's not necessary to store this information in a (redundant) field in
the membership table.

Instead, you can base the combo box on a Query joining the membership
table to the customer table; select only active memberships. Uncheck
all the fields in the membership table in the Show checkbox on the
query designer, and set the Query's Unique Values property to True.


John W. Vinson[MVP]