>I have two related tables. I have designed a view that shows me a set of records based on a certain FILTER condition. This works fine. The returned records are equipment serial#s, a closeout date, and other info. I would like the view to somehow group the records so only the most recent ( based on closeout date ) are displayed for each serial number. Each serial number might have multiple closeout dates representing different repairs over time.

>Can I do this with the View Designer ?

No, the View Designer can't do this for you. It can only do the most basic queries.

I just answered a question similar to this on another thread in this section a few minutes ago. He wanted to know how to do the same thing with Customers and Invoices (finding the invoice with the max invoice date for each customer). You can substitute CustNo with Serial# and InvDate with CloseOut Date.

Here's what I posted to him:

SELECT ;
  Customer.CustNo,;
  Customer.Name,;
  Invoice.InvNo,;
  Invoice.InvDate,;
  Invoice.InvAmt ;
FROM ;
  Customer ;
  JOIN Invoice on Customer.CustNo=Invoice.CustNo ;
WHERE ;
  DTOS(Invoice.InvDate)+STR(Invoice.InvNo,6) = ;
    (SELECT MAX(DTOS(InvTemp.InvDate)+STR(InvTemp.InvNo,6)) ;
      FROM Invoice InvTemp WHERE InvTemp.CustNo=Invoice.CustNo) ;
INTO ;
  CURSOR result

Note how the inner sub-query connects to the customer on the outside main query.

Also, the reason I combined the Invoice Date and Invoice Number is because there may be more than one invoice on the same date for the same customer. I'm also assuming your Invoice Number field is numeric, thus the STR(,6) stuff.

(If this message solved your problem, please click on the "Mark this message as the answer to the thread" hyperlink below... Thanks).

--Brad