By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,157 Members | 1,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,157 IT Pros & Developers. It's quick & easy.

Help Required on Query Building in VBA

P: 77
Hello,

I hope someone can help me in resolving the following in access 2003:

I have a table with the following fields:

emp_id,emp_name,repdate,actions,duration,lob,categ ory,ltstatus

emp_id is not unique and there appears to be no primary key for this table.

the actions field stores the count of all actions recorded under ltstatus and their duration is logged under duration field.

there can be multiple actions for each lob & category.

What i need is the following:

All unique actions need to be sidelined from the table and then for each unique date (repdate), emp_id, lob & category combination, i need the sum of duration and sum of action (the number of actions is currently 8 and hence it makes 8*2=16 fields to record the sum of duration and sum of action for each action)

I am currently able to accomplish the task by looping through the records but it is taking a very very very long time (due to the loops of course)..

What I am looking for is a solution (a query perhaps?) which can do it faster...

Pls help.

Thanks.
Aug 27 '08 #1
Share this Question
Share on Google+
25 Replies


nico5038
Expert 2.5K+
P: 3,072
For this you can use across table query.
For getting the 16 columns you need to create a query like:
Expand|Select|Wrap|Line Numbers
  1. Select emp_id,emp_name,repdate, 'act-' & action as Col, sum(ltstatus) as ColVal  from tblYours 
  2. group by emp_id,emp_name,repdate
  3. UNION
  4. Select emp_id,emp_name,repdate, 'dur-' & action as col, sum(duration)  from tblYours 
  5. group by emp_id,emp_name,repdate
  6.  
Save this guery e.g. as qryBasic.
Now you can use this for the crosstable query, using emp_id,emp_name,repdat as the rowheader, Col as the colheader and ColVal as the value. (don't forget to set the "groupby" to "sum")

Nic;o)
Aug 27 '08 #2

P: 77
Sorry, but this is not working....

Please suggest alternate fix...
Aug 28 '08 #3

P: 77
The current code is as follows:

Expand|Select|Wrap|Line Numbers
  1. Public Sub generateAPR(fName$)
  2.     'On Error GoTo errr
  3.     Dim vCbk As Double, vMeal As Double, vOne2One As Double, vOpsTrg As Double, vProd As Double, vQlty As Double, vSick As Double
  4.     Dim vSystem As Double, vTea As Double, vTrg As Double, vVnA As Double, vSkip As Double, vClosed As Double, vPending As Double
  5.     Dim vXferExt As Double, vXferInt As Double, vCbkCnt As Double, vMealCnt As Double, vOne2OneCnt As Double
  6.     Dim vQltyCnt As Double, vSickCnt As Double, vSystemCnt As Double, vTeaCnt As Double, vTrgCnt As Double, vVnACnt As Double
  7.     Dim vSkipCnt As Double, vOpsTrgCnt As Double, vLoginCnt As Double
  8.     vStatus = SysCmd(acSysCmdSetStatus, "Please wait while APR data is being created...")
  9.     DoCmd.Hourglass True
  10.     Call conDB
  11.     Set rsFourth = New ADODB.Recordset
  12.     vSQL = "delete * from tmp_apr_raw"
  13.     rsFourth.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  14.     vSQL = "select distinct(emp_id),emp_name from apr_raw where repdate>=#" & sdate & "# and repdate<=#" & edate & "#"
  15.     rsMain.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  16.     rsMain.MoveFirst
  17.     Do While Not rsMain.EOF
  18.         Set rsFirst = New ADODB.Recordset
  19.         vSQL = "select count(*) from apr_raw where emp_id=" & rsMain(0) & " and repdate>=#" & sdate & "# and " & _
  20.                "repdate<=#" & edate & "#"
  21.         rsFirst.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  22.         If rsFirst(0) <> 0 Then
  23.             Set rsFirst = New ADODB.Recordset
  24.             Set rsSecond = New ADODB.Recordset
  25.             Set rsThird = New ADODB.Recordset
  26.             Set rsSixth = New ADODB.Recordset
  27.             'finding out the distinct dates for an employee
  28.             vSQL = "select distinct(repdate) from apr_raw where emp_id=" & rsMain(0) & " and repdate>=#" & sdate & "# and " & _
  29.                "repdate<=#" & edate & "#"
  30.             Set rsFirst = conMain.Execute(vSQL)
  31.             rsFirst.MoveFirst
  32.             'rsFirst.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  33.             For i = 1 To rsFirst.RecordCount
  34.                 'finding out the distinct LOBs for each date and each employee
  35.                 vSQL = "select distinct(lob) from apr_raw where emp_id=" & rsMain(0) & " and repdate=#" & rsFirst(0) & "#"
  36.                 Set rsSecond = conMain.Execute(vSQL)
  37.                 vLoginCnt = 0
  38.                 'rsSecond.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  39.                 For j = 1 To rsSecond.RecordCount
  40.                     'finding out the distinct categories for each date, each employee, each lob
  41.                     vSQL = "select distinct(category) from apr_raw where emp_id=" & rsMain(0) & " and " & _
  42.                             "repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "'"
  43.                     Set rsSixth = conMain.Execute(vSQL)
  44.                     For k = 1 To rsSixth.RecordCount
  45.                         vCbk = 0: vMeal = 0: vOne2One = 0: vOpsTrg = 0: vProd = 0: vQlty = 0: vSick = 0: vSystem = 0: vTea = 0
  46.                         vSkip = 0: vClosed = 0: vPending = 0: vXferExt = 0: vXferInt = 0: vCbkCnt = 0: vMealCnt = 0
  47.                         vSickCnt = 0: vSystemCnt = 0: vTeaCnt = 0: vTrgCnt = 0: vVnACnt = 0: vSkipCnt = 0
  48.                         vOpsTrgCnt = 0: vTrg = 0: vVnA = 0: vOne2OneCnt = 0: vQltyCnt = 0
  49.                         wsday = "": wsmonth = "": wsyear = "": weday = "": wemonth = "": weyear = ""
  50.                         'calculating Callback duration and count
  51.                         vSQL = "select sum(duration),sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
  52.                         "repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Callback' and " & _
  53.                         "category='" & rsSixth(0) & "'"
  54.                         Set rsThird = New ADODB.Recordset
  55.                         rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  56.                         If IsNull(rsThird(0)) = False Then
  57.                             vCbk = rsThird(0)
  58.                         Else
  59.                             vCbk = 0
  60.                         End If
  61.                         If IsNull(rsThird(0)) = False Then
  62.                             vCbkCnt = rsThird(1)
  63.                         Else
  64.                             vCbkCnt = 0
  65.                         End If
  66.                         'calculating meal break duration and count
  67.                         vSQL = "select sum(duration),sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
  68.                         "repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Meal Break' and " & _
  69.                         "category='" & rsSixth(0) & "'"
  70.                         Set rsThird = New ADODB.Recordset
  71.                         rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  72.                         If IsNull(rsThird(0)) = False Then
  73.                             vMeal = rsThird(0)
  74.                         Else
  75.                             vMeal = 0
  76.                         End If
  77.                         If IsNull(rsThird(0)) = False Then
  78.                             vMealCnt = rsThird(1)
  79.                         Else
  80.                             vMealCnt = 0
  81.                         End If
  82.                         'calculating One2One duration and count
  83.                         vSQL = "select sum(duration),sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
  84.                         "repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='One on One' and " & _
  85.                         "category='" & rsSixth(0) & "'"
  86.                         Set rsThird = New ADODB.Recordset
  87.                         rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  88.                         If IsNull(rsThird(0)) = False Then
  89.                             vOne2One = rsThird(0)
  90.                         Else
  91.                             vOne2One = 0
  92.                         End If
  93.                         If IsNull(rsThird(0)) = False Then
  94.                             vOne2OneCnt = rsThird(1)
  95.                         Else
  96.                             vOne2OneCnt = 0
  97.                         End If
  98.                         'calculating Ops Training duration and count
  99.                         vSQL = "select sum(duration),sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
  100.                         "repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Ops Training' and " & _
  101.                         "category='" & rsSixth(0) & "'"
  102.                         Set rsThird = New ADODB.Recordset
  103.                         rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  104.                         If IsNull(rsThird(0)) = False Then
  105.                             vOpsTrg = rsThird(0)
  106.                         Else
  107.                             vOpsTrg = 0
  108.                         End If
  109.                         If IsNull(rsThird(0)) = False Then
  110.                             vOpsTrgCnt = rsThird(1)
  111.                         Else
  112.                             vOpsTrgCnt = 0
  113.                         End If
  114.                         'calculating Quality Feedback duration and count
  115.                         vSQL = "select sum(duration),sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
  116.                         "repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Quality Feedback' and " & _
  117.                         "category='" & rsSixth(0) & "'"
  118.                         Set rsThird = New ADODB.Recordset
  119.                         rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  120.                         If IsNull(rsThird(0)) = False Then
  121.                             vQlty = rsThird(0)
  122.                         Else
  123.                             vQlty = 0
  124.                         End If
  125.                         If IsNull(rsThird(0)) = False Then
  126.                             vQltyCnt = rsThird(1)
  127.                         Else
  128.                             vQltyCnt = 0
  129.                         End If
  130.                         'calculating Sick duration and count
  131.                         vSQL = "select sum(duration),sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
  132.                         "repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Sick' and " & _
  133.                         "category='" & rsSixth(0) & "'"
  134.                         Set rsThird = New ADODB.Recordset
  135.                         rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  136.                         If IsNull(rsThird(0)) = False Then
  137.                             vSick = rsThird(0)
  138.                         Else
  139.                             vSick = 0
  140.                         End If
  141.                         If IsNull(rsThird(0)) = False Then
  142.                             vSickCnt = rsThird(1)
  143.                         Else
  144.                             vSickCnt = 0
  145.                         End If
  146.                         'calculating System down duration and count
  147.                         vSQL = "select sum(duration),sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
  148.                         "repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='System Down' and " & _
  149.                         "category='" & rsSixth(0) & "'"
  150.                         Set rsThird = New ADODB.Recordset
  151.                         rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  152.                         If IsNull(rsThird(0)) = False Then
  153.                             vSystem = rsThird(0)
  154.                         Else
  155.                             vSystem = 0
  156.                         End If
  157.                         If IsNull(rsThird(0)) = False Then
  158.                             vSystemCnt = rsThird(1)
  159.                         Else
  160.                             vSystemCnt = 0
  161.                         End If
  162.                         'calculating Tea Break duration and count
  163.                         vSQL = "select sum(duration),sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
  164.                         "repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Tea Break' and " & _
  165.                         "category='" & rsSixth(0) & "'"
  166.                         Set rsThird = New ADODB.Recordset
  167.                         rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  168.                         If IsNull(rsThird(0)) = False Then
  169.                             vTea = rsThird(0)
  170.                         Else
  171.                             vTea = 0
  172.                         End If
  173.                         If IsNull(rsThird(0)) = False Then
  174.                             vTeaCnt = rsThird(1)
  175.                         Else
  176.                             vTeaCnt = 0
  177.                         End If
  178.                         'calculating Training duration and count
  179.                         vSQL = "select sum(duration),sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
  180.                         "repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Training' and " & _
  181.                         "category='" & rsSixth(0) & "'"
  182.                         Set rsThird = New ADODB.Recordset
  183.                         rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  184.                         If IsNull(rsThird(0)) = False Then
  185.                             vTrg = rsThird(0)
  186.                         Else
  187.                             vTrg = 0
  188.                         End If
  189.                         If IsNull(rsThird(0)) = False Then
  190.                             vTrgCnt = rsThird(1)
  191.                         Else
  192.                             vTrgCnt = 0
  193.                         End If
  194.                         'calculating V&A duration and count
  195.                         vSQL = "select sum(duration),sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
  196.                         "repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='V & A Feedback' and " & _
  197.                         "category='" & rsSixth(0) & "'"
  198.                         Set rsThird = New ADODB.Recordset
  199.                         rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  200.                         If IsNull(rsThird(0)) = False Then
  201.                             vVnA = rsThird(0)
  202.                         Else
  203.                             vVnA = 0
  204.                         End If
  205.                         If IsNull(rsThird(0)) = False Then
  206.                             vVnACnt = rsThird(1)
  207.                         Else
  208.                             vVnACnt = 0
  209.                         End If
  210.                         'calculating Skip duration and count
  211.                         vSQL = "select sum(duration),sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
  212.                         "repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Skip' and " & _
  213.                         "category='" & rsSixth(0) & "'"
  214.                         Set rsThird = New ADODB.Recordset
  215.                         rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  216.                         If IsNull(rsThird(0)) = False Then
  217.                             vSkip = rsThird(0)
  218.                         Else
  219.                             vSkip = 0
  220.                         End If
  221.                         If IsNull(rsThird(0)) = False Then
  222.                             vSkipCnt = rsThird(1)
  223.                         Else
  224.                             vSkipCnt = 0
  225.                         End If
  226.                         'calculating Production duration
  227.                         vSQL = "select sum(duration) from apr_raw where emp_id=" & rsMain(0) & " and " & _
  228.                         "repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and LT='Production Time' and " & _
  229.                         "category='" & rsSixth(0) & "'"
  230.                         Set rsThird = New ADODB.Recordset
  231.                         rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  232.                         If IsNull(rsThird(0)) = False Then
  233.                             vProd = rsThird(0)
  234.                         Else
  235.                             vProd = 0
  236.                         End If
  237.                         'calculating # of closed cases
  238.                         vSQL = "select sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
  239.                         "repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Closed' and " & _
  240.                         "category='" & rsSixth(0) & "'"
  241.                         Set rsThird = New ADODB.Recordset
  242.                         rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  243.                         If IsNull(rsThird(0)) = False Then
  244.                             vClosed = rsThird(0)
  245.                         Else
  246.                             vClosed = 0
  247.                         End If
  248.                         'calculating # of Pending cases
  249.                         vSQL = "select sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
  250.                         "repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Pending' and " & _
  251.                         "category='" & rsSixth(0) & "'"
  252.                         Set rsThird = New ADODB.Recordset
  253.                         rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  254.                         If IsNull(rsThird(0)) = False Then
  255.                             vPending = rsThird(0)
  256.                         Else
  257.                             vPending = 0
  258.                         End If
  259.                         'calculating # of Internal XFER cases
  260.                         vSQL = "select sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
  261.                         "repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Transferred Internal' and " & _
  262.                         "category='" & rsSixth(0) & "'"
  263.                         Set rsThird = New ADODB.Recordset
  264.                         rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  265.                         If IsNull(rsThird(0)) = False Then
  266.                             vXferInt = rsThird(0)
  267.                         Else
  268.                             vXferInt = 0
  269.                         End If
  270.                         'calculating # of External XFER cases
  271.                         vSQL = "select sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
  272.                         "repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Transferred External' and " & _
  273.                         "category='" & rsSixth(0) & "'"
  274.                         Set rsThird = New ADODB.Recordset
  275.                         rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  276.                         If IsNull(rsThird(0)) = False Then
  277.                             vXferExt = rsThird(0)
  278.                         Else
  279.                             vXferExt = 0
  280.                         End If
  281.                         'inserting the records in the temp table
  282.                         Set rsFourth = New ADODB.Recordset
  283.                         vSQL = "select * from tmp_apr_raw"
  284.                         rsFourth.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  285.                         rsFourth.AddNew
  286.                         rsFourth(0) = rsMain(0)
  287.                         vSQL = "select tl_id,tl_name,om_id,om_name,lob_mapping from apr_raw where emp_id=" & rsMain(0) & _
  288.                                " and repdate=#" & rsFirst(0) & "#"
  289.                         Set rsFifth = New ADODB.Recordset
  290.                         rsFifth.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
  291.                         rsFourth(1) = rsMain(1)
  292.                         rsFourth(2) = rsFifth(0)
  293.                         rsFourth(3) = rsFifth(1)
  294.                         rsFourth(4) = rsFifth(2)
  295.                         rsFourth(5) = rsFifth(3)
  296.                         rsFourth(6) = rsSecond(0)
  297.                         rsFourth(7) = rsSixth(0)
  298.                         rsFourth(8) = rsFirst(0)
  299.                         wsday = Day(DateAdd("d", -(Weekday(rsFirst(0), vbFriday) - 1), rsFirst(0)))
  300.                         wsmonth = Format(DateAdd("d", -(Weekday(rsFirst(0), vbFriday) - 1), rsFirst(0)), "mmm")
  301.                         wsyear = Right(Year(DateAdd("d", -(Weekday(rsFirst(0), vbFriday) - 1), rsFirst(0))), 2)
  302.                         weday = Day(DateAdd("d", -(Weekday(rsFirst(0), vbFriday) - 1) + 6, rsFirst(0)))
  303.                         wemonth = Format(DateAdd("d", -(Weekday(rsFirst(0), vbFriday) - 1) + 6, rsFirst(0)), "mmm")
  304.                         weyear = Right(Year(DateAdd("d", -(Weekday(rsFirst(0), vbFriday) - 1) + 6, rsFirst(0))), 2)
  305.                         rsFourth(9) = "WE_" & weday & wemonth & weyear 'weekname
  306.                         rsFourth(10) = Format(rsFirst(0), "mmm")
  307.                         rsFourth(11) = vCbk
  308.                         rsFourth(12) = vMeal
  309.                         rsFourth(13) = vOne2One
  310.                         rsFourth(14) = vOpsTrg
  311.                         rsFourth(15) = vProd
  312.                         rsFourth(16) = vQlty
  313.                         rsFourth(17) = vSick
  314.                         rsFourth(18) = vSystem
  315.                         rsFourth(19) = vTea
  316.                         rsFourth(20) = vTrg
  317.                         rsFourth(21) = vVnA
  318.                         rsFourth(22) = vSkip
  319.                         rsFourth(23) = vClosed
  320.                         rsFourth(24) = vPending
  321.                         rsFourth(25) = vXferExt
  322.                         rsFourth(26) = vXferInt
  323.                         rsFourth(27) = vCbkCnt
  324.                         rsFourth(28) = vMealCnt
  325.                         rsFourth(29) = vOne2OneCnt
  326.                         rsFourth(30) = vOpsTrgCnt
  327.                         rsFourth(31) = vQltyCnt
  328.                         rsFourth(32) = vSickCnt
  329.                         rsFourth(33) = vSystemCnt
  330.                         rsFourth(34) = vTeaCnt
  331.                         rsFourth(35) = vTrgCnt
  332.                         rsFourth(36) = vVnACnt
  333.                         rsFourth(37) = vSkipCnt
  334.                         If j = 1 And k = 1 Then
  335.                             vLoginCnt = 1
  336.                         Else
  337.                             vLoginCnt = 0
  338.                         End If
  339.                         rsFourth(38) = vLoginCnt
  340.                         rsFourth(39) = rsFifth(4)
  341.                         rsFourth.UpdateBatch
  342.                         rsSixth.MoveNext
  343.                     Next
  344.                     rsSecond.MoveNext
  345.                 Next
  346.                 rsFirst.MoveNext
  347.             Next
  348.         End If
  349.         rsMain.MoveNext
  350.     Loop
  351.     Call createAPR
  352.     vsday = Day(CDate(sdate))
  353.     vsmonth = Format(sdate, "mmm")
  354.     vsyear = Right(Year(sdate), 2)
  355.     veday = Day(CDate(edate))
  356.     vemonth = Format(edate, "mmm")
  357.     veyear = Right(Year(edate), 2)
  358. '    fName = "AgentRaw_" & vsday & vsmonth & vsyear & "_" & veday & vemonth & veyear & ".xls"
  359.     oWB.SaveAs vRepPath & fName
  360.     oWB.Close
  361.     oExcel.Quit
  362.     Set oExcel = Nothing
  363.     Set oWB = Nothing
  364.     Set oWS = Nothing
  365.     MsgBox "Please collect the APR from " & Left(vRepPath, Len(vRepPath) - 1)
  366.     'rsExcel.Close
  367.     Set conExcel = Nothing
  368.     Set rsMain = Nothing
  369.     Set rsFirst = Nothing
  370.     Set rsSecond = Nothing
  371.     Set rsThird = Nothing
  372.     Set rsFourth = Nothing
  373.     Set rsFifth = Nothing
  374.     Set rsSixth = Nothing
  375.     Set conMain = Nothing
  376.     vStatus = SysCmd(acSysCmdClearStatus)
  377.     DoCmd.Hourglass False
  378.     Exit Sub
  379. errr:
  380.     MsgBox Err.Description
  381.     'rsExcel.Close
  382.     rsMain.Close
  383.     Set rsExcel = Nothing
  384.     Set conExcel = Nothing
  385.     Set rsMain = Nothing
  386.     Set rsFirst = Nothing
  387.     Set rsSecond = Nothing
  388.     Set rsThird = Nothing
  389.     Set rsFourth = Nothing
  390.     Set rsFifth = Nothing
  391.     Set rsSixth = Nothing
  392.     Set conMain = Nothing
  393.     vStatus = SysCmd(acSysCmdClearStatus)
  394.     DoCmd.Hourglass False
  395. End Sub
Aug 28 '08 #4

Expert Mod 2.5K+
P: 2,545
Hi. I have added code tags to what was an absolutely unreadable post without them.

There is no way at all that you should expect our volunteer experts to read more than 300 lines of your code. Please understand that this is an unreasonable request and not one with which we can assist.

If it were not for you having very recently joined I would simply have deleted your post, but in the circumstances I will let it stand at present.

Please let me know if you would prefer to post a revised - simplified - set of code and I or another moderator will delete the excess post above.

Also, if you say something does not work please tell us what happens exactly - it is bordering on rude to accept assistance and then simply post 'it does not work' without telling us what does or does not happen, what error messages (if any) you receive, and so on.

MODERATOR
Aug 28 '08 #5

P: 77
Hi,

Apologies.. I was not aware of the code tags.. Many thanks for correcting me.

Also, this is the exact code that I am currently using and am looking for a simplified solution to this. Unfortunately I was not able to explain my issue in detail in the previous posts and hence the solutions provided earlier just didn't work (as they were not according to my requirements)..

This is the reason why I posted the entire code so that someone may have a look at it and assist.

Didn't mean to be rude :-(

Hi. I have added code tags to what was an absolutely unreadable post without them.

There is no way at all that you should expect our volunteer experts to read more than 300 lines of your code. Please understand that this is an unreasonable request and not one with which we can assist.

If it were not for you having very recently joined I would simply have deleted your post, but in the circumstances I will let it stand at present.

Please let me know if you would prefer to post a revised - simplified - set of code and I or another moderator will delete the excess post above.

Also, if you say something does not work please tell us what happens exactly - it is bordering on rude to accept assistance and then simply post 'it does not work' without telling us what does or does not happen, what error messages (if any) you receive, and so on.

MODERATOR
Aug 28 '08 #6

Expert Mod 2.5K+
P: 2,545
Thank you for your response. Well, you might get lucky and find somebody who is willing to review nearly 400 lines of your code to suggest simplifications - but it is not what we are here to do.

We try to assist you to find your own solution to problems - we cannot know anything at all about your application in the depth needed to even review code of this length, never mind suggest improvements.

All of us develop Access applications either professionally, for interest, or both; we know how much time and effort it takes to devise even 'simple' applications, and it is because it would take hours of time to understand your code as you have posted it that it is not a reasonable request to make of us.

-Stewart
Aug 28 '08 #7

P: 77
I understand that the code is long and would take time to understand. However, in my earlier post, I have tried to explain my problem that I'm facing and what I am looking for... I hope someone would understand my problem with my initial post and would not have to go through the code.. The code may be used for reference purposes (just in case anyone would want to take a look).. Till the time there is a solution, it's going to be a long wait of over 35 minutes each time this code is executed (as it returns over 10,000 records)... :-|

Thank you for your response. Well, you might get lucky and find somebody who is willing to review nearly 400 lines of your code to suggest simplifications - but it is not what we are here to do.

We try to assist you to find your own solution to problems - we cannot know anything at all about your application in the depth needed to even review code of this length, never mind suggest improvements.

All of us develop Access applications either professionally, for interest, or both; we know how much time and effort it takes to devise even 'simple' applications, and it is because it would take hours of time to understand your code as you have posted it that it is not a reasonable request to make of us.

-Stewart
Aug 28 '08 #8

nico5038
Expert 2.5K+
P: 3,072
Sorry, but this is not working....

Please suggest alternate fix...
Can you elaborate what's not working ?

Nic;o)
Aug 28 '08 #9

P: 77
Hi Nic,

This code is working perfectly. The only concern is that it is taking almost 30-35 minutes to complete execution due to the loops..

What I am looking for is a query to avoid the loops and produce the desired output..

Can you elaborate what's not working ?

Nic;o)
Aug 29 '08 #10

P: 77
I have prepared a sample raw data and final output required.. but i'm not sure if attachments are allowed and how to display the data in the forum.. Pls suggest..

Hi Nic,

This code is working perfectly. The only concern is that it is taking almost 30-35 minutes to complete execution due to the loops..

What I am looking for is a query to avoid the loops and produce the desired output..
Aug 29 '08 #11

nico5038
Expert 2.5K+
P: 3,072
I have prepared a sample raw data and final output required.. but i'm not sure if attachments are allowed and how to display the data in the forum.. Pls suggest..
Just press the Edit/Delete button after saving the comment.
This will allow the attachment of a file to the comment.

Nic;o)
Aug 29 '08 #12

P: 77
Sounds Great... Let me do it right away in the current post.

Just press the Edit/Delete button after saving the comment.
This will allow the attachment of a file to the comment.

Nic;o)
Aug 29 '08 #13

nico5038
Expert 2.5K+
P: 3,072
I used this query on the RAW table:
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum(RAW.Seconds) AS SumOfSeconds
  2. SELECT RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, RAW.Category
  3. FROM RAW
  4. GROUP BY RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, RAW.Category
  5. PIVOT RAW.Actions;
  6.  
And got a similar result, although all "empty" columns will not show.
These can be "forced" when you have a table with all actions.

Nic;o)
Aug 29 '08 #14

P: 77
Nic,

Great Great help indeed... This does give me the desired output... However, its giving me the sum of seconds at the moment and what I would ideally want is the count of actions as well... I think this is a crosstab query (which I am certainly not good at) and while going through the help docs, I came to know that we can't have more than 1 value field in the crosstab query?

If that's true, then is it possible to create two separate crosstab queries (one for each column) and then to join them later to produce the final output?

Pls help...

Thanks.... :-)

I used this query on the RAW table:
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum(RAW.Seconds) AS SumOfSeconds
  2. SELECT RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, RAW.Category
  3. FROM RAW
  4. GROUP BY RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, RAW.Category
  5. PIVOT RAW.Actions;
  6.  
And got a similar result, although all "empty" columns will not show.
These can be "forced" when you have a table with all actions.

Nic;o)
Aug 30 '08 #15

nico5038
Expert 2.5K+
P: 3,072
When you read my first comment yo can see how to solve the two value problem.
A crosstable doesn't allow two values, but you can double the rows and "create" a combined columnheader to mimic the effect.

Nic;o)
Aug 30 '08 #16

P: 77
Hi Nic,

Could you please help me a bit more with creating a combined column header and doubling the rows... As I stated earlier, I'm not too good with this crosstab query and not sure how to go about doing this..

Your help is highly appreciated as always :-)

When you read my first comment yo can see how to solve the two value problem.
A crosstable doesn't allow two values, but you can double the rows and "create" a combined columnheader to mimic the effect.

Nic;o)
Sep 1 '08 #17

nico5038
Expert 2.5K+
P: 3,072
Start with creating a union query named "qryUnionTrick" like:
Expand|Select|Wrap|Line Numbers
  1. SELECT RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, [Category] & "-mm" AS Cat, Sum(RAW.Seconds) AS SumOfSeconds
  2. FROM RAW
  3. GROUP BY RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, [Category] & "-mm"
  4.  
  5. UNION SELECT RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, [Category] & "-count" AS Cat, count(*) AS SumOfSeconds
  6. FROM RAW
  7. GROUP BY RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, [Category] & "-count";
  8.  
Now we can create the final cross-table query based on the union like:
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum(qryUnionTrick.SumOfSeconds) AS SumOfSumOfSeconds
  2. SELECT qryUnionTrick.Report_Date, qryUnionTrick.Login_ID, qryUnionTrick.LOB_Name
  3. FROM qryUnionTrick
  4. GROUP BY qryUnionTrick.Report_Date, qryUnionTrick.Login_ID, qryUnionTrick.LOB_Name
  5. PIVOT qryUnionTrick.Cat;
  6.  
Getting the idea ?

Nic;o)
Sep 1 '08 #18

P: 77
Sort of getting it now... Just one another quick question... Will it work the same way using VBA as well ??

Start with creating a union query named "qryUnionTrick" like:
Expand|Select|Wrap|Line Numbers
  1. SELECT RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, [Category] & "-mm" AS Cat, Sum(RAW.Seconds) AS SumOfSeconds
  2. FROM RAW
  3. GROUP BY RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, [Category] & "-mm"
  4.  
  5. UNION SELECT RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, [Category] & "-count" AS Cat, count(*) AS SumOfSeconds
  6. FROM RAW
  7. GROUP BY RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, [Category] & "-count";
  8.  
Now we can create the final cross-table query based on the union like:
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum(qryUnionTrick.SumOfSeconds) AS SumOfSumOfSeconds
  2. SELECT qryUnionTrick.Report_Date, qryUnionTrick.Login_ID, qryUnionTrick.LOB_Name
  3. FROM qryUnionTrick
  4. GROUP BY qryUnionTrick.Report_Date, qryUnionTrick.Login_ID, qryUnionTrick.LOB_Name
  5. PIVOT qryUnionTrick.Cat;
  6.  
Getting the idea ?

Nic;o)
Sep 1 '08 #19

nico5038
Expert 2.5K+
P: 3,072
Yep, no problem. Even directly calling the database from e.g. VB wilt work.

Nic;o)
Sep 1 '08 #20

P: 77
Ok I'm confused a bit here.. Do you mean to say that I need to create these queries in Access and then call them from VBA? (If yes, then I don't know how to call Access queries from VBA - Help required pls........)

If this is not what you mean then in VBA, would I be creating the first query and then using its recordset, will I create the second crosstab query? (Sounds confusing to me........)

Help Plsssssssssssss.....

Yep, no problem. Even directly calling the database from e.g. VB wilt work.

Nic;o)
Sep 2 '08 #21

nico5038
Expert 2.5K+
P: 3,072
Just open the query editor and select no table, thus you'll see the top left button with the text SQL appear.
Then copy/paste my query and see the effect.

Nic;o)
Sep 2 '08 #22

P: 77
Hi Nic,

Apologies for the late response on this.

It surely works great and as required in Access Queries.

However, I'm still a bit confused as to how to carry this out in Access VBA.

Would I be creating a query statement for the first union query and then use its recordset variable in the second crosstab query to get the effect or do you recommend me to create these queries using the regular Queries and then call them using VBA?

Please assist how to carry out both the operations....

Many Thanks :-)
Sep 11 '08 #23

nico5038
Expert 2.5K+
P: 3,072
Not sure why you want these queries "in" VBA.
I guess you want to make the result visible and for that you can use a form (or sub form) based on the (final) query.

Using VBA to get the same result with record set processing will only cause the operation to last at least 8 to 10 times as looooong :-)

Can you indicate what you need to achieve ?

Nic;o)
Sep 11 '08 #24

P: 77
What needs to be achieved is to create a report in Excel combining the fields in these three queries.. So apparantly, I'd have to write these queries in VBA and then throw the output in Excel?

Not sure why you want these queries "in" VBA.
I guess you want to make the result visible and for that you can use a form (or sub form) based on the (final) query.

Using VBA to get the same result with record set processing will only cause the operation to last at least 8 to 10 times as looooong :-)

Can you indicate what you need to achieve ?

Nic;o)
Sep 12 '08 #25

nico5038
Expert 2.5K+
P: 3,072
Just check the "docmd.transferspreadsheet" command in the helpfile to see how to export a saved query to an excel workbook.

Nic;o)
Sep 12 '08 #26

Post your reply

Sign in to post your reply or Sign up for a free account.