I have 2 relational tables.
Parent: Quotations
Child: QuotationRevisions

Currently my query returns all entries, so for example the 1st row of the "Quotations" table contains 8 rows in "QuotationRevisions" (where Q_QuotationID = 1) .
Instead of all 8 rows being returned, I would like it to return only the last entry from the child table i.e. only return row 8. This filter should continue through all results.

Here is my current SQL returning all 8 rows for Q_QuotationID = 1:
SELECT Quotations.Q_QuotationID, QuotationRevisions.QR_Number
FROM Quotations INNER JOIN QuotationRevisions ON Quotations.Q_QuotationID = QuotationRevisions.QR_QuotationID;

Many thanks.

what criteria identifies the last record?

for example does it have a record number in ascending order? Does it have a date or time designation?

From your SQL, I assume it has from this field: QuotationRevisions.QR_QuotationID

I would suggest you try grouping on the field Quotations.Q_QuotationID and then use the aggregate function (I think that's what it's called) "last"

Hi,

Yes QuotationRevisions contains QR_QuotationID to link it to the Quotations table. QuotationRevisions has it's own ID (QR_Number) which I want it to return the highest value only for each Q_QuotationID

At present I get:

http://img.photobucket.com/albums/v368/flemmo/table1.gif

I would like:

http://img.photobucket.com/albums/v368/flemmo/table2.gif

I'm an SQL novice so dont really know where to start.
Thanks

Could you post a sample database (access 2003 format or lower) with some sample data in?

Just make sure your query does a descending sort on QR_Number and then use TOP 1 to display the record you want. See Access help for more info

Hi Rabbie,

I tried the top 1 method, but as I'm after the top 1 on the joined table, I wasn't able to get it working.

Anyway, I went back to basics and used the Query Wizard which gives the following SQL:

SELECT DISTINCTROW Quotations.Q_QuotationID, First(QuotationRevisions.QR_QuotationID) AS [First Of QR_QuotationID], Max(QuotationRevisions.QR_Number) AS [Max Of QR_Number]
FROM Quotations INNER JOIN QuotationRevisions ON Quotations.[Q_QuotationID] = QuotationRevisions.[QR_QuotationID]
GROUP BY Quotations.Q_QuotationID;

Seems to do exactly what I'm after.
I've now got to try and integrate this with my custom search and full query as the above is only a small part of my original query... so I may well be back ;)

Hi all,

I'm still struggling with this problem. I thought I'd cracked it but I hadn't.
I've attached my database for you to have a look at.
If you run "theQuery" you will see if comes back with 8 entries for Q_QuotationsID = 1, each with a QR_Number from 1 to 8. I want to group by Q_QuotationsID and so that only the highest (or last) QR_Number is returned. So where Q_QuotationsID = 1, only QR_QuotationRevision 52 (QR_Number of 8) would be returned.

Thanks for any help you can offer.