Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL syntax for 'where exists' on first record

Posted on 1998-09-24
9
Medium Priority
?
265 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 400 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

597 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