I've got two tables: [project] and [expenses], they are joined on the field [projectnumber]. In the table [expenses] is also a field [date]. What I want to do is create an SQL statement that shows every record in the [project] table ... but ... it should check the last record in the [expenses] table (the [expenses] table is sorted on date descending so the last record is shown first) to see if the field [amount] has a positive value. All other records in the [expenses] table have to be discarded.
I has to be done in one SQL-statement without using recordsets or other queries because I'm transfering the query to another form and use it as a recordsource for that form.
The syntax I have so far is: Select * from [project] where exists(select * from [expenses] where projectnumber = [project.projectnumber] and ...
Dows anyone know the correct syntax?