Subqueries

[Phil McF]

I am trying to create a query to run against an Access db.The BillngData
table has (in part) the following fields.

Date, JobNo,Code,Charge

I want a SQL query that returns Date, JobNo, SumOf Charge for each JobNo
where the SumOfCharge is the total for all codes since last Date that
Code="INV"
I believe this can be done using a subquery but every attempt so far has
failed. The use of EXISTS is the downfall so far. I am unsure as to the
correct syntax and the help on subqueries is not much help.
Any suggestions or pointers would be appreciated.

Thanks
Phil

 

[Pavel Romashkin]

I replaced the reserved word Date with MyDate. Try something like

SELECT Last(MyDate), JobNo, Sum(Charge) AS SumOfCharge FROM BillingData
GROUP BY JobNo WHERE Date > (SELECT Max(MyDate) FROM BillingData WHERE
Code = 'INV') HAVING Sum(Charge) > 0

but the "date" field is meaningless (becasue it is one of the dates) and
so is code, which I omitted.
SQL is air code - please check.
Pavel