Count of Fields with "YES" in a related table
Another hopefully quick and easy one for you. i am trying to calculate the amount of items i have in stock in 1 field and another field of how many we own, these are rental items in case you think this is odd. so i have a main inventory table and then a serial inventory table eg. Main Product A serial is then Product a1,a2,a3,a4 etc.
i have managed to get the total qty to show in a field by doing a count of related records that have the same product ID but i cant figure out how to make a count of how many we have in stock.
on each serial record there is an "in stock" field with a yes or no drop down menu. i would like to count how many related records that have the same product id and have Yes selected. i hope this makes sense currently the 2 tables are connected with a relationship between the product id field only.