Stock list

[Lasse T]

I hope I can explain this with my limited english.

I´m trying to create a stock list. So far I have 3 sum queries. One to
calculate purchased items, one to calculate sold items and one that joins
theese two and calculates stock. Now I can check stock for each item but
only one at the time. If I enter an item number in the query i got stock
value and number of items in stock for that item.
I can not figure out how to get this into a list. Some kind of loop
perheaps?

I tried to make a query with all 3 tables (items, purchased and sold) and
used left join to show all posts. The problem is that i get a row in the
query for each time i sell or purchase the same item number. I understand
that the query has no way of knowing how to deal with multiple posts like
this so I guess that was a wrong approach.

This problems has been bugging me for a while. The database works fine and
are used in a tire and wheel shop and I have an items form that shows number
of items in stock foir each item, using dlookup. Hovewer, the owner (my
brother) would like to be able to print out an inventory list once in a
while to check against his stock.

Any help would be highly appreciated.

Lasse T
---------------

 

[Albert D. Kallal]

You don't mention how many tables you have.

tblStockItems - our basic list of possible stock items

tblStockAdd - our basic list of stock that we add, of course the
"stockID" is from the above table

tblStockSold - our basic list of stock that we sold (or, it could be
the detail lines in a invoice).

So, a query to generate the current stock would be:


select stockID, Description,
(select sum(StockAdd) from tblStockAdd where stockID
= tblStockItems.StockID) as TotalAddStock),
(select sum(StockSold) from tblStockSold where
stockID = tblStocktems.StockID) as TotalSoldStock),
(TotalAddStock - TotalSoldStock) as InStock

from tblStockItems

So, the above would show stockID, Descripition,
TotalAddStock,TotalSoldStock, InStock

the above can easily be sent to a report. Your tables are likely
different..but the above gives you the idea. In fact, even when selecting a
stock item that is to be sold..the above will give you totals at all items
on the fly...