That is not the same query as you originally posted. In your original post,
you were returning the LAST uf-set-date value. In this one you seem to be
trying to limit records based on the Max of uf-set-date. And you are no longer
trying to get an average.
Please describe in words what you are attempting to do. The query I posted
should have given you the average price for items. This one will get the
average price and the last date of the price that was used in the calculation
of the average for that item.
SELECT item.item
, item.description
, Avg([ux-itemcust].[uf-retail-price]) AS [AvgOfuf-retail-price]
, item.stat
, Max([ux-itemcust].[uf-set-date]) as LastDate
FROM ([ux-itemcust] INNER JOIN item ON [ux-itemcust].item=item.item) INNER
JOIN tblInventory3 ON item.item=tblInventory3.Item
WHERE [ux-itemcust].[uf-retail-price]>0 AND item.stat)="A"
GROUP BY item.item, item.description, item.stat
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I tried that and no get an aggregate error.
SELECT item.item, item.description, [ux-itemcust].[uf-retail-price], item.stat
FROM ([ux-itemcust] INNER JOIN item ON [ux-itemcust].item = item.item) INNER
JOIN tblInventory3 ON item.item = tblInventory3.Item
WHERE ((([ux-itemcust].[uf-set-date])=Max("uf-set-date")) AND
(([ux-itemcust].[uf-retail-price])>0) AND ((item.stat)="A"))
GROUP BY item.item, item.description, [ux-itemcust].[uf-retail-price],
item.stat, tblInventory3.Item;
John Spencer said:
Use MAX not last. First and Last will more or less give you a value
from a random record in the group - which ever record the query happens
to access first or last. That has nothing to do with the order the
records were entered or with the values in the fields.
Max will give you the Biggest (latest) date. Min will give you the
smallest (earliest) date.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
Rpettis31 wrote:
That works great, however I discovered there is a date field I can pull to
give me the last price however, when I use Last its not working.
SELECT item.item, item.description, [ux-itemcust].[uf-retail-price],
item.stat, Last([ux-itemcust].[uf-set-date]) AS [LastOfuf-set-date]
FROM ([ux-itemcust] INNER JOIN item ON [ux-itemcust].item=item.item) INNER
JOIN tblInventory3 ON item.item=tblInventory3.Item
WHERE ((([ux-itemcust].[uf-retail-price])>0) AND ((item.stat)="A"))
GROUP BY item.item, item.description, [ux-itemcust].[uf-retail-price],
item.stat, tblInventory3.Item;
:
You can't include the price if you want to get the average of the price. If
you do include the price in the group by and select clauses then the average
is going to be for that one price since the price is part of what determines
the group.
I think you want the following:
SELECT item.item
, item.description
, Avg([ux-itemcust].[uf-retail-price]) AS [AvgOfuf-retail-price]
, item.stat
FROM ([ux-itemcust] INNER JOIN item ON [ux-itemcust].item=item.item) INNER
JOIN tblInventory3 ON item.item=tblInventory3.Item
WHERE [ux-itemcust].[uf-retail-price]>0 AND item.stat)="A"
GROUP BY item.item, item.description, item.stat, tblInventory3.Item
Notice that uf-Retail-price is in the where clause where it is used to exclude
prices of zero from the calculation; however, it is not in the group by or
select clause other than being used by the aggregate function Avg.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Rpettis31 wrote:
I am trying to get the average retail price in a query from a table that has
the same item listed 3 or 4 times but sold to different customers. The end
result I thought I would get was item A list once with the average of the
retail price but my query does not seem to work this way.
SELECT item.item, item.description, Avg([ux-itemcust].[uf-retail-price]) AS
[AvgOfuf-retail-price], item.stat, [ux-itemcust].[uf-retail-price] AS
RetailPrice
FROM ([ux-itemcust] INNER JOIN item ON [ux-itemcust].item=item.item) INNER
JOIN tblInventory3 ON item.item=tblInventory3.Item
GROUP BY item.item, item.description, item.stat,
[ux-itemcust].[uf-retail-price], tblInventory3.Item
HAVING (((Avg([ux-itemcust].[uf-retail-price]))>0) AND ((item.stat)="A"));
