Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Jan 2003
    Posts
    15

    Unanswered: Domain Fuctions on Recordsets

    I am wondering if it is possible to do a domain function such as Davg on records in a recordset(instead of records in a standard table or stored query). If so what is the proper syntax?

    The recordset is a DAO recordset and has been declared public.

  2. #2
    Join Date
    Nov 2002
    Posts
    150

    Re: Domain Fuctions on Recordsets

    Originally posted by ageiger
    I am wondering if it is possible to do a domain function such as Davg on records in a recordset(instead of records in a standard table or stored query). If so what is the proper syntax?

    The recordset is a DAO recordset and has been declared public.
    Don't think so. Those functions only work on stored database objects such as tables and queries.

  3. #3
    Join Date
    Jan 2003
    Posts
    15
    In that case, can anyone suggest a solution for this problem:

    I am writing a report to generate results from a survey that my company's employees have been asked to fill out. I need to filter the results of the survey by department, and generate the overall sentiment about a certain question. I have a form established to enter the answers from the survey into a table and the responses are weighted 1-5. What I am trying to do is filter the results and calculate the responses(thats where the avg question came in).

    Heres where im hitting a brick wall, I can get the average by using avg() in the SQL select statement when I am creating my recordset. Unfortunately, due to limitations in the openrecordset source statement I can only average 2 or 3 items because of the 255 character limit.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    255 is for one query (actually, string). if you query(query(query(query))) you have 1020 chars and so on ad infinitum as far as i know... i have apps with queries-on-queries stacked to 7 levels, but i admit i never tried stacking 100, 1000, 10000... queries

    it's also a whole heap easier to debug if you use step-by-step instead of everything in one elaborate sql string. performance will suffer, but this is a report after all... a decent pc will outpace most printers.

    izy

  5. #5
    Join Date
    Jan 2003
    Posts
    15
    Could you please give a clearer example of this type of query.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    a query returns a recordset
    you can query this recordset returning a recordset
    you can query this recordset returning a recordset
    you can query this recordset returning a recordset
    you can query this recordset returning a recordset
    you can query this recordset returning a recordset
    you can query this recordset returning a recordset
    ..................etc

    sorry i dont have time to do this in detail now... tomorrow if nobody else replies in the meantime.

    izy

  7. #7
    Join Date
    Nov 2002
    Posts
    150

    Re: Domain Fuctions on Recordsets

    What does the table look like?

  8. #8
    Join Date
    Jan 2003
    Posts
    15
    The table has a userid, a department id, and the responses to the questions that can be numbers anywhere from 1-5.

    I am filtering by the department id at this time, and the user can select multiple department ids to filter by(up to 30)

  9. #9
    Join Date
    Nov 2002
    Posts
    150
    Originally posted by ageiger
    The table has a userid, a department id, and the responses to the questions that can be numbers anywhere from 1-5.

    I am filtering by the department id at this time, and the user can select multiple department ids to filter by(up to 30)
    OK, so you have a userid, a deptid, and something like 25 fields that hold the answers to the responses?

    Are you trying to get the average for each response field by department?

  10. #10
    Join Date
    Jan 2003
    Posts
    15
    that is correct, I have to get the average response for each of these questions, AND only do it for certain departments, sorta screwy I know, but thats what was requested

  11. #11
    Join Date
    Jan 2003
    Posts
    15
    this is how I am handling the department sorting issue:

    I have a form with a check box for each department and then I hit a command button and the vba code does it's magic

    Public Sub ReportDepAvg()
    strDepCnt = 0
    strDepSql = "Select * FROM tblMain Where ((tblMain.tblMainDep In ("



    If Forms!frmReportDep!chkFrmReportDepGl1st.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "3"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",3"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepGl2nd.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "1"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",1"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepTl1st.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "4"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",4"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepTl2nd.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "2"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",2"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepPro1st.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "14"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",14"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepPro2nd.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "15"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",15"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepRl1st.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "34"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",34"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepRl2nd.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "35"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",35"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepRl3rd.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "36"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",36"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepS5A1st.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "32"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",32"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepS5A2nd.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "33"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",33"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepS0K1st.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "30"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",30"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepS0K2nd.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "31"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",31"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepSDA1st.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "28"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",28"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepSDA2nd.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "29"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",29"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepSDAVac1st.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "26"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",26"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepSDAVac2nd.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "27"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",27"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepBod.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "25"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",25"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepQc1st.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "21"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",21"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepQc2nd.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "22"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",22"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepQc3rd.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "23"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",23"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepPpg.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "20"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",20"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepAcc.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "6"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",6"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepMis.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "12"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",12"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepMaint1st.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "17"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",17"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepMaint2nd.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "18"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",18 "
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepMaint3rd.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "19"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",19"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepWh1st.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "10"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",10"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepWh2nd.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "11"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",11"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepMs1st.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "8"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",8"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepMs2nd.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "9"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",9"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepPic.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "13"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",13"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If Forms!frmReportDep!chkFrmReportDepHr.Value = -1 Then
    If strDepCnt = 0 Then
    strDepSql = strDepSql + "5"
    strDepCnt = strDepCnt + 1
    Else
    strDepSql = strDepSql + ",5"
    strDepCnt = strDepCnt + 1
    End If
    End If

    If strDepCnt < 1 Then
    MsgBox "No Selections have been made, please select the departments that you would like to query and try again.", vbOKOnly, "No Selections"
    Else
    strDepSql = strDepSql + ")))"
    Set rstSurveyFull = CurrentDb.OpenRecordset(strDepSql)

    If rstSurveyFull.RecordCount > 0 Then
    DoCmd.OpenReport "rptSurveyAvg", acViewPreview, , , acWindowNormal
    Else: MsgBox "The selected departments yeilded no results", vbOKOnly, "No Data"
    End If
    End If

    End Sub

  12. #12
    Join Date
    Nov 2002
    Posts
    150
    Originally posted by ageiger
    that is correct, I have to get the average response for each of these questions, AND only do it for certain departments, sorta screwy I know, but thats what was requested
    You should be able to that fairly easy once the data is in the table. You can create a query and use it as the resource source for the report or you do it in grouping options.

    Something like this:

    SELECT Survey.DepartmentId, Avg(Survey.Q1) AS AvgOfQ1, Avg(Survey.Q2) AS AvgOfQ2, Avg(Survey.Q3) AS AvgOfQ3, Avg(Survey.Q4) AS AvgOfQ4, Avg(Survey.Q5) AS AvgOfQ5
    FROM Survey
    GROUP BY Survey.DepartmentId;

  13. #13
    Join Date
    Jan 2003
    Posts
    15
    Originally posted by FirstAndGoal4
    You should be able to that fairly easy once the data is in the table. You can create a query and use it as the resource source for the report or you do it in grouping options.

    Something like this:

    SELECT Survey.DepartmentId, Avg(Survey.Q1) AS AvgOfQ1, Avg(Survey.Q2) AS AvgOfQ2, Avg(Survey.Q3) AS AvgOfQ3, Avg(Survey.Q4) AS AvgOfQ4, Avg(Survey.Q5) AS AvgOfQ5
    FROM Survey
    GROUP BY Survey.DepartmentId;
    That was the origional plan, but with the filtering of departments and the averaging, I ran into the 255 char limit issue.

  14. #14
    Join Date
    Nov 2002
    Posts
    150
    Originally posted by ageiger
    That was the origional plan, but with the filtering of departments and the averaging, I ran into the 255 char limit issue.
    Here is how I would do it. Create a mutiselect listbox with the departments in it. The bound column should be the department id. Put an unbound, invisible text box on the form also. After the user selects all the departments and clicks the run report button, generate the WHERE condition.

    Something like this:

    Private Sub Command2_Click()
    Dim sWhere As String
    Dim varItem As Variant

    sWhere = "[DepartmentId] in ("
    For Each varItem In Me.List0.ItemsSelected
    sWhere = sWhere & Me.List0.ItemData(varItem) & ","
    Next varItem
    sWhere = Left(sWhere, Len(sWhere) - 1)
    sWhere = sWhere & ")"
    DoCmd.OpenReport "Survey", acViewPreview, , sWhere
    End Sub

    if departmentid is a string, you will have to include quote marks around the values: sWhere = sWhere & "'" & Me.List0.ItemData(varItem) & "',"

  15. #15
    Join Date
    Jan 2003
    Posts
    15
    this is the sql statement that is returned if all the departments are checked

    Select * FROM tblMain Where ((tblMain.tblMainDep In (3,1,4,2,14,15,34,35,36,32,33,30,31,28,29,26,27,25 ,21,22,23,20,6,12,17,18 ,19,10,11,8,9,13,5)))

    when I added the average funtions in place of the * thats when I started to get the length issues, should I rewrite my sql statement(before it had about 30 OR statments in it)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •