AnsweredAssumed Answered

Displaying a count of records that have a certain value in an unrelated table

Question asked by SB_1 on Apr 12, 2012
Latest reply on Apr 18, 2012 by SB_1

Title

Displaying a count of records that have a certain value in an unrelated table

Post

Hi.

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. 

Any ideas?

Please let me know if you require more clarification. It is rather complex to describe our situation.

Thanks

Outcomes