473,323 Members | 1,550 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Query on the last element in a subtable

18
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
Jul 21 '10 #1

✓ answered by 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.

Expand|Select|Wrap|Line Numbers
  1. SELECT [Project Data].*, tblB.Most_Recent_Release
  2. FROM (SELECT Dates.[Project ID], (SELECT MAX([Release Date])                                                 
  3.                                   FROM Dates AS tblA                                             
  4.                                   WHERE tblA.[Project ID] = Dates.[Project ID]) AS Most_Recent_Release 
  5.       FROM Dates 
  6.       GROUP BY Dates.[Project ID]) AS tblB INNER JOIN [Project Data] ON tblB.[Project ID] = [Project Data].[Project ID]
  7. WHERE [Project Data].Status = 'Production' AND tblB.Most_Recent_Release = [Date to Search:];
  8.  

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

10 2401
patjones
931 Expert 512MB
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
Jul 21 '10 #2
cmbirk
18
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
Jul 21 '10 #3
patjones
931 Expert 512MB
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
Jul 21 '10 #4
cmbirk
18
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM [Project Data] INNER JOIN Dates ON [Project Data].[Project ID] = Dates.[Project ID]
  3. WHERE ((([Project Data].Status)="Production") AND ((Dates.[Release Date])=[Enter Release:]));
  4.  
Thanks,
Chris
Jul 21 '10 #5
patjones
931 Expert 512MB
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT [Project Data].*, tblB.Most_Recent_Release
  2. FROM (SELECT Dates.[Project ID], (SELECT MAX([Release Date])                                                 
  3.                                   FROM Dates AS tblA                                             
  4.                                   WHERE tblA.[Project ID] = Dates.[Project ID]) AS Most_Recent_Release 
  5.       FROM Dates 
  6.       GROUP BY Dates.[Project ID]) AS tblB INNER JOIN [Project Data] ON tblB.[Project ID] = [Project Data].[Project ID]
  7. WHERE [Project Data].Status = 'Production' AND tblB.Most_Recent_Release = [Date to Search:];
  8.  

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
Jul 21 '10 #6
cmbirk
18
@zepphead80
That works perfect. Thanks for the help. It saved me a lot of time.

-Chris
Jul 22 '10 #7
cmbirk
18
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
Jul 22 '10 #8
patjones
931 Expert 512MB
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:

Expand|Select|Wrap|Line Numbers
  1. 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
Jul 22 '10 #9
NeoPa
32,556 Expert Mod 16PB
An alternative might be :
Expand|Select|Wrap|Line Numbers
  1. SELECT tPD.*
  2.       ,subD.MaxDate
  3.  
  4. FROM   [Project Data] AS tPD INNER JOIN
  5.     (
  6.     SELECT   [Project ID]
  7.             ,Max([Release Date]) As MaxDate
  8.     FROM     Dates AS tDi
  9.     GROUP BY [Project ID]
  10.     ) AS subD
  11.   ON   tPD.[Project ID]=subD.[Project ID]
Jul 22 '10 #10
patjones
931 Expert 512MB
@NeoPa
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
Jul 22 '10 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

20
by: xerix | last post by:
I'm trying to make a function which insert an object into a list and returns an iterator to the new element in the list. But as far as I know there is no function which returns an iterator to the...
57
by: pinkfloydhomer | last post by:
Isn't there an easier way than lst = ... ?
2
by: Christos via AccessMonster.com | last post by:
Dear friends I would like to find the first and the last element of a record in a table. Is there any possibility especially if yoy have not promary key? Christos -- Message posted via...
5
by: junky_fellow | last post by:
Hi, I discussed about this earlier as well but I never got any satisfactory answer. So, I am initiating this again. Page 84, WG14/N869 "If both the pointer operand and the result point to...
11
by: Bob Rock | last post by:
Hello, I have an array of strings and need to find the matching one with the fastest possible code. I decided to order the array and then write a binary search algo. What I came up with is the...
7
by: artistlikeu | last post by:
Dear all, i have a column with data type array with variable size ...... e.g number = i want to access the last element of this array....... i accessed the first element by following...
11
by: desktop | last post by:
How do I find the last element in the list numsx defined below? int* myfind(int* arr_start, int* arr_end, int& s) { int not_found = 666; int* result = &not_found; while (arr_start !=...
16
by: Juha Nieminen | last post by:
I'm actually not sure about this one: Does the standard guarantee that if there's at least one element in the data container, then "--container.end()" will work and give an iterator to the last...
12
by: Howard | last post by:
Is there an easy way to get an iterator (*not* a reverse-iterator) to the last element in a list? The last() function returns the element itself, not an iterator. Thanks, -Howard
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.