• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

SQL syntax for 'where exists' on first record

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?
1 Solution
ramromconsultant Commented:
Sounds like you're trying to:

1) display all project records
2) determing sign of THE most recent expense record

OR do you want to determine the sign of the most recent expense for EACH project?

Assuming the latter, your result set (as I see it) looks like:

project 1 data ... amount of latest associated expense
project 2 data ... amount of latest associated expense
Try this (i've assumed a 2nd projects field named desc. You may have others. Be sure to include each desired field in the select list and in the group by list):

SELECT projects.projectno, projects.desc, Last(expenses.amount) AS LastOfamount, Max(expenses.date) AS MaxOfdate
FROM projects INNER JOIN expenses ON projects.projectno = expenses.projectno
GROUP BY projects.projectno, projects.desc;
I think ramrom's query has a flaw. Max(date) could give you a date that contains a negative amout. try this:
SELECT Project.[Project #], Project.Name, First(expenses.Date) AS FirstOfDate, First(expenses.amount) AS FirstOfamount
FROM expenses INNER JOIN Project ON expenses.[Project #] = Project.[Project #]
WHERE (((expenses.amount)>0))
GROUP BY Project.[Project #], Project.Name
ORDER BY Project.[Project #], First(expenses.Date) DESC;
SykeyAuthor Commented:
I can't get it to work and I think we have a bit of a misunderstanding. What I want is this:
Fir every [project] the most recent record in the [expenses] table has to be checked. If the [amount] is >= 0 then the [project] has to be shown. Otherwise the [project] has to be skipped. The query should not be checking previous records in the [expenses] table.
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Sykey, being the lazy type of guy I am, I go straight to Query Builder on these.  This is how I would do it:

Go into Query Builder.

Drag in the project table and the Expenses table and connect them on Projectnumber.  From your description, it seems you want an inner join on the projects with Expenses having amounts >=0.  If this is not the case (you want all projects, but only expenses for the ones with amounts greater than or equal to 0), first create a query to screen out the expenses you don't want, and join THAT query to Project.

Drag down the fields you want, including the amount for the expenses table.  Set the constraint on the amount to be >= 0.

Then you can look in under the View menu to grab the SQL.  Hope this helps.

SykeyAuthor Commented:
Sorry, I think you haven't read my comments to clarify what I want. Or maybe didn't understand what I meant by them. Please read my comments carefully..
SELECT A.*,B.* FROM Project A,Expenses B
WHERE (A.Projectnumber = B.Projectnumber) AND
(B.Amount >= 0)

This should select the records you require, then you can use
a bit more code to sort it out
SykeyAuthor Commented:
I know how to join two tables, but what I really want is this:
For every [project] the most recent record in the [expenses] table has to be checked. If the [amount] is >= 0 then the [project] has to be shown. Otherwise the [project] has to be skipped. The query should not be checking previous records in the [expenses] table.
The only thing I have achieved so far is that the query shows all the [projects] where a [expneses] can be found with [amount] > 0. But he also looks at records other than the most recent. He should NOT do that..
Sykey, I know you want to do this off of one query; that seems to be the "flaw" you are finding with my logic.  However, if you build a query off of a query, the first one will execute when the second one does; your form can be built off the second with no additional overhead on your part. (You don't have to kick off the first query to get the second one to run; it just happens.)

The screening by Max date guarantees you will only get the most recent date in the first query.  Try it; I think you can confirm it works or does not very quickly.

If I am misunderstanding you, please give me a little clarification and I will try and modify my response.  Thanks.

I agree, I don't think this can be done with one query. You need a bridging query to refine your original recordset because you are using aggregate functions group by and max.

I would create a query called (say) qryBridge

SELECT Project.projectnumber, Max(Expenses.date) AS Maxdate
FROM Project INNER JOIN Expense ON Project.projectnumber = Expense.projectnumber
GROUP BY Project.Projectnumber
ORDER BY Project.Projectnumber, Max(Expenses.date) DESC;

and another query to only show those records in the above where the expense is a positive number

SELECT Expenses.projectnumber, Expenses.date, Expenses.amount
FROM qryBridge INNER JOIN Expenses ON (qryBridge.Maxdate = Expenses.date) AND (qryBridge.projectnumber = Expenses.projectnumber)
WHERE (((Expenses.amount)>="0"))
ORDER BY expenses.projectnumber;

Your concern over using 2 queries is a non starter. You would bind your form or whatever to the 2nd query and the bridge query will run transparently in the background quite happily each time you load the form.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now