IF Then

[SFCMatthews]

I have a problem. There are three things that have to be done (B,C,D) before (A) can be considered completed. So what I have is a field named QualType, its a list box on one of my forms. I want to create a report that will show me who has or has not competed there Qualifications. I think I would use a IF THEN statement but to sure where to start. In advance thanks for all of your help.

 

[SFCMatthews]

Without knowing your schema, I can't say exactly how you should do this so I'm going to assume that you have a properly normalized database where the qualifications would be three separate ROWS in a table (if they are columns, the schema is not normalized). In the AfterUpdate event of the subform that shows the qualifications, use a DCount() to count the number of completed qualifications. If the count is > 2, then set the QualCompleted field on the parent form to True.

Me.Parent.QualCompleted = True.

Ok here are my tables,
tblSoldierData with Name, SSN, and so on
tbleWeaponsData with typeOfWeapon, WeaponAssigned, QualType (Day, NBC, Night), QualDate, and SoldierID
A soldier can have many weapons but a weapon can only be assigned to one soldier. To be qualified on a weapon a soldier has to qualify Day,NBC, and Night. I need a report that will tell me who ins qualified and is not qualified.

 

[SFCMatthews]

Sounds like the tables are set up correctly. Use the DCount() in the AfterUpdate event of the subform based on tblWeaponsData to update a column in tblSoldierData to indicate certification.

To some this may sound like a violation of normalization but it isn't or at least it may not be. If certification is immediately revoked if the required qualifications change, then yes, this is a violation of normal forms so I take back my recommendation. But, I'm guessing that certification is effective for a time period and if that is true, then the certified flag does not violate the prohibition against duplicating data.

Ok I had to make some changes to my tables, they are as fallows; tblSoldiersData stayed the same
tblWeaponData no longer has the qulification data
NEW TABLE
tblWeaponQualData, QualType, QualDate, WeaponQualDataID, fk_WeaponDataID
Still need a query to make a report to tell me if soldier with weapon A has completed his qulification, weapon A needs qualtype B,C,D to be qualified on weapon A. Thanks for your help and support