Search results for query: *

I have worked out what the problem is... The first line of code should have been SELECT SUM(table_x.variable_z) AS 'Total' rather than SELECT table_x.variable_z

WillRiley: If I exclude the GROUP BY statement, I get the right number of records for the criteria table_x.variable_y='A'...I am just at a loss to show those records aggregated by variable_z as well. How do I set up aggregate functions? 'Help' is no use..

Hi, If I have the following code in a SQL statement in MS Query: ...WHERE table_x.variable_y='A' GROUP BY table_x.variable_z.... and run it, it appears to ignore the first line and groups the entire content of table_x rather than grouping the content of table_x and restricting the grouped...

Hi, If I have the following code in a SQL statement in MS Query: ...WHERE table_x.variable_y='A' GROUP BY table_x.variable_z.... and run it, it appears to ignore the first line and groups the entire content of table_x rather than grouping the content of table_x and restricting the grouped...

Hi, I am attempting to extract the sum of the absolute values of table1.variable1 in MS Query. i.e. i would like to this... SELECT (Sum(ABS(table1.variable1))) FROM table1 WHERE table1.variable1 <=0 but there is no built-in function that returns absolute numbers from a list of records. I...

Hi, I am using the following code to calculate the size of a data list in Excel, and name the range of cells... end_row = Range(&quot;A65000&quot;).End(xlUp).Row Sheets(9).Range(Cells(2, 1), Cells(end_row, 9)).Select Sheets(9).Names.Add Name:=&quot;test&quot;, RefersToR1C1:=&quot;='ECP...

Hi, I am currently using the GUI on Query to sort data from a server. However, I am having a problem trying to sort the following..(===) denotes link Table 1 Table 2 product_num======product_num type (A,B) data (x,y,z) Currently, I am sorting the data according to the...

Hi, Don't know if this is a stupid quesiton, but here goes: If I want the highest number from a list, I would use =MAX(Ax:Ay). However, how do I get Excel to extract the 2 highest numbers from the same list?? Or the 3 highest etc. Thanks

?? not sure what you mean - I got it to work by using PivotTableWizard rather than PivotCaches (which the recorder will generate in Excel 2000) If you look at my code in my last post it is using the PivotTableWizard method. Ross

I got it to work last night using the code in Geoff's second post. It is (works for Excel 2000): Dim srcRng As Range Set srcRng = Sheets(&quot;worksheet&quot;).Range(Cells(1,2), Cells(x,4)) ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=srcRnga...

Hi, Does anyone know how to link dates in an Excel spreadsheet to criteria in Query? For any other criteria, i.e. names or constants, it is simple - in Query, in the criteria field of the chosen data category put in [name], then in Excel link the criteria by selecting 'Query Parameters' and...

Unfortunately, I get the error message &quot;Application-defined or Object-Defined Error&quot; using the code: set srcRng = Sheets(&quot;Sheetname&quot;).Range(Cells(x,y),Cells(xx,yy)) And if I replace Range(Cells(x,y),Cells(xx,yy)) with Range(&quot;A2:D25&quot;), I then get the error message...

Hi, I am trying to write a macro that will create a pivot table from a list in an excel worksheet. The list will change in size every time I want the macro run. Range(&quot;B1:D25&quot;).Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=...