So you want the last price that was charged for an item. To do this you are
going to have to stack queries because your field and table names contains
dashes. Field and table names should consist of ONLY Letters, Numbers, and
the underscore character. If you don't do that then you are forced to use []
to surround the names and you cannot (usually) use a sub-query in the from clause.
First Query - saved as qItemLast
SELECT [ux-itemcust].item
, Max([ux-itemcust].[uf-set-date]) as LastDate
FROM [ux-itemcust]
Next query using your tables and the query qItemLast
SELECT I.Item
, I.Description
, Q.LastDate
, C.[uf-Retail-Price] as RetailPrice
FROM ([ux-itemcust] as C INNER JOIN qItemLast as Q
ON C.Item = Q.Item and C.[uf-set-date] = Q.LastDate)
INNER JOIN Item as I
ON I.Item = Q.Item
WHERE I.Stat = "A"
Another possibility is to use a correlated sub-query in the where clause.
This is slower with LARGE sets of data
SELECT Item.Item
, Item.Description
, Item.Stat
, [uf-retail-Price] as RetailPrice
, [uf-set-Date] as AsOfDate
FROM Item INNER JOIN [ux-itemcust]
ON Item.Item = [ux-itemcust].Item
WHERE [uf-set-date] =
(SELECT Max([uf-set-Date]) FROM [Ux-itemcust] as Temp
WHERE Temp.Item =[ux-itemcust].item)
AND Item.Stat = "A"
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I listed the wrong sql, I am trying to list for a single item what the last
pricing date was this statment does not show the last item by the date.
SELECT item.item, item.description, [ux-itemcust].[uf-retail-price],
Max([ux-itemcust].[uf-set-date]) AS [MaxOfuf-set-date]
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, [ux-itemcust].[uf-retail-price],
tblInventory3.Item;
Duane Hookom said:
Try:
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
WHERE item.stat="A"
GROUP BY item.item, item.description, item.stat,
[ux-itemcust].[uf-retail-price]
HAVING Avg([ux-itemcust].[uf-retail-price])>0;
--
Duane Hookom
Microsoft Access MVP
:
I am getting an aggregate message I am trying to pull the records of the
latest retail price by the date. Not sure what I am doing wrong.
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"));
