Sql.request for GROuP BY

[Rama]

Hi Guys,

The statement below when using SQL.request ( on excel
cells and not VBA) works fine. It returns all the data ,
1) However, now I want to GROUP BY TerrCode and not as now
where it repeats the terrcode each time,I just cannot do
it . Pls help
2) Also the Date returns 37626 , looks award , how can I
give the real date format of 5/01/2003 as type


DSN=MS Access
Database;DBQ=S:\rcamatchee\donthula\FinalHongKongDatabase;D
riverId=281;FIL=MS Access
="SELECT DISTINCT TerrCode,Category_Code, Salevalue2003,
Category_Name, Territory FROM S12003DataRama WHERE (Date

"&A6&") AND (Date < "&B6&")"

5/01/2003 10/01/2003 <<Parameter Type in A6 and B6

=SQL.REQUEST($A$3,,,$A$4)

 

[Robin Hammond]

Rama,

this is a group by example extracted from one of my routines running on SQL,
not Access.

strSQL = "SELECT Max(ShortName) as ShortName, Max(ItemDescription) as
ItemDescription, Max(ItemIndex) as ItemIndex, Count(ShortName) as ItemCount,
Max(UploadType) as UploadType FROM " & strGetFunctionItems & " WHERE
(Industry IN ("A","B","C") AND IsSeries = " & cBit(bTimeSeries) & ") GROUP
BY ShortName ORDER BY ItemIndex"

So this might return something meaningful...

strSQL = "SELECT Max(TerrCode) as TerrCode, Max(Category_Code) as CatCode,
Max(Salevalue2003) as Sales2003,
Max(Category_Name) as CatName, Max(Territory) as Territory FROM
S12003DataRama WHERE (Date

"&A6&") AND (Date < "&B6&")" GROUP BY TerrCode

You probably don't just want max functions though. You can look at Min,
count, Sum, avg, etc to get what you need.

It's always difficult to get these right without access to the underlying
data, but I hope I've given you a clue. One way to start is to try and
create a query in Access that does what you want for a single parameter and
date, then look at the SQL view of the query.

Robin Hammond
www.enhanceddatasystems.com

 

[Patrick Molloy]

Excel saves dates a Long... well actually Double . The
integer is the number of days since 30-Dec-1899
the decimal is the time of day, so 37626.5 is 12 noon on
5/1
Sending the date to SQL you need to format them

dim Date1 as string
dim Date2 as string

Date1 = format$(Range("A6"),"dd-mmm-yy")
Date2 = format$(Range("B6"),"dd-mmm-yy")


your SQL should aim to look something like this...


SELECT TerrCode,Category_Code,SUM(Salevalue2003),
Category_Name, Territory FROM S12003DataRama
WHERE
(Date > '" & Date1 & "' AND "(Date < '" & Date2 & "')"
GROUP BY TerrCode,Category_Name, Territory

note the single quotes around the dates...you script
should look like this

....WHERE (Date < '24-Jan-04' AND Date > '5-Jan-04')


HTH
Patrick Molloy
Microsoft Excel MVP

 

[onedaywhen]

Robin,
What do you mean by, "running on SQL, not Access". MS Access does
contain an implementation of SQL which isn't ANSI standard. However,
the SQL your strSQL variable will contain appears to be both ANSI and
MS Access compliant. Am I missing something?

--