You can clean up the output column names --
Category_Max
SELECT [Lot Info].Category, Max([Lot Info].Price) AS MaxOfPrice
FROM [Lot Info]
GROUP BY [Lot Info].Category;
Category_Max_Detail
SELECT [Lot Info_1].Category, Sum([Lot Info_1].Price) AS SumOfPrice,
Avg([Lot Info_1].Price) AS AvgOfPrice, Count([Lot Info_1].Price) AS
CountOfPrice, [Current Buyer Info].[B Consultant], [Lot Info].Category, [Lot
Info].[Lot No], [Lot Info].[Lot Name], [Lot Info].Price
FROM [Lot Info] AS [Lot Info_1] INNER JOIN (([Lot Info] INNER JOIN [Current
Buyer Info] ON [Lot Info].[Buyer No] = [Current Buyer Info].[Buyer No]) INNER
JOIN Category_Max ON ([Lot Info].Price = Category_Max.MaxOfPrice) AND ([Lot
Info].Category = Category_Max.Category)) ON [Lot Info_1].Category = [Lot
Info].Category
GROUP BY [Lot Info_1].Category, [Current Buyer Info].[B Consultant], [Lot
Info].Category, [Lot Info].[Lot No], [Lot Info].[Lot Name], [Lot Info].Price;
--
KARL DEWEY
Build a little - Test a little
PAR said:
SELECT [Lot Info].[Lot No], [Lot Info].Category, [Lot Info].[Lot Name], [Lot
Info].[Buyer No], [Lot Info].Price, [Current Buyer Info].[B Consultant]
FROM [Current Buyer Info] INNER JOIN [Lot Info] ON [Current Buyer
Info].[Buyer No] = [Lot Info].[Buyer No];
karl dewey said:
Post the SQL of the query that feeds your report.
--
KARL DEWEY
Build a little - Test a little
PAR said:
It is not a sql statement, it is a calculation in an unbound field in the
group footer.
The report is a summary report, grouped by category, listing average price,
highest price, total sales for the category. I also want to list the details
for the highest selling lot, but just the highest selling lot.
:
Post the complete SQL you now have.
--
KARL DEWEY
Build a little - Test a little
:
I have an auction sale summary report that shows the total for each sale
category, the average and the maximum sale price. What I would also like to
include is the item details for the maximum sale priced item.
I tried this, but it does not work
=IIf(Max([Price]),([Lot No] & ' - ' & [Lot Name]),Null)
Thanks in advance for your help
