Start Free Trial

asked on

Finding averages, Stdev, count, from Access database

Hi Experts,

I have 2 combo boxes on my form, that contain the start date and end date

I have 2 tables in my database, and when the user selects the startDate and the endDate, I want to get the data relevant to those dates from both tables.

I have attached samples from both tables.

Table1 contains bi-weekly data, and Table2 contains Hourly data.

I need to get the averages, the stdev, and the count for both tables in the range of date selected.

Thanks in advance

Dim cmd_find As New ADODB.Command
Dim RS_find As New ADODB.Recordset
With cmd_find
    .ActiveConnection = m_Conn
    .CommandText = "SELECT tbl_ReferenceIntegration.StartDate, tbl_ReferenceIntegration.StartTime, tbl_ReferenceIntegration.EndDate, tbl_ReferenceIntegration.EndTime, Avg(tbl_ReferenceIntegration.OC) AS Avg_OC, Avg(tbl_ReferenceIntegration.EC) AS Avg_EC, Avg(tbl_ReferenceIntegration.TC) AS Avg_TC, Avg(Import_Match_Integration.Mean) AS ParamMean, StDev(Import_Match_Integration.Mean) AS ParamStDev, Count(Import_Match_Integration.Mean) AS ParamCount" & _
    " From Import_Match_Integration, tbl_ReferenceIntegration" & _
    " WHERE (((Import_Match_Integration.StartDate) Between #" & cbo_Date_From.Text & "# And #" & cbo_date_to.Text & "#)) And OC<>0 And EC<>0 And TC<>0" & _
    " GROUP BY tbl_ReferenceIntegration.StartDate, tbl_ReferenceIntegration.EndDate, tbl_ReferenceIntegration.StartTime, tbl_ReferenceIntegration.EndTime" & _
    " ORDER BY tbl_ReferenceIntegration.StartDate;"
    .CommandType = adCmdText
End With
 
  With RS_find
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open cmd_find
  End With
  
  Set grd_result.DataSource = RS_find

Open in new window

sample-tables.xls

ASKER

I have attached another file.

This is the result that I get from another query, and is displayed on my flex grid.

Now for example, if the user selects the date ranges in the first 3 rows,

then I just want to get a query that will give me the averages, stdev, and count, of those rows.

Thanks :)
Sample-table-values.xls

your query is looking good. With StartDate and EndDate, you filtered the unwanted rows. Whats the issue ?

ASKER

Well Sharath it doesn't work.

I am getting all the data, even though I specify that I only want the data between the startDate and EndDate

You can try my query with the same data that I have given and compare the results, if you get the same as I am.

Thanks for your help :)

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Hi JimPen

Thanks for your help, I tried the code, but it didnt give me the result.

I am still getting all the data, and not the data in my combo box.

I am also getting the same number repeated for all the rows for avg(tbl2.mean), the stdev, count.

You can take a look at my 1st comment after the question, it has 2 sample tables that contain sample data, and if you query any 2 dates between those 2 tables, we still dont get the correct results.

I really need this soon, thanks for your help !

Put in a debug and then post the resulting SQL statement from the immediate window <Ctrl>+G.

Are you running this as a sub of the form or as standalone module?

  " ORDER BY tbl_ReferenceIntegration.StartDate;"
 
DEBUG.PRINT SQL 
 
With cmd_find

Open in new window

ASKER

Hi Jimpen,

sorry for not being here so long.

I actually put some thought into this, and I think that I should've specified the output format.

This is what I want in the output, when the user selects the startDate and the EndDate fromt he combo box, then I want the query to just return one row of data, in which the startdate and the enddate is what the user selected, and then the rest of the information is just the avg, stdev, and count.

In the query we have, when we group between the ranges of startDate and Endate, we will get multiple rows, but I want to just display one row.

Another idea I had was to just match the 2 tables as is, I have that query, in which I match the date+Time ranges from both tables and display those, so I was thinking that from that query being displayed in the grid, I can put text boxes on the form that will display the result via VB6 code, as there are about 50 rows, so I can use the textmatrix property in the MSHFlex grid to just give me the averages, stdev and sum(count) from the rows.

please advice if I should continue is SQL for my desired output, or in VB6.

Much Thanks !

ASKER

This is the match As Is query which I am using to get the result in my flexgrid.

Now from these results, when the user selects the StartDate and EndDate from the combo Box, then I want to just give one Row of results.

Thanks

SELECT tbl_ReferenceIntegration.StartDate, tbl_ReferenceIntegration.StartTime, tbl_ReferenceIntegration.EndDate, tbl_ReferenceIntegration.EndTime, Avg(tbl_ReferenceIntegration.OC) AS OC, Avg(tbl_ReferenceIntegration.EC) AS EC, Avg(tbl_ReferenceIntegration.TC) AS TC, Avg(Import_Match_Integration.Mean) AS ParamMean, StDev(Import_Match_Integration.Mean) AS StDevOfMean, Count(Import_Match_Integration.mean) AS CountOfMean
FROM Import_Match_Integration, tbl_ReferenceIntegration
WHERE Import_Match_Integration.StartDate+Import_Match_Integration.StartTime Between tbl_ReferenceIntegration.StartDate+tbl_ReferenceIntegration.StartTime And tbl_ReferenceIntegration.EndDate+tbl_ReferenceIntegration.EndTime And OC<>0 And EC<>0 And TC<>0
GROUP BY tbl_ReferenceIntegration.StartDate, tbl_ReferenceIntegration.EndDate, tbl_ReferenceIntegration.StartTime, tbl_ReferenceIntegration.EndTime
ORDER BY tbl_ReferenceIntegration.StartDate;

Open in new window

ASKER

Here is an example to make things very easy to understand.

Please see file attached...
Irregular-matching-Example.xls

Lets Try it this way:

Please understand, This will display ALL the lines that qualify. Are you Looking to "group" the qualifying Lines together?

SELECT tbl_ReferenceIntegration.StartDate, tbl_ReferenceIntegration.StartTime, tbl_ReferenceIntegration.EndDate, tbl_ReferenceIntegration.EndTime, Avg(tbl_ReferenceIntegration.OC) AS Avg_OC, Avg(tbl_ReferenceIntegration.EC) AS Avg_EC, Avg(tbl_ReferenceIntegration.TC) AS Avg_TC, Avg(Import_Match_Integration.Mean) AS ParamMean, StDev(Import_Match_Integration.Mean) AS ParamStDev, Count(Import_Match_Integration.Mean) AS ParamCount
FROM Import_Match_Integration INNER JOIN tbl_ReferenceIntegration ON Import_Match_Integration.StartDate = tbl_ReferenceIntegration.StartDate
WHERE (((Import_Match_Integration.StartDate)>=" & & cbo_Date_From.Text & " And (Import_Match_Integration.StartDate)<#" & cbo_date_to.Text & "#) AND ((tbl_ReferenceIntegration.[OC])<>0) AND ((tbl_ReferenceIntegration.[EC])<>0) AND ((tbl_ReferenceIntegration.[TC])<>0))
GROUP BY tbl_ReferenceIntegration.StartDate, tbl_ReferenceIntegration.StartTime, tbl_ReferenceIntegration.EndDate, tbl_ReferenceIntegration.EndTime
ORDER BY tbl_ReferenceIntegration.StartDate;

Open in new window

ASKER

RDWaibel,

Please check the attached file, it will explain what I want precisely

ASKER

The query that you have sent will result in multiple rows, where as I just need one row for the final output.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

You Might want to change the following piece...

SELECT Max(tbl_ReferenceIntegration.StartDate) AS MaxOfStartDate,

TO

SELECT Min(tbl_ReferenceIntegration.StartDate) AS MinOfStartDate,

ASKER

Will do.

Let me have a look at the results
:-)

ASKER

VERYYYY Impressive !

You got the one row thing perfectly, but the results are not so accurate.. lol

This is what the results should be, as verified from excel.

StartDate      StartTime      EndDate      EndTime      OC      EC      TC      ParamMean      StDevOfMean      CountOfMean
8/15/2005      3:10:00 PM      8/29/2005      6:35:00 PM      2.041      70.979      76.038      83.884      2.838      168
8/29/2005      6:41:00 PM      9/14/2005      5:07:00 PM      17.308      90.366      77.06      119.241      26.253      258
9/14/2005      5:12:00 PM      9/26/2005      3:09:00 PM      11.843      93.133      71.676      108.605      3.079      284
9/26/2005      3:15:00 PM      10/10/2005      2:15:00 PM      4.136      48.019      77.99      112.958      25.787      246
                        8.832      75.62425      75.691      106.172      13.31628578      956

And this is the result I am getting

MinOfStartDate      MaxOfEndDate      Avg_OC      Avg_EC      Avg_TC      ParamMean      ParamStDev      ParamCount
8/15/2005      10/10/2005      6.979      74.447      74.642      101.823      12.42      54

ASKER

Ok this is in Excel format, the one before is very messy

I have also attached the query with which I get the results on my grid. From these results I have pasted the first 4 lines to compare with our results.

SELECT tbl_ReferenceIntegration.StartDate, tbl_ReferenceIntegration.StartTime, tbl_ReferenceIntegration.EndDate, tbl_ReferenceIntegration.EndTime, Avg(tbl_ReferenceIntegration.OC) AS OC, Avg(tbl_ReferenceIntegration.EC) AS EC, Avg(tbl_ReferenceIntegration.TC) AS TC, Avg(Import_Match_Integration.Mean) AS ParamMean, StDev(Import_Match_Integration.Mean) AS StDevOfMean, Count(Import_Match_Integration.mean) AS CountOfMean
FROM Import_Match_Integration, tbl_ReferenceIntegration
WHERE Import_Match_Integration.StartDate+Import_Match_Integration.StartTime Between tbl_ReferenceIntegration.StartDate+tbl_ReferenceIntegration.StartTime And tbl_ReferenceIntegration.EndDate+tbl_ReferenceIntegration.EndTime And OC<>0 And EC<>0 And TC<>0
GROUP BY tbl_ReferenceIntegration.StartDate, tbl_ReferenceIntegration.EndDate, tbl_ReferenceIntegration.StartTime, tbl_ReferenceIntegration.EndTime
ORDER BY tbl_ReferenceIntegration.StartDate;

Open in new window

Export-Results.xls

ASKER

Is this because we don't take into effect the startTime and EndTime?

Data in tbl_radiativeData is hourly, so the time range will effect the accuracy of the results. The counts are highly off to...

I will be back within 1.5 hours.

Thanks for your help.

Great Work !

Not sure. no matter how I pull the data, I don't get your "single" record's numbers.

ASKER

Ok.

I was just playing with the function, and it doesn't work always.

Sometimes even though I select a different date in my combo box, the result is not as the same date.

I think this is happening because of the max/min functions, that are either looking at the Day, so if a case arises in which the day of the previous selection was larger then the current, then it will not display the current, but will display the previous one.

I'll try to work on it as well and keep you updated.

I'm off for now.
Thanks for your help

I just might have something.. Give me a sec

ARG...

This one is going to take some doing. There is no Nice way of processing the data as the Start/End date vary

ASKER

Ok.

So how about I use the textmatrix property, and find those dates in the datagrid, then from the matchasis query, i can just put the formulas for avg, stdev and count in vb6

ASKER

From the result*** of the matchAsIs query on the grid, I can get the outputs

That would work...

ASKER

Ok... so I'll give that a shot and let you know

Thanks

You Bet

Split:
ID: http:/#23881487 Author:RDWaibel
ID: http:/#23810868 Author:jimpen