SQL-Query Access by Date

[Cady Steldyn]

Example:

Date | ItemCode | Stock_In_qty | Stock_Out_qty | Bal_qty
------------------------------------------------------------------

12/09/2003 | A100 | 20 | 0 | 20
25/10/2003 | A100 | 0 | 10 | *10

I need to query an Access database that will take field 3 (Stock_In_qty)
plus any bal from
the above row in the calculated field (Bal_qty) minus field 4
(Stock_Out_qty) that will show me
the latest Bal_qty, note that Bal_qty = (Stock_In_Qty - Stock_Out_Qty)
AS Bal_Qty.As an Example in the above scenario,
the Bal_qty in the second row on 25/10/2003 is (0 + 20(Row 1) - 10) =
*10.Stock out not necessary comes from
Stock In, it could simply be taken out from the Bal_qty balance from
previous month, any clues?

can anyone help? Thanks


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[John Bell]

Re: SQL-Query Access by Date

Hi

As this is an access query you may get a better answer from an Access
newsgroup, but you may want to create a query similar to the following:

SELECT S.Date, S.ItemCode, S.Stock_In_Qty, S.Stock_Out_Qty ,
( SELECT SUM(t.Stock_In_ qty) - SUM(t.Stock_Out _qty) FROM Stock t WHERE
S.date >= t.date ) AS TotalStock
FROM Stock S

In Access you probably have to create a predefined query instead of the
subquery and say call it StockBallance

SELECT Date, SUM(Stock_In_qt y) - SUM(Stock_Out_q ty) as TotalStock FROM
Stock GROUP BY Date

Then you query could be:

SELECT S.Date, S.ItemCode, S.Stock_In_Qty, S.Stock_Out_Qty , b.TotalStock
FROM Stock S
INNER StockBallance b on b.date = s.date

John

"Cady Steldyn" <dcartford@yaho o.ca> wrote in message
news:4143d11e$0 $26085$c397aba@ news.newsgroups .ws...[color=blue]
> Example:
>
> Date | ItemCode | Stock_In_qty | Stock_Out_qty | Bal_qty
> ------------------------------------------------------------------
>
> 12/09/2003 | A100 | 20 | 0 | 20
> 25/10/2003 | A100 | 0 | 10 | *10
>
> I need to query an Access database that will take field 3 (Stock_In_qty)
> plus any bal from
> the above row in the calculated field (Bal_qty) minus field 4
> (Stock_Out_qty) that will show me
> the latest Bal_qty, note that Bal_qty = (Stock_In_Qty - Stock_Out_Qty)
> AS Bal_Qty.As an Example in the above scenario,
> the Bal_qty in the second row on 25/10/2003 is (0 + 20(Row 1) - 10) =
> *10.Stock out not necessary comes from
> Stock In, it could simply be taken out from the Bal_qty balance from
> previous month, any clues?
>
> can anyone help? Thanks
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]