Distinct values from hiarachical tables

  • Thread starter Thread starter JensB
  • Start date Start date
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
 
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
 
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...
 
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.

--
 
Back
Top