Query on the last element in a subtable

[cmbirk]

Hi everyone,
I have a database with project information in one table, and a series of dates for each project in another. I want to create a report showing the project information for a date, but only if that date is the last date in the date subtable. In a pseudocode query, it would be something like

Display [Project Data] where last([Dates].[Release Date]) = "some date"

both tables have a project id field that link them.

Thanks,
Chris

[patjones]

In the table that contains the dates, is the last date (the one you're looking for) always the most recent date? Maybe a little more explanation about why you're after that one piece of information will help us out. Thanks.

Pat

[cmbirk]

The dates are actually in a release form. Ie. 1A for the first release of january, and 5B for the second release in May. They are in text form. I am trying to create a report that will prompt for a release date when it's opened, and then will show all those projects with that as the last date. The dates will be in ascending order.

Thanks,
Chris

[patjones]

It is certainly possible to write some succinct SQL to accomplish this. In the way that I envision doing it, it would be helpful to have the release information written out in a three-character format, such as 02A, 05B, 09C, 11D, etc. The issue is this...

If you take a particular project's dates and order them top to bottom (newest to oldest) what will happen is that dates like 10D or 11E or 12C will fall to the bottom, because from a string processing standpoint 5B is greater than 10D.

On the other hand, if you write 5B as 05B, it will fall below 10D on the sort; 10D will properly show up as the more recent entry.

Anyway, my idea is to use something in SQL called the "TOP" clause, which allows the user to pick out only the TOP n records from a larger set of records. In theory, if the records are sorted in descending order and you pick out the TOP 1 record for each project number, you should get the most recent date for each project.

Does this make any sense?

Pat

[cmbirk]

Here is my query code. Where would I insert this TOP command? Also, I need to make sure the it is the last release date for each Project ID, not the whole release date column. There will be different release dates for each project ID because they can change and I am tracking the changes.

SELECT *
FROM [Project Data] INNER JOIN Dates ON [Project Data].[Project ID] = Dates.[Project ID]
WHERE ((([Project Data].Status)="Production") AND ((Dates.[Release Date])=[Enter Release:]));

Thanks,
Chris

[patjones]

This query will give you the projects that have the release date typed into the pop-up as their most recent release date. Omitting the WHERE clause would just give you a complete list of most recent release dates versus project.

SELECT [Project Data].*, tblB.Most_Recent_Release
FROM (SELECT Dates.[Project ID], (SELECT MAX([Release Date])                                                 
                                  FROM Dates AS tblA                                             
                                  WHERE tblA.[Project ID] = Dates.[Project ID]) AS Most_Recent_Release 
      FROM Dates 
      GROUP BY Dates.[Project ID]) AS tblB INNER JOIN [Project Data] ON tblB.[Project ID] = [Project Data].[Project ID]
WHERE [Project Data].Status = 'Production' AND tblB.Most_Recent_Release = [Date to Search:];

This will work correctly provided that you format the dates as I indicated in my previous post (i.e. 05B, 09C, etc. as opposed to 5B, 9C...).

What happens here is that the innermost subquery picks out the maximum (most recent) release date for the project determined by the middle subquery. These two queries are said to be correlated. The outermost query simply replaces project ID's with project names using the INNER JOIN.

Let me know how this works for you.

Pat

[cmbirk]

It is certainly possible to write some succinct SQL to accomplish this. In the way that I envision doing it, it would be helpful to have the release information written out in a three-character format, such as 02A, 05B, 09C, 11D, etc. The issue is this...

If you take a particular project's dates and order them top to bottom (newest to oldest) what will happen is that dates like 10D or 11E or 12C will fall to the bottom, because from a string processing standpoint 5B is greater than 10D.

On the other hand, if you write 5B as 05B, it will fall below 10D on the sort; 10D will properly show up as the more recent entry.

Anyway, my idea is to use something in SQL called the "TOP" clause, which allows the user to pick out only the TOP n records from a larger set of records. In theory, if the records are sorted in descending order and you pick out the TOP 1 record for each project number, you should get the most recent date for each project.

Does this make any sense?

Pat

That works perfect. Thanks for the help. It saved me a lot of time.

-Chris

[cmbirk]

The last thing I need to do for this project is create a tester report. I have 3 columns holding different kinds of testers. I need to return the project information for each project id in which at least one of the three columns is not null. That is, I need to report on all projects that have at least one tester assigned. The three fields are [Project Data].[OMNI Tester], [Project Data].[IFT Tester], and [Project Data].[CIT Tester]. I believe this requires a join statement and then an evaluation on not null, but my knowledge of SQL is minimal. Any help would be great.

Thanks,
Chris

[patjones]

Hi Chris,

I happy that the query worked out for you. Just so you know, SQL being what it is, there is very likely another way to do it (which even I am wondering about myself at this moment). For your last problem, there is no joining involved; it is a matter of querying using the condition:

WHERE ([IFT Tester] & [CIT Tester] & [OMNI Tester]) IS NOT NULL

The concatenated expression will return not null as long as at least one of the columns has an entry. Be aware of zero-length strings, which are not equivalent to NULL.

If you are going to continue doing any significant database in future, I recommend picking up as much SQL as possible. Take a look at Learning SQL by Alan Beaulieu; it's a pain-free and enjoyable introduction to SQL with lots of examples and exercises. You'll be required to download MySQL, which is a free SQL platform.

Pat

[NeoPa]

An alternative might be :

SELECT tPD.*
      ,subD.MaxDate

FROM   [Project Data] AS tPD INNER JOIN
    (
    SELECT   [Project ID]
            ,Max([Release Date]) As MaxDate
    FROM     Dates AS tDi
    GROUP BY [Project ID]
    ) AS subD
  ON   tPD.[Project ID]=subD.[Project ID]

[patjones]

An alternative might be :

SELECT tPD.*
      ,subD.MaxDate

FROM   [Project Data] AS tPD INNER JOIN
    (
    SELECT   [Project ID]
            ,Max([Release Date]) As MaxDate
    FROM     Dates AS tDi
    GROUP BY [Project ID]
    ) AS subD
  ON   tPD.[Project ID]=subD.[Project ID]

That's more like what I had in mind, but it just wouldn't come to me.

Chris, this essentially combines my two inner queries into one query. This would be the preferred method as there is no comparison needed on each record.

Pat