How to create query to show percentages.

[Littlewood]

Guys,

Essentially I have both these queries.

One to show the total number of 'valves' by a certain manufacturer and another to show the certain number of valves that have failed their annual test by a certain manufacturer.

What I want to do it, look the the total number of valves for 'Noneywell' for example, then look at the total number of fails - and return that as a percentage.

Any help will be greatfully recieved.

[JoeM]

You can perform a query between these two query, joining on the "Make" field.
If you set it up so that it is a Left Join from the Total_by_Model query to the TotalFailed_by_Model query, it will return all your records.
Use the NZ function to return the total number of failures, that way you can have it return a 0 when there is no match.
Then just divide the two values to get your percentage.

[JoeM]

The SQL code of that query would look something like this:

SELECT 
    Total_by_model.Make, 
    Total_by_model.CountOfMake, 
    Nz([TotalFailed_by_model]![CountOfMake],0)+0 AS Failed, 
    Nz([TotalFailed_by_model]![CountOfMake],0)/[Total_by_model]![CountOfMake] AS Percent_Failed
FROM Total_by_model 
LEFT JOIN TotalFailed_by_model 
ON Total_by_model.Make = TotalFailed_by_model.Make;