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
TblStockTransactionType*
StockTransactionType
Mutiplier
TblStockTransaction
StockTransactionID
StockID
Month
StockTransactionType
TransactionQuantity
*StockTransactionType In, Multipiler 1; StockTransactionType Out, Multipiler -1
You need a form/subform to enter StockTransactions. The main form is based on
TblStock and the subform is based on TblStockTransaction. In the subform, you
use a combobox to select the StockTransactionType (In or Out) and then enter the
TransactionQuantity. TransactionQuantity is always entered as a positive number.
The combobox is based on TblStockTransactionType. 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, TblStockTransactionType and TblStockTransaction. Include
the following fields:
StockType 'TblStockType
Month 'TblStockTransaction
Transaction:[StockTransactionType]*[TransactionQuantity] '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.
** StockTransactionType 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******@pcdatasheet.com www.pcdatasheet.com
"MAX SMITH" <MA*********@HOTMAIL.COM> wrote in message
news:bb**************************@posting.google.c om...
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
