If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Aggregate Error
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")); |
#2
|
|||
|
|||
Aggregate Error
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" wrote: 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")); |
#3
|
|||
|
|||
Aggregate Error
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" wrote: 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" wrote: 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")); |
#4
|
|||
|
|||
Aggregate Error
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 Rpettis31 wrote: 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" wrote: 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" wrote: 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")); |
#5
|
|||
|
|||
Aggregate Error
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" wrote: 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 Rpettis31 wrote: 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" wrote: 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" wrote: 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")); |
Thread Tools | |
Display Modes | |
|
|