Max,
The problem you are having is that your tables are designed incorrectly! Also
you only need one stock table, not two. Try these tables:
TblStock
StockID
StockTypeID
TblStockType
StockTypeID
StockType
TblStockTransac tionType*
StockTransactio nType
Mutiplier
TblStockTransac tion
StockTransactio nID
StockID
Month
StockTransactio nType
TransactionQuan tity
*StockTransacti onType In, Multipiler 1; StockTransactio nType Out, Multipiler -1
You need a form/subform to enter StockTransactio ns. The main form is based on
TblStock and the subform is based on TblStockTransac tion. In the subform, you
use a combobox to select the StockTransactio nType (In or Out) and then enter the
TransactionQuan tity. TransactionQuan tity is always entered as a positive number.
The combobox is based on TblStockTransac tionType. BoundColumn =2, ColumnCount =
2, ColumnWidths = .75;0.
To get the inventory of any stock at any time, you need a query that includes
TblStock, TblStockType, TblStockTransac tionType and TblStockTransac tion. Include
the following fields:
StockType 'TblStockType
Month 'TblStockTransa ction
Transaction:[StockTransactio nType]*[TransactionQuan tity] 'Calculated Field
**
Enter the following expression in the criteria for Month:
<=[Enter Month]
Change the query to a totals query. Where it says GroupBy under Month, change to
Expression. Where it says GroupBy under Transaction, change to Sum.
** StockTransactio nType actually has the value of Multiplier (+1 or -1). So in
this calculated field, you are multiplying +1 times Quantity for In Transactions
and -1 times Quantity for Out Transactions.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdata sheet.com www.pcdatasheet.com
"MAX SMITH" <MA*********@HO TMAIL.COM> wrote in message
news:bb******** *************** ***@posting.goo gle.com...
I'm a newbie to Access and am having trouble understanding the logic
behind queries. I have 2 tables, 1 for stock in and 1 for stock out. I
have joined these in a union query. the fields I have are:
Month
Stockpile
Stock type1
Stock type2
Stock type3
Stock type4
What I want to do is produce a query that gives me a monthend stock
figure for each stockpile (without all the detail).
Thanks for any help
Max