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
resource@pcdatasheet.com www.pcdatasheet.com
"MAX SMITH" <MAXSMITH949@HOTMAIL.COM> wrote in message
news:bb7cbc16.0408030052.719f3aa2@posting.google.c om...[color=blue]
> 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[/color]
Query on month 
