Simple Query I cant seem to solve confused:

Hi All,

I think I have exhausted all avenues to try solve a query which I just cannot seem to get right with my limited knowledge?

Background: I receive data from a team of Surveyors weekly which includes and inventory of each of the sites they have visited. They have a predefined list of inventory types that they can select and I then import this data with associated fields including a quantity of each inventory type. i.e. Round tables 10 etc (the quantity is stored in its own field. As there are many Surveyors we do get the odd error where the Surveyor may assign the incorrect inventory type or alternatively miss an item or items on site.

I have a table that stores all the incoming data with associated fields, I have another table which is a copy of all the inventory types available to choose from prior to upload, in the next field in this table I have a yes no function. I have inserted a tick next to all the items that I don't think we should be adding to the inventory, a query is run and I can then raise the items on this list that think have been classified incorrectly - This works well.

Now my friends, here is where it gets a bit tricky for me. I have a similar table of inventory items I would expect to find at these sites which is its own table with yes/no's against each. Want I want to do is list all the items I would expect to find on site (All the inventory types in this table with a true value) and then list next to each of these items a sum of the records I have in my table by site. I can get the query laid out but cant get it to work. In the qty field I get 1's when it should be null or the table condenses and only shows values with a qty. I essentially need to see the all the inventory types "true" and then a summed qty alongside. If I don't have a record against an inventory type I need it to show 0 or false? With this I can establish if something has been potentially missed during the survey.

I really someone can understand what I have written here and provide some guidance.

Many Thanks,

GC.