find maximum value in range of fields

[skiing]

Hello

Could someone advise/assist with the following:

I have fields like the following:
Badge Date 1
Badge Date 2
Badge Date 3
etc... to Badge Date 7

What I would like to have happen is when a new badge date is entered
the badge dates would cascade -- dropping the oldest date and
cascading badge dates -- ( i.e. Badge Date 1 would be the newly
entered date - Badge Date 2 would be the date previously in Badge Date
1 - previously in Date 2 goes to Date 3, etc...)

OR

The ability to pull the maximum ( most current date) from the Badge
Date fields.

Basically I want to keep the last 7 dates for the badgee - and then
use the most recent as a method to calculate a 30-day activation
reminder.

I would be working in an Access object - I am not familiar with SQL
enough to write the code - I can usually (somewhat) comprehend what
the code is doing.

Thank you for any assistance given

 

[Douglas J. Steele]

You should definitely reconsider your design. What you're describing is
known as a repeating group, and it actually violates 1st Normal Form in
database normalization theory.

Rather than having 7 fields in a single row, you should have 7 rows in a
related table. In that way, it becomes trivial to find the largest value.

 

[Jim Bunton]

I think you should consider putting badge dates into a separate table.

People table:
PesonId, PersonName . . . .

Badges table:
PersonId, BadgeDate.

You add an entry into the badges table each time a badge is issued.
[PersonId is the key column in each table]
[you need to look at the facility to define relationships between tables -
Menu - Tools > Relationships]

Just keeping 7 dates is a bit arbitary - what if you want more or less in
the future. You can, of course use Dog and DogId or whatever if thats more
appropriate than people!

You can find the latest badge date with something like
"SELECT Max(BadgeDate) as LatestBadgeDate FROM People INNER JOIN Badges ON
People.PersonId = Badges.PersonId;

OR you can 'see it' by constructing a Form (people) + subform (badges) on
which you could delete unwanted (too old) badge dates and see latest badge
dates.

There's no chance as far as I can see that you can do the sort of thing you
want to do really unless you start grappling with SQL and some coding!

Good luck

Jim Bunton