Access 2003: How to Compare Fields in a Record 2

[Co7Homer]

Access 2003 / XP SP2

I have Googled and gone through Microsoft's help and cannot find an answer to my problem so I am hopeful that someone here can help me.

Background:

We have been doing a version of the biggest loser at work and are about to start our second session. I have a database that keeps track of pounds and percentage lost on a week-to-week and cumulative basis. All was well until they decided it would be better on our week-to-week to always check for the lowest weight a person has achieved and subtract against that weight, rather than a person's actual weight between weeks. I looked at the functions available and thought the MIN function might do the trick, but unfortunately, all of my efforts to make it work have failed.

So, I need help with a function or formula to compare the fields in a table as it builds each week:

Start - 184 Week7 - 177
Week1 - 182 Week8 - 180
Week2 - 180 Week9 - 178
Week3 - 181 Week10 - 177
Week4 - 181 Week11 - 177
Week5 - 182 Week12 - 175
Week6 - 179 LowWeight - 175

Each week, I need to compare the current weight against all of the prior weeks in order to determine which weight is the lowest in a record. The LowWeight field for Week 1 would be 182; Week 2 would be 180; Week 3, 180 and stay at 180 until the person hits 179 and then change to the lower number with each lower weight achieved.

My apologies - I am an ignorant user and do not do programming, so we have to stick to formulas and functions.

I need help with which function, if any, can do this for me and equally imortantly, I need to know the proper syntax - or help with a formula from brighter minds than mine.

Thank you so much for any help you can offer!!!

C.H.
7/5/2008

 

[MajP]

You did not say where and how you want to use the answer, but most likely you need a dmin function.

http://www.techonthenet.com/access/functions/domain/dmin.php

 

[fneily]

You didn't post your table(s) structure. I hope you don't have a table that looks like this:
EmployeeID Week1 Week2 Week3 etc.
Violates first normal form. You may want to see:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Since you're new to Access, you must normalize your tables first. So it seems you need two tables:
tblEmployee
EmployeeID Primary Key
other info fields

tblWeek with a multi-field key with two fields:
WeekID PK
EmployeeID PK
Weight

So tblWeek would have data like:
1 Bob 180
1 Jane 134
1 Bill 210
2 Bob 179
2 Jane 133
2 Bill 220

Now you just run a query picking off the employee and use the min function on their weight. So for Bill you'd get 210.

 

[Co7Homer]

Thank you Mapj and Fneily! Both of you have given me invaluable information which will help me out so much!!

I'll let you know if I was able to get it to work for me.

C. H.
7/6/08