Solved

SQL syntax for 'where exists' on first record

Posted on 1998-09-24
9
255 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
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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now