inventory variance report

[mcnewsxp]

i have an inventory program that requires a variance report.
the count table needs to be compare to the previous on hand count.
the previous on hand count will contain an item number and a quantity.
the count table may contain more than one instanc eo f the item number in
different locations.
at least two counts are taken and sometimes three.
my variance report(s) need to sum the count for each item in the count table
in order to campare to the on hand table to see if an item should be
included in the variance report and included for further data entry.
should i just add a boolean flag in the count table, create a sum query that
can be used to find variance in the on hand table and set the flag to true
in each of the item's boolVariance flag?

tia,
mcnewsxp

 

[Arvin Meyer [MVP]]

First run a query like (assuming the counted inventory table is
tblInventory):

SELECT ProductID, Sum(ProductCount) AS Total
FROM tblInventory
GROUP BY ProductID;

Then run another query adding the table storing the quantity on the books.
Use a left join which uses all the product id's from the products joined to
your query. Add a column: NZ([tblInventory].[ProductID],0) so that there are
no empty values and subtract this column from the supposed inventory. Now
add a criteria on this column of: >0 and you will have what you want.