Hide fields that meet condition and display only selected fields

[ipaqAccess]

Hi,

I have table Parts with Fields:
- Part number: Contain part numbers
- Description: Description of the parts.
- 1st Sample: Contains either "Approved" or "Conditional Approved" or "Rejected"
- 2nd Sample: Contains either "Approved" or "Conditional Approved" or "Rejected"
- 3rd Sample: As above
- 4th Sample: As above
- 5th Sample: As above
The 1st,2nd... Sample Fields are the times that the Part sample is made and given approval if it's meet the design or not.

I'm trying to create a report that show which parts is Rejected and Conditional Approved so that can email to ask for more samples. I dont want to show 5 columns at once (E.g Rejected Rejected Rejected Rejected Rejected). I only want to show the latest Samples that are Rejected/Conditional Approved and hide all the Approved parts.


Please help to guide me how to do this! Thanks so much.

 

[Beetle]

The underlying problem here is your table structure. The fields [1st Sample] through [5th Sample] should not exist in the Parts table. The samples should be in a separate, related table. A correct table structure would look more like;

tblParts
******
PartID (Primary Key - Autonumber)
PartNumber
Description
-other attributes specific to each part

tblSamples
********
SampleID (PK - Autonumber)
PartID (Foreign Key to tblParts)
SampleNumber - Values like 1,2,3 (or 1st, 2nd, 3rd)
Status - Values like "Approved", "Rejected", etc.

This way you can have as few, or as many, samples as are needed for any given part. When creating a report, you could simply query for only Rejected/Conditional values from the Samples table.