It's possible, provided you are only returning one customer (determined by
the [Enter Customer:] parameter in the example below). You can use a union
operation to return one row with the customer name and lowest item value and
the remaining rows with a NULL and the Item values where the latter is
greater then the lowest value (the values can be text of course, not
necessarily numbers as in your example). This is determined using a subquery
correlated on the Customer column, with the table being given the aliases O1
and O2 (I've assumed the table name is Orders for thisexample) to distinguish
the two instances of the table:
SELECT Customer, MIN(Item) As ItemOrdered
FROM Orders
WHERE Customer = [Enter Customer:]
GROUP BY Customer
UNION ALL
SELECT NULL, Item
FROM Orders AS O1
WHERE Customer = [Enter Customer:]
AND Item >
(SELECT MIN(Item)
FROM Orders AS O2
WHERE O2.Customer = O1.Customer)
ORDER BY ItemOrdered;
However a far simpler and better way is to just use a simple query as the
RecordSource for a report. Either group the report by Customer and put the
Customer control in the group header and the Item control in the detail
section, positioned to the right of the Customer control. In the group
header section's Format event procedure put the following code:
' set MoveLayout property False to make Customer
' name display on same line as first item
MoveLayout = False
Or leave the report ungrouped but sorted (in the report not in the
underlying query) by customer then item, put everything in the detail section
and suppress the duplicate Customer names by setting the Customer control's
HideDuplicates property to True.
A report, unlike the union query, will handle multiple customers, heading
each set of item values with the relevant customer name.
Ken Sheridan
Stafford, England
