Aggregate Error

  • Thread starter Thread starter Rpettis31
  • Start date Start date
R

Rpettis31

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"));
 
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;
 
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


Rpettis31 said:
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"));
Click to expand...
Click to expand...
 
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


Rpettis31 said:
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"));
Click to expand...
Click to expand...
Click to expand...
 
This information is from the ERP system at my company and I get very
frustrated with the nomenclature of the fields and the "-" as you point out
quite frequently.

I struggle with SQL and quering with any of my additional projects with this
data source. The record set should only be about a 1000 max, if it is that
on a regular basis so I will use your second option.

Thanks very much for your help.

John Spencer said:
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"));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top