If you think about it logically, how could it give you the last name
information? You're telling it to group by the item code and give you the
minimum price of each. Fine. Say there are three prices for a particular
item code. The result is one line with the item code and the minimum price
of the three. Fine. Now you want the last name. Which last name does it give
you? There are three of them. How can it know which one you want?
You may say, "I want the one that's associated with the minimum price." OK;
but what if there are two last names that have the same price and both are
the minimum price. Which last name would it use?
Consider the following:
ItemCode: 1
LastName: Smith
Price: 100.00
ItemCode:1
LastName: Jones
Price: 100.00
ItemCode:1
LastName: Peters
Price: 200
In your current query, the line for item code 1 will read as follows:
ItemCode MinPrice
1 100.00
Now you want to add the last name. Which one does it use, Smith or Jones?
Hence the error.
The answer is either to group by the last name (which will ruin your item
code group by, as you found out), or create two queries -- one to get the
minimum price, and the other to get the name (in SQL Server you can do this
with one query, but in Access you'd need to use a stored subquery).
So, you would keep your first query the way it is. Call it Query1. Then
create a new query with the following SQL:
SELECT itemlist.ItemCode, itemlist.Price, First(itemlist.Name) AS Name
FROM itemlist INNER JOIN Query1 ON (itemlist.Price = Query1.MinPrice) AND
(itemlist.ItemCode = Query1.ItemCode)
GROUP BY itemlist.ItemCode, itemlist.Price
That would give you the first name in the case that there is more than one
with the same minimum price.
Neil
"Danny" <da********@hotmail.com> wrote in message
news:N_**********************@news4.srv.hcvlny.cv. net...
I have this bit of code:
SELECT itemcode, min(Price) AS minprice
FROM itemlist
GROUP BY itemcode;
it lists the minimum price for each itemcode group. It works fine.
But when I want to see the last name of the person who bought this item,
it
give me that aggregate error saying the lastname is not part of the
aggregate function.
BUt that is silly, because if I put it in the group by, then the function
does not work.
SELECT lastname, itemcode, min(Price) AS minprice
FROM itemlist
GROUP BY itemcode;
<< this gives an error
SELECT lastname, itemcode, min(Price) AS minprice
FROM itemlist
GROUP BY itemcode, lastname;
this will not group by the itemcode only which I need, it groups but
itemcode and lastname
what do I do?
