Distinct values from hiarachical tables

J

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
 
M

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
 
J

JensB

Hi Michel

Thanks that did the job

JensB

Michel Walsh said:
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
Click to expand...
 
J

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..
Click to expand...

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.

--
 
Top