Newbie - How to find the latest value in a few fields

[ipaqAccess]

Hi,

I got a table contained fields below:
Part number.
Review status 1
Review status 2
Review status 3
....
Review status 10
Latest Review status. (this is the field that have the value of the latest Review, assume 1 is the 1st review then after that is 2 ... until 10. Field has not have the review yet will be blank or N/A)

I think that this Latest Review status not needed to be a Field but rather to be generated whenever I run the report or run the form. But as a very newbie, I dont know how to code this. Anybody can give some advise what should I use for this?

What I want to do:
- In a form with a List box display Part number - After select the part a text box will show the latest Review status instead of showing 10 Review status
- In a report generate the Review status of all the parts.

Thanks much in advanced

 

[Alansidman]

It appears that you have a non-normalized table. You have repeating information in more than one field. Once you normalize your table, then you will be able to obtain last status easily with out code but with normal query.

Look at this

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

It should help you in designing your db properly.

 

[jdraw]

I'd suggest a normalized table


PartNumber
ReviewStatus
ReviewDate

There would be multiple records in this table for a given PartNo. Each Review would be uniquely identified by PartNo + ReviewDate

Latest Review for a given PartNo is the Record with the latest ReviewDate.

Also, I'd recommend you do NOT use spaces or special characters in field or object names.

Here's a short document that will help with Design.
http://forums.aspfree.com/attachment.php?attachmentid=4712