Totals queries with multiple product types

[mike_980]

I have had this issue before and could of sworn I fixed it but for some reason it's not working again....


Part of my database tracks fertiliser stock levels which have the following variables:

Fertiliser type (let's say type 1, type 2, type 3 etc)
Fertiliser location (the location at which they are stored)
Bag weight (the weight of the bags, 500, 600 or 1000Kgs)

All of the above parameters have their own tables which are related to the tables "tbl_add_fert" and "tbl_remove_fert"

Whenever fertiliser is added or removed from one of the stores, it is logged into the respective table and a query runs to calculate the new stock level as in theory the stock is always going to be the difference in the two tables.

I have two primary queries which generate total of each type that has ever been added and total that has ever been removed which both work perfectly when run but my problem is in creating a query that can calculate the difference.

(Addedd and Removedd are purposefully mis-spelled just so you know, that is not my issue)




That is one of the queries that is feeding the secondary query, the other of is the same just with different information.







And that is the query that I am having problems with, for some reason, if I remove a bag of a particular type of fertiliser from one store, it removes a bag of every type from every store. Also the Nz isn't working properly as when i run the query when bags have only been added and none removed, the query gives no results.

[June7]

You have two queries that summarize tbl_add_fert and tbl_remove_fert records? Is the summation by Fert_type? You have a table of fertilizer types? Join those two queries to the table.