Subquery?

[GSMike]

I have a query that looks like this:
asofdt status no_each
1/31/02 All 27040
2/28/02 All 27038

Is there a way to generate a fourth column that has the difference between row 1 and 2, column no_each? Mike K

 

[mdthornton]

not really, you need to go about this a different way. eg, will your first query always return 2 rows? if, so you could write dependent queries that will give you what you are looking for. Best Regards,
Mike

 

[GSMike]

Thanks, Mike.
No, the query will no always return two rows, it will be multiple row; but the difference I am looking for in another column will always be the difference between two values in one column in two different rows.
???? Sounds confusing, probably. Does it make sense?
I'm just using my example of two rows to make it understandable.
I've always wanted to be able to compare values from different rows in a single query, and haven't quite figured out how.
I've been tooling around with subqueries, and I keep getting the same error message: "At most one record can be returned by this subquery...". Haven't quite figured that one out, either.
Here's my latest futile attempt (you can ignore the switch function field, that's another issue):

select *,switch(asofdt=#1/31/02#,no_each,true,no_each*-1) as New_No_Each, (select no_each from tblloantape2 where asofdt=#1/31/02#) as no_each1,(select no_each from tblloantape2 where asofdt=#2/28/02#) as no_each2,no_each1-no_each2 as no_each_subtot
from query10;

Mike K