Distinct values from hiarachical tables

[JensB]

Hi

I got 3 related tables: Quotehead, Quotedetails and QuoteItems :
I want to make a Distinct list of all rows from Quoteitems column
"Description" and in the same recordset also include "quotedate" column from
the QuoteHead table.
Is it posible at all?

JensB

 

[Michel Walsh]

Something like:

SELECT Quoteitems.description, MAX(QuoteHead.quotedate)
FROM Quoteitems INNER JOIN QuoteHead ON
QuoteItems.ID = QuoteHead.ID
GROUP BY Quoteitems.description



Note that I chose, for you, to use the MAX value of quoteHead for each
distinct quoteItems, but you can use MIN..


I also assumed the field ID was common to QuoteItems and to QuoteHead and is
the field to be used to indicate a "match" of rows, between the two tables.



Vanderghast, Access MVP

 

[JensB]

Hi Michel

Thanks that did the job

JensB

Something like:

SELECT Quoteitems.description, MAX(QuoteHead.quotedate)
FROM Quoteitems INNER JOIN QuoteHead ON
QuoteItems.ID = QuoteHead.ID
GROUP BY Quoteitems.description



Note that I chose, for you, to use the MAX value of quoteHead for each
distinct quoteItems, but you can use MIN..


I also assumed the field ID was common to QuoteItems and to QuoteHead and
is the field to be used to indicate a "match" of rows, between the two
tables.



Vanderghast, Access MVP

 

[Jamie Collins]

Something like:

SELECT Quoteitems.description, MAX(QuoteHead.quotedate)
FROM Quoteitems INNER JOIN QuoteHead ON
        QuoteItems.ID = QuoteHead.ID
GROUP BY Quoteitems.description

Note that I chose, for you, to use the MAX value of quoteHead for each
distinct quoteItems, but you can use MIN..

Assuming ADO is still alive and well (!!), here's an alternative which
avoids picking a set function (MAX, MIN, etc):

SHAPE {SELECT ID, description FROM Quoteitems} AS chapQuoteitems
APPEND ({SELECT ID, quotedate FROM QuoteHead} AS chapQuoteHead
RELATE ID TO ID)

Jamie.

--