Query Question - Multiple passes

rhawk · Jul 27, 2010

I am using the following query to get data, between 2 dates, from a database.

----------------------------
****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CRHAWKINS%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5CRHAWKINS%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5CRHAWKINS%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style>  </style> [FONT=&quot]Worksheets("queryData").Select<o

></o

>[/FONT]
[FONT=&quot]<o

> </o

>[/FONT]
[FONT=&quot]With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _<o

></o

>[/FONT]
[FONT=&quot]"ODBC;DSN=hist;UID=OSI;PWD=OSIOSI;DBQ=HIST;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllS uccessful;MTS=F;MDI=F;" _<o

></o

>[/FONT]
[FONT=&quot]), Array("CSR=F;FWC=F;PFC=10;TLO=0;")), _<o

></o

>[/FONT]
[FONT=&quot]Destination:=Range("$A$1")).QueryTable<o

></o

>[/FONT]
[FONT=&quot].CommandText = Array( _<o

></o

>[/FONT]
[FONT=&quot]"SELECT " & filetime & "," _<o

></o

>[/FONT]
[FONT=&quot], _<o

></o

>[/FONT]
[FONT=&quot]"" & filekey & "," _<o

></o

>[/FONT]
[FONT=&quot], _<o

></o

>[/FONT]
[FONT=&quot]"" & fileValue1 & "" & Chr(13) & "" & Chr(10) & "" _<o

></o

>[/FONT]
[FONT=&quot], _<o

></o

>[/FONT]
[FONT=&quot]"FROM " & filevalue2 & "" & Chr(13) & "" & Chr(10) & " " _<o

></o

>[/FONT]
[FONT=&quot], _<o

></o

>[/FONT]
[FONT=&quot]"WHERE " & FileType & " and " & timeinterval & "" _<o

></o

>[/FONT]
[FONT=&quot], _<o

></o

>[/FONT]
[FONT=&quot]"AND (" & MW & " or " & MVAR & ") " _<o

></o

>[/FONT]
[FONT=&quot], _<o

></o

>[/FONT]
[FONT=&quot]"order by " & filetime & " , " & filekey & " " _<o

></o

>[/FONT]
[FONT=&quot])<o

></o

>[/FONT]

--------------------------------
After the user enters the start and stop date the following input query parameters are generated:

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CRHAWKINS%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5CRHAWKINS%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5CRHAWKINS%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style>  </style> [FONT=&quot]filetime = DATA_Values_5Min_5_2010.Time<o

></o

>[/FONT]
[FONT=&quot]filekey = DATA_Values_5Min_5_2010.OSI_KEY<o

></o

>[/FONT]
[FONT=&quot]filevalue1 = DATA_Values_5Min_5_2010.Value<o

></o

>[/FONT]
[FONT=&quot]filevalue2 = OSI.DATA_Values_5Min_5_2010 DATA_Values_5Min_5_2010<o

></o

>[/FONT]
[FONT=&quot]filetype = DATA_Values_5Min_5_2010.DATA_Type=1<o

></o

>[/FONT]
[FONT=&quot]mydatetime = 04/30/2010 <o

></o

>[/FONT]
[FONT=&quot]datepick = {ts '2010-04-30 00:00:00'}<o

></o

>[/FONT]
[FONT=&quot]mydatetimeend = 05/31/2010 <o

></o

>[/FONT]
[FONT=&quot]datepickend = {ts '2010-05-31 00:00:00'}[/FONT]

The way it is written works fine if the start and stop date is in the same month because the tables hold monthly data. So i you enter 5/1/2010 to 5/6/2010 you would get the correct data from the 5_2010 data values. The data is recorded every 5 minutes. If you enter 5/22/2010 to 6/15/2010 you would only get data for 5/22 to 5/31.

[FONT=&quot]<o

></o

>[/FONT]
How would i get data for multiple months? Would i have to loop for each month that is entered?
Also, since the data is every 5 minutes the query returns a lot of rows. Is there a way to have the query group/avg data over a certain period of time - i.e. every 15 or 30 minutes

rhawk · Jul 27, 2010

sorry. i entered this message twice and do not know how to delete one of them....

Query Question - Multiple passes

rhawk

Board Regular

Excel Facts

rhawk

Board Regular

Similar threads

Forum statistics

Share this page

Query Question - Multiple passes

rhawk

Board Regular

Excel Facts

rhawk

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock