Solved

SQL syntax for 'where exists' on first record

Posted on 1998-09-24
9
263 Views
Last Modified: 2012-08-13
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?
0
Comment
Question by:Sykey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 17

Expert Comment

by:ramrom
ID: 1963461
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
etc
 
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;
 
0
 

Expert Comment

by:TerryEllis
ID: 1963462
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;
0
 

Author Comment

by:Sykey
ID: 1963463
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.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 3

Expert Comment

by:kaldrich
ID: 1963464
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.

Kevin
0
 

Author Comment

by:Sykey
ID: 1963465
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..
0
 

Expert Comment

by:kamyanlai
ID: 1963466
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
0
 

Author Comment

by:Sykey
ID: 1963467
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..
0
 
LVL 3

Expert Comment

by:kaldrich
ID: 1963468
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.

Kevin
0
 
LVL 8

Accepted Solution

by:
Helicopter earned 200 total points
ID: 1963469
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.


0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question