Query on month

[MAX SMITH]

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

[Bruce Dodds]

Re: Query on month

MAX SMITH wrote:[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]

Try looking up crosstab queries in Help.

[PC Datasheet]

Re: Query on month

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
resource@pcdata sheet.com

https://www.pcdatasheet.com

"MAX SMITH" <MAXSMITH949@HO TMAIL.COM> wrote in message
news:bb7cbc16.0 408030052.719f3 aa2@posting.goo gle.com...[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]