SQL- Calculated Field

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