.First

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query from MAINTABLE with two joins to children tables, print
records where >= Date() - 30

so for the first customer 6 reocords might print out becuase customer1 might
have 3 children in tabel2 and 8 children in tABLE 2


All I need is for the FIRST record to print for each customer.....the other
"children records are not necessary and just take up space. How can I get
the query to output just the first instance of each customer and joined data?
 
This was a good try, but that returns the very first record of the very first
customer, but not the first record of every [parent] customer.

Maybe it is a special kind of join statement

cust1 fieldA fieldB childfieldA childfieldB < ------- I want this one
cust1 fieldA fieldB childfieldA childfieldB
cust1 fieldA fieldB childfieldA childfieldB
cust1 fieldA fieldB childfieldA childfieldB
cust2 fieldA fieldB childfieldA childfieldB < ------- I want this one
cust2 fieldA fieldB childfieldA childfieldB
cust2 fieldA fieldB childfieldA childfieldB
cust3 fieldA fieldB childfieldA childfieldB< ------- I want this one
cust3 fieldA fieldB childfieldA childfieldB
cust4 fieldA fieldB childfieldA childfieldB< ------- I want this one
cust4 fieldA fieldB childfieldA childfieldB
cust5 fieldA fieldB childfieldA childfieldB< ------- I want this one
cust5 fieldA fieldB childfieldA childfieldB
cust5 fieldA fieldB childfieldA childfieldB
cust5 fieldA fieldB childfieldA childfieldB
cust6 fieldA fieldB childfieldA childfieldB
cust6 fieldA fieldB childfieldA childfieldB< ------- I want this one
cust6 fieldA fieldB childfieldA childfieldB
 
mark said:
I have a query from MAINTABLE with two joins to children tables, print
records where >= Date() - 30

so for the first customer 6 reocords might print out becuase customer1 might
have 3 children in tabel2 and 8 children in tABLE 2


All I need is for the FIRST record to print for each customer.....the other
"children records are not necessary and just take up space. How can I get
the query to output just the first instance of each customer and joined data?
Click to expand...


If you don't want the data from the child records, then you
probably should not be joining the child tables. Maybe you
can get by with a subquery in the Where clause?
 
There is a fundamental problem with your request. There
really is no such thing a "first" record, unless you have a
well defined sort order. Your example in another post did
not indicate such a thing so I think maybe it doesn't matter
which child record you get.

OTOH, maybe your original query is sorted by some field. If
so you can use another query:

SELECT cust1, fieldA, fieldB,
First(childfieldA), First(childfieldB)
FROM yourquery
GROUP BY cust1, fieldA, fieldB
 
Back
Top