Finding Dates

[Little Rattler]

I have a database of members and several date columns. These are
Testpassdate, Renewdate1, Renewdate2, Renewdate3, etc. Members join at
different times, and I need to be able to produce a query showing when
members are next due for retest. I can use the Dateadd to calculate two years
from the last date. It is an ongoing process with current members and those
yet to join, therefore some of the fields will be empty will others will not.
How do I query which field contains the next due date?
Many thanks,

 

[Tom van Stiphout]

On Fri, 15 May 2009 05:06:07 -0700, Little Rattler

You have an incorrect database design, and from that follow problems
like the one you are describing. You have a "repeating group" with
your RenewdateX fields. If I renew every week, will you create 52
fields per year to hold my info? This data needs to be spun off in its
own table: each Member record has many Renewal records, 1 : M related
to each other.
Then your problem goes away.

-Tom.
Microsoft Access MVP

 

[Little Rattler]

The renewal date would be every two years, and no date would be the same, so
I was trying to have a record of each persons dates to build up a sort of
history. Five columns or fields would cover a period of 10 years assuming
that member stayed in the group that long.

 

[John W. Vinson]

The renewal date would be every two years, and no date would be the same, so
I was trying to have a record of each persons dates to build up a sort of
history. Five columns or fields would cover a period of 10 years assuming
that member stayed in the group that long.

"Fields are expensive, records are cheap". Sure, you can have multiple renewal
fields, but you'll have the hassles you're now experiencing trying to search
them; and eventually you'll run out, and then need to either discard history
or restructure your table, all your queries, all your forms... ouch!

You're using a relational database; use it relationally! Follow Tom's
excellent advice. If you work with Access as it was designed rather than
struggling against it, you'll find it very effective.