SQL- Calculated Field

[fdtoo]

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
Fromebbie Mcnight
e-mail:dcartford@yahoo.ca

Q2


I need some expert to help me with this Access SQL Syntax as
there is an error message that says"At most one record can be returned by this subquery"
I think it means that only one result can be return on a subquery, but I need to display
individual ItemCode Qty that are > 30 days old from the Purchase_Date

my synatx are:

"Select distinct ItemCode,Purchase_Date, (SELECT Qty FROM Access_Table WHERE Purchase_Date <Now()-30 And Purchase_Date >Now()-60 AND ItemCode = Main.ItemCode) AS > 30DYS FROM Access_Table AS Main;"


My Query result should look something like this:

Purchase_Date ItemCode > 30 DYS | > 60 DYS | Total
01/06/2004 A100 0 | 15 | 15 |
02/07/2004 A100 20 | 0 | 20 |
12/06/2004 B100 0 | 10 | 10 |
--------------------------------
20 | 25 | 45 |
================================
can anyone help? Thanks
Fromebbie Mcnight
e-mail:dcartford@yahoo.ca