I have an access database that has organizes test result data for multiple suppliers. Three of the tables in the database are tblSuppliers which lists all of the suppliers we have test data for, tblResults which lists the possible combinations of test outcomes, and tblTestResults which lists the actual outcomes of the tests which were performed. What I am trying to do is come up with a query that will list all the possible combinations of suppliers from tblSuppliers and results from tblResults and then count all of the instances of this combination from tblTest Results. So far, I have gotten so far as creating a query that pulls SupplierName from tblSupplier and Result from tblResults and when you run the query, it lists all of the possible combinations. When I try to add the following expression to the query, I'm getting a "Your expression does not include the specified expression 'SupplierName' as part of an aggregate function." error:
Code: Select all
Supplier Result: Count(IIf([tblTestResults]![SupplierName]=[tblSuppliers]![SupplierName] And [tblTestResults]![TestResult]=[tblResult]![Result],1))
For your information in case it's helpful, this is the SQL view for what I currently have:
Code: Select all
SELECT tblSuppliers.SupplierName, tblResult.Result, Count(IIf([tblTestResults]![SupplierName]=[tblSuppliers]![SupplierName] And [tblTestResults]![TestResult]=[tblResult]![Result],1)) AS [Supplier Result]
FROM tblSuppliers, tblResult, tblTestResults;
Can anyone tell me where I'm making a mistake?