Change status of master record based on results of mult related record

[RobertIngles]

Hi all;

Just can't wrap my head around this so I could use some direction.

Working on a Win7 deployment projet app test status report.

I have a table with the "Master" App name (ie. Adobe Design Suite CS3) that has multiple modules (ie. photoshop, illustrator, design etc.) which are in a seperate table joined by the "Master app name.

I am trying to create a query that will tell me if all the modules related to the "Master" app have passed testing which will indicate that the app can go to UAT. So if one of four apps failed the "Master" app is in a failed state and requires additional remediation. If four of four apps have passed then the "Master" app is in a pass state and can be put out for UAT.

Can anyone suggest how I can set up the query to return only those "Master" apps that all sub modules have passed testing?

Thanks all!!

 

[dhookom]

Use the min or max of the testing field in a join between the master and other table. If you need more assistance, you should provide some actual table and field names. At this point, we don't have a clue how you even record passed or failed.

Duane
Hook'D on Access
MS Access MVP

 

[RobertIngles]

Sorry for being vague Duane....

Tables are:
TBL_Master_Apps and TBL_Master_Apps_and_Modules.

One to many relationship between TBL_Master_Apps. [Master_App_Name] and TBL_Master_Apps_and_Modules. [Master_App_Name]

Fields in TBL_Master_Apps_and_Modules:
[Master_App_Name] and [App_Module_Included_in_Package] and [Test_Launch_Status].

Options for input to [Test_Launch_Status] are "Pass" "Fail" and "In Remediation".

I am looking to create a query that will return only [Master_App_Names] whose [App_Module_Included_in_Package].[Test_Launch_Status] all show as "Pass". If one or more of the [App_Module_Included_in_Package].[Test_Launch_Status] shows as "Fail" or "In Remediation" then the "Master App" is not to a point where it is ready for UAT.

I will also use the query to create a form based on TBL_Master_Apps and sub form based on TBL_Master_Apps_and_Modules. If all the TBL_Master_Apps_and_Modules.[App_Module_Included_in_Package].[Test_Launch_Status]in the sub form are "Pass" a visual cue will appear in the main form indicating the Master App is ready for UAT.

Since Master Apps can have from 1 to 20 App_Modules I need Access to recognize when all of the [Test_Launch_Status] fields are "Pass". If even one of the [Test_Launch_Status] fields is not "Pass" the Master App cannot go to UAT until all [Test_Launch_Status]fields are "Pass.

Thanks Duane -I hope this all makes sense
Let me know your thoughts -
Robert

 

[dhookom]

I would create a small table of status values with a column to store a value like 0 for Fail and Remediation and 1 for Pass. If you add this table to a totals query of TBL_Master_Apps_and_Modules, you can group by Master_App_Name and if the average of the new field is not 1 then it means there are one or more Fail/Remediation records.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Typed, untested:

SELECT Master_App_Name
FROM TBL_Master_Apps_and_Modules
GROUP BY Master_App_Name
HAVING Count(*)=Sum(Abs(Test_Launch_Status='Pass'))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886