Current stock calculation by store location

[anonymous]

I have 3 table

1. stock IN
   which contains
   ID (primary key)
   PRODUCT
   DATE
   QUANTITY
   REMARKS
   RECEIVED BY
   STORE POINT

2.Stock OUT
STOCK_OUT_ID
CUSTOMER
PRODUCT_NAME
DATE
QUANTITY
REMARKS
STORE POINT

1. Products
   Product Name
   Product Description
   Price Image
   Total Inventory
   Total Requested
   Total Available
   store location(V C)

now current stock will be calculated by

SUM(
** SELECT(**
** Stock IN[QUANTITY],**
** [PRODUCT] = [_THISROW].[Product Name])) -**

sum( SELECT(
** Stock out[QUANTITY],**
** [PRODUCT_NAME]= [_THISROW].[Product Name]))**

This is only display the number of stock

But i have to display current stock of an item by store location and current stock of a item in that location .

how this possible??

[anonymous]

Hai,

     You have to subtract Stock IN Quantity and Stock Out Quantity to get the current stock

[anonymous]

Current stock has already calculated
but i want stock of the product at different location

[Lynn]

Hi @Arjun032
Have you tried with AND()

[anonymous]

no
how??

[Steve]

Read the help doc and experiment.

[anonymous]

@Steve

Here my tables am created current stock by calculating
this syntax
SUM(
SELECT(
Stock IN[QUANTITY],
[PRODUCT] = [_THISROW].[Product Name]))

sum( SELECT(
Stock out[QUANTITY],
[PRODUCT_NAME]= [_THISROW].[Product Name]))

this syntax will return stock of an item correctly as whole amount in all store location.
but i have 4 store point.

main issue that when stockout a product from specific store point then display the current stock of the product in that store location.
and also when an stockin were done at a store point then stock of the product only display in that storepoint.

how is thiss possible??

here my tables
products

stock in

stockout

anyone who knows please help??? because am new to this and try to learn !!!

[sangmin]

Hi~
I had the same problem.
Store location = Store point ??
When calculating inventories using AND functions, Location must also be added.

SUM(
SELECT(
Stock IN[QUANTITY],
AND(
[PRODUCT] = [_THISROW].[Product Name],
[STORE POINT] = [_THISROW].[store location]
)
))

-SUM( SELECT(
Stock out[QUANTITY],
AND(
[PRODUCT_NAME]= [_THISROW].[Product Name],
[STORE POINT] = [_THISROW].[store location]
)
))

I’m not an expert, but I hope it helped.

[anonymous]

Thanks @sangmin this syntax is okay and worked.

[anonymous]

** Worked in my case

SUM(
    SELECT(
        [Related Receiveds][QTY],
        AND(
            [DIA]=[_THIS].[DIA],
            [DESCRIPTION]=[_THIS].[DESCRIPTION]
        )
    )
) 
- 
SUM(
    SELECT(
        [Related Releaseds][QTY],
        AND(
            [DIA]=[_THIS].[DIA],
            [DESCRIPTION]=[_THIS].[DESCRIPTION]
        )
    )
) 
+ 
SUM(
    SELECT(
        [Related Returneds][FINAL RETURNED QTY],
        AND(
            [DIA]=[_THIS].[DIA],
            [DESCRIPTION]=[_THIS].[DESCRIPTION]
        )
    )

)