Changing Field in One Table based on Date in Another

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

scs - 19 Feb 2006 01:06 GMT

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

Reply to this Message

John Vinson - 19 Feb 2006 02:51 GMT

>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
[quoted text clipped - 10 lines]
>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]

Reply to this Message

scs - 19 Feb 2006 06:19 GMT

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

>>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
[quoted text clipped - 22 lines]
>
> John W. Vinson[MVP]

Reply to this Message