Displaying a count of records that have a certain value in an unrelated table
I have a database with the main table "Subjects". This table contains all the information for the participants on our trial. This is linked to many other tables that contain the data for each subject at many visits.
I have 4 unrelated tables that contain information about stock levels for 4 different allocations of product.
Each 4 unrelated table relates to canisters or boxes of product we have had manufactured.
Tables : CanType1, CanType0, BoxType1, BoxType0
Our manufacturer is supplying us with cans and boxes as we go so we dont have a large stockpile.
From a layout based on the subjects table I need to see how many CanType1, CanType0, BoxType1 and BoxType 0 we have in stock. There is a field in each 4 tables that contain a 1 if that can/box is unavailabe (used or not yet recieved) or a 0 if it is in stock and available for allocation.
All i need is a count of how many of each CanType1, CanType0, BoxType1, BoxType0 are in stock (have a 0 in the stock field) on a layout based on the Subjects table.
Please let me know if you require more clarification. It is rather complex to describe our situation.