I only want the LAST child in a one-to many parent child relationship.

[Seamus59]

Here's a puzzler! I've two tables in a one-to-many relationship, joined by ID=TranID. The child table has data that accumulates monthly. I've been asked to provide a report with the parent table and only the LAST entry (by date) of monthly data.



I've got a few ideas, but none seem elegant. I'm sure someone else has probably done this, although I couldn't find anything when I searched the forum. If someone has a suggestion or idea, it would be awesome.

[Seamus59]

This seems to grab the latest test data of any particular item (293 in this case) in the parent field:

SELECT TOP 1 [Transformer Test Data].TestDate, [Transformer Test Data].[Tran ID]
FROM [Transformer Test Data]
WHERE ((([Transformer Test Data].[Tran ID])=293))
ORDER BY [Transformer Test Data].TestDate DESC;

Now if I can figure out how to go through the entire parent table while selecting this in the child table.

[amrut]

You posted when I was replying to first post
Try -

SELECT TOP 1 [Transformer Test Data].TestDate, [Transformer Test Data].[Tran ID]
FROM [Transformer Test Data]
ORDER BY [Transformer Test Data].TestDate DESC;

You can use both master and child tables in query.

[Seamus59]

Thanks, yes. now do you know how to construct a query that uses that as one of it's citeria?


and the overall query could look something like this:

 SELECT [Transformer List].ID, [Transformer Test Data].[Tran ID], [Transformer Test Data].TestDate
FROM [Transformer List] LEFT JOIN [Transformer Test Data] ON [Transformer List].ID = [Transformer Test Data].[Tran ID]
AND [Transformer Test Data].[Tran ID] = (SELECT TOP 1 [Transformer Test Data].TestDate, [Transformer Test Data].[Tran ID]
FROM [Transformer Test Data]
WHERE ((([Transformer Test Data].[Tran ID])= [Transformer List].ID))
ORDER BY [Transformer Test Data].TestDate DESC);

unfortuanately, I don't have the syntax going in the right direction....Hmmmmm

[amrut]

does this works ?

SELECT TOP1 [Transformer List].ID, [Transformer Test Data].[Tran ID], [Transformer Test Data].TestDate
FROM [Transformer List] LEFT JOIN [Transformer Test Data] ON [Transformer List].ID = [Transformer Test Data].[Tran ID]
ORDER BY [Transformer Test Data].TestDate DESC;

[Seamus59]

It is a-l-m-o-s-t there, thanks, but it only returns all the parents whose dates equal the latest date, not the latest dates for each parent.....getting closer!

[amrut]

This should work

SELECT [Transformer List].ID, [Transformer Test Data].[Tran ID], Max([Transformer Test Data].TestDate) AS MaxOfTestDate FROM [Transformer List] 
LEFT JOIN [Transformer Test Data] ON [Transformer List].ID = [Transformer Test Data].[Tran ID] 
GROUP BY Transformer List.ID 
ORDER BY [Transformer Test Data].TestDate;

[Seamus59]

I hadn't thought of using the MAX function, very good suggestion. With a slight modification it works. It only provides the Trans ID and latest date, so I save that query as MaxofTestDates, create an INNER JOIN between that and my original parent-child tables and Voila!!!

SELECT [Transformer Test Data].[Tran ID], [Transformer List].Equipment, [Transformer List].Description, [Transformer Test Data].TestDate, [Transformer Test Data].[VPM_Oil Temperature], [Transformer Test Data].[VPM_Oil Pressure]
FROM MaxofTestDates INNER JOIN ([Transformer List] INNER JOIN [Transformer Test Data] ON [Transformer List].ID = [Transformer Test Data].[Tran ID]) ON (MaxofTestDates.[Tran ID] = [Transformer Test Data].[Tran ID]) AND (MaxofTestDates.MaxOfTestDate = [Transformer Test Data].TestDate);

and MaxofTestDates was your previous query with a minor modification.

SELECT [Transformer Test Data].[Tran ID], Max([Transformer Test Data].TestDate) AS MaxOfTestDate
FROM [Transformer List] LEFT JOIN [Transformer Test Data] ON [Transformer List].ID = [Transformer Test Data].[Tran ID]
GROUP BY [Transformer Test Data].[Tran ID];

Thanks!


Looks wierd, but works!