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.
25 2045
For this you can use across table query.
For getting the 16 columns you need to create a query like: -
Select emp_id,emp_name,repdate, 'act-' & action as Col, sum(ltstatus) as ColVal from tblYours
-
group by emp_id,emp_name,repdate
-
UNION
-
Select emp_id,emp_name,repdate, 'dur-' & action as col, sum(duration) from tblYours
-
group by emp_id,emp_name,repdate
-
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)
Sorry, but this is not working....
Please suggest alternate fix...
The current code is as follows: - Public Sub generateAPR(fName$)
-
'On Error GoTo errr
-
Dim vCbk As Double, vMeal As Double, vOne2One As Double, vOpsTrg As Double, vProd As Double, vQlty As Double, vSick As Double
-
Dim vSystem As Double, vTea As Double, vTrg As Double, vVnA As Double, vSkip As Double, vClosed As Double, vPending As Double
-
Dim vXferExt As Double, vXferInt As Double, vCbkCnt As Double, vMealCnt As Double, vOne2OneCnt As Double
-
Dim vQltyCnt As Double, vSickCnt As Double, vSystemCnt As Double, vTeaCnt As Double, vTrgCnt As Double, vVnACnt As Double
-
Dim vSkipCnt As Double, vOpsTrgCnt As Double, vLoginCnt As Double
-
vStatus = SysCmd(acSysCmdSetStatus, "Please wait while APR data is being created...")
-
DoCmd.Hourglass True
-
Call conDB
-
Set rsFourth = New ADODB.Recordset
-
vSQL = "delete * from tmp_apr_raw"
-
rsFourth.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
vSQL = "select distinct(emp_id),emp_name from apr_raw where repdate>=#" & sdate & "# and repdate<=#" & edate & "#"
-
rsMain.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
rsMain.MoveFirst
-
Do While Not rsMain.EOF
-
Set rsFirst = New ADODB.Recordset
-
vSQL = "select count(*) from apr_raw where emp_id=" & rsMain(0) & " and repdate>=#" & sdate & "# and " & _
-
"repdate<=#" & edate & "#"
-
rsFirst.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
If rsFirst(0) <> 0 Then
-
Set rsFirst = New ADODB.Recordset
-
Set rsSecond = New ADODB.Recordset
-
Set rsThird = New ADODB.Recordset
-
Set rsSixth = New ADODB.Recordset
-
'finding out the distinct dates for an employee
-
vSQL = "select distinct(repdate) from apr_raw where emp_id=" & rsMain(0) & " and repdate>=#" & sdate & "# and " & _
-
"repdate<=#" & edate & "#"
-
Set rsFirst = conMain.Execute(vSQL)
-
rsFirst.MoveFirst
-
'rsFirst.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
For i = 1 To rsFirst.RecordCount
-
'finding out the distinct LOBs for each date and each employee
-
vSQL = "select distinct(lob) from apr_raw where emp_id=" & rsMain(0) & " and repdate=#" & rsFirst(0) & "#"
-
Set rsSecond = conMain.Execute(vSQL)
-
vLoginCnt = 0
-
'rsSecond.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
For j = 1 To rsSecond.RecordCount
-
'finding out the distinct categories for each date, each employee, each lob
-
vSQL = "select distinct(category) from apr_raw where emp_id=" & rsMain(0) & " and " & _
-
"repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "'"
-
Set rsSixth = conMain.Execute(vSQL)
-
For k = 1 To rsSixth.RecordCount
-
vCbk = 0: vMeal = 0: vOne2One = 0: vOpsTrg = 0: vProd = 0: vQlty = 0: vSick = 0: vSystem = 0: vTea = 0
-
vSkip = 0: vClosed = 0: vPending = 0: vXferExt = 0: vXferInt = 0: vCbkCnt = 0: vMealCnt = 0
-
vSickCnt = 0: vSystemCnt = 0: vTeaCnt = 0: vTrgCnt = 0: vVnACnt = 0: vSkipCnt = 0
-
vOpsTrgCnt = 0: vTrg = 0: vVnA = 0: vOne2OneCnt = 0: vQltyCnt = 0
-
wsday = "": wsmonth = "": wsyear = "": weday = "": wemonth = "": weyear = ""
-
'calculating Callback duration and count
-
vSQL = "select sum(duration),sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
-
"repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Callback' and " & _
-
"category='" & rsSixth(0) & "'"
-
Set rsThird = New ADODB.Recordset
-
rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
If IsNull(rsThird(0)) = False Then
-
vCbk = rsThird(0)
-
Else
-
vCbk = 0
-
End If
-
If IsNull(rsThird(0)) = False Then
-
vCbkCnt = rsThird(1)
-
Else
-
vCbkCnt = 0
-
End If
-
'calculating meal break duration and count
-
vSQL = "select sum(duration),sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
-
"repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Meal Break' and " & _
-
"category='" & rsSixth(0) & "'"
-
Set rsThird = New ADODB.Recordset
-
rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
If IsNull(rsThird(0)) = False Then
-
vMeal = rsThird(0)
-
Else
-
vMeal = 0
-
End If
-
If IsNull(rsThird(0)) = False Then
-
vMealCnt = rsThird(1)
-
Else
-
vMealCnt = 0
-
End If
-
'calculating One2One duration and count
-
vSQL = "select sum(duration),sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
-
"repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='One on One' and " & _
-
"category='" & rsSixth(0) & "'"
-
Set rsThird = New ADODB.Recordset
-
rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
If IsNull(rsThird(0)) = False Then
-
vOne2One = rsThird(0)
-
Else
-
vOne2One = 0
-
End If
-
If IsNull(rsThird(0)) = False Then
-
vOne2OneCnt = rsThird(1)
-
Else
-
vOne2OneCnt = 0
-
End If
-
'calculating Ops Training duration and count
-
vSQL = "select sum(duration),sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
-
"repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Ops Training' and " & _
-
"category='" & rsSixth(0) & "'"
-
Set rsThird = New ADODB.Recordset
-
rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
If IsNull(rsThird(0)) = False Then
-
vOpsTrg = rsThird(0)
-
Else
-
vOpsTrg = 0
-
End If
-
If IsNull(rsThird(0)) = False Then
-
vOpsTrgCnt = rsThird(1)
-
Else
-
vOpsTrgCnt = 0
-
End If
-
'calculating Quality Feedback duration and count
-
vSQL = "select sum(duration),sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
-
"repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Quality Feedback' and " & _
-
"category='" & rsSixth(0) & "'"
-
Set rsThird = New ADODB.Recordset
-
rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
If IsNull(rsThird(0)) = False Then
-
vQlty = rsThird(0)
-
Else
-
vQlty = 0
-
End If
-
If IsNull(rsThird(0)) = False Then
-
vQltyCnt = rsThird(1)
-
Else
-
vQltyCnt = 0
-
End If
-
'calculating Sick duration and count
-
vSQL = "select sum(duration),sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
-
"repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Sick' and " & _
-
"category='" & rsSixth(0) & "'"
-
Set rsThird = New ADODB.Recordset
-
rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
If IsNull(rsThird(0)) = False Then
-
vSick = rsThird(0)
-
Else
-
vSick = 0
-
End If
-
If IsNull(rsThird(0)) = False Then
-
vSickCnt = rsThird(1)
-
Else
-
vSickCnt = 0
-
End If
-
'calculating System down duration and count
-
vSQL = "select sum(duration),sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
-
"repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='System Down' and " & _
-
"category='" & rsSixth(0) & "'"
-
Set rsThird = New ADODB.Recordset
-
rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
If IsNull(rsThird(0)) = False Then
-
vSystem = rsThird(0)
-
Else
-
vSystem = 0
-
End If
-
If IsNull(rsThird(0)) = False Then
-
vSystemCnt = rsThird(1)
-
Else
-
vSystemCnt = 0
-
End If
-
'calculating Tea Break duration and count
-
vSQL = "select sum(duration),sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
-
"repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Tea Break' and " & _
-
"category='" & rsSixth(0) & "'"
-
Set rsThird = New ADODB.Recordset
-
rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
If IsNull(rsThird(0)) = False Then
-
vTea = rsThird(0)
-
Else
-
vTea = 0
-
End If
-
If IsNull(rsThird(0)) = False Then
-
vTeaCnt = rsThird(1)
-
Else
-
vTeaCnt = 0
-
End If
-
'calculating Training duration and count
-
vSQL = "select sum(duration),sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
-
"repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Training' and " & _
-
"category='" & rsSixth(0) & "'"
-
Set rsThird = New ADODB.Recordset
-
rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
If IsNull(rsThird(0)) = False Then
-
vTrg = rsThird(0)
-
Else
-
vTrg = 0
-
End If
-
If IsNull(rsThird(0)) = False Then
-
vTrgCnt = rsThird(1)
-
Else
-
vTrgCnt = 0
-
End If
-
'calculating V&A duration and count
-
vSQL = "select sum(duration),sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
-
"repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='V & A Feedback' and " & _
-
"category='" & rsSixth(0) & "'"
-
Set rsThird = New ADODB.Recordset
-
rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
If IsNull(rsThird(0)) = False Then
-
vVnA = rsThird(0)
-
Else
-
vVnA = 0
-
End If
-
If IsNull(rsThird(0)) = False Then
-
vVnACnt = rsThird(1)
-
Else
-
vVnACnt = 0
-
End If
-
'calculating Skip duration and count
-
vSQL = "select sum(duration),sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
-
"repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Skip' and " & _
-
"category='" & rsSixth(0) & "'"
-
Set rsThird = New ADODB.Recordset
-
rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
If IsNull(rsThird(0)) = False Then
-
vSkip = rsThird(0)
-
Else
-
vSkip = 0
-
End If
-
If IsNull(rsThird(0)) = False Then
-
vSkipCnt = rsThird(1)
-
Else
-
vSkipCnt = 0
-
End If
-
'calculating Production duration
-
vSQL = "select sum(duration) from apr_raw where emp_id=" & rsMain(0) & " and " & _
-
"repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and LT='Production Time' and " & _
-
"category='" & rsSixth(0) & "'"
-
Set rsThird = New ADODB.Recordset
-
rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
If IsNull(rsThird(0)) = False Then
-
vProd = rsThird(0)
-
Else
-
vProd = 0
-
End If
-
'calculating # of closed cases
-
vSQL = "select sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
-
"repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Closed' and " & _
-
"category='" & rsSixth(0) & "'"
-
Set rsThird = New ADODB.Recordset
-
rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
If IsNull(rsThird(0)) = False Then
-
vClosed = rsThird(0)
-
Else
-
vClosed = 0
-
End If
-
'calculating # of Pending cases
-
vSQL = "select sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
-
"repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Pending' and " & _
-
"category='" & rsSixth(0) & "'"
-
Set rsThird = New ADODB.Recordset
-
rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
If IsNull(rsThird(0)) = False Then
-
vPending = rsThird(0)
-
Else
-
vPending = 0
-
End If
-
'calculating # of Internal XFER cases
-
vSQL = "select sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
-
"repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Transferred Internal' and " & _
-
"category='" & rsSixth(0) & "'"
-
Set rsThird = New ADODB.Recordset
-
rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
If IsNull(rsThird(0)) = False Then
-
vXferInt = rsThird(0)
-
Else
-
vXferInt = 0
-
End If
-
'calculating # of External XFER cases
-
vSQL = "select sum(actioncount) from apr_raw where emp_id=" & rsMain(0) & " and " & _
-
"repdate=#" & rsFirst(0) & "# and lob='" & rsSecond(0) & "' and actions='Transferred External' and " & _
-
"category='" & rsSixth(0) & "'"
-
Set rsThird = New ADODB.Recordset
-
rsThird.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
If IsNull(rsThird(0)) = False Then
-
vXferExt = rsThird(0)
-
Else
-
vXferExt = 0
-
End If
-
'inserting the records in the temp table
-
Set rsFourth = New ADODB.Recordset
-
vSQL = "select * from tmp_apr_raw"
-
rsFourth.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
rsFourth.AddNew
-
rsFourth(0) = rsMain(0)
-
vSQL = "select tl_id,tl_name,om_id,om_name,lob_mapping from apr_raw where emp_id=" & rsMain(0) & _
-
" and repdate=#" & rsFirst(0) & "#"
-
Set rsFifth = New ADODB.Recordset
-
rsFifth.Open vSQL, StrCon, adOpenDynamic, adLockBatchOptimistic
-
rsFourth(1) = rsMain(1)
-
rsFourth(2) = rsFifth(0)
-
rsFourth(3) = rsFifth(1)
-
rsFourth(4) = rsFifth(2)
-
rsFourth(5) = rsFifth(3)
-
rsFourth(6) = rsSecond(0)
-
rsFourth(7) = rsSixth(0)
-
rsFourth(8) = rsFirst(0)
-
wsday = Day(DateAdd("d", -(Weekday(rsFirst(0), vbFriday) - 1), rsFirst(0)))
-
wsmonth = Format(DateAdd("d", -(Weekday(rsFirst(0), vbFriday) - 1), rsFirst(0)), "mmm")
-
wsyear = Right(Year(DateAdd("d", -(Weekday(rsFirst(0), vbFriday) - 1), rsFirst(0))), 2)
-
weday = Day(DateAdd("d", -(Weekday(rsFirst(0), vbFriday) - 1) + 6, rsFirst(0)))
-
wemonth = Format(DateAdd("d", -(Weekday(rsFirst(0), vbFriday) - 1) + 6, rsFirst(0)), "mmm")
-
weyear = Right(Year(DateAdd("d", -(Weekday(rsFirst(0), vbFriday) - 1) + 6, rsFirst(0))), 2)
-
rsFourth(9) = "WE_" & weday & wemonth & weyear 'weekname
-
rsFourth(10) = Format(rsFirst(0), "mmm")
-
rsFourth(11) = vCbk
-
rsFourth(12) = vMeal
-
rsFourth(13) = vOne2One
-
rsFourth(14) = vOpsTrg
-
rsFourth(15) = vProd
-
rsFourth(16) = vQlty
-
rsFourth(17) = vSick
-
rsFourth(18) = vSystem
-
rsFourth(19) = vTea
-
rsFourth(20) = vTrg
-
rsFourth(21) = vVnA
-
rsFourth(22) = vSkip
-
rsFourth(23) = vClosed
-
rsFourth(24) = vPending
-
rsFourth(25) = vXferExt
-
rsFourth(26) = vXferInt
-
rsFourth(27) = vCbkCnt
-
rsFourth(28) = vMealCnt
-
rsFourth(29) = vOne2OneCnt
-
rsFourth(30) = vOpsTrgCnt
-
rsFourth(31) = vQltyCnt
-
rsFourth(32) = vSickCnt
-
rsFourth(33) = vSystemCnt
-
rsFourth(34) = vTeaCnt
-
rsFourth(35) = vTrgCnt
-
rsFourth(36) = vVnACnt
-
rsFourth(37) = vSkipCnt
-
If j = 1 And k = 1 Then
-
vLoginCnt = 1
-
Else
-
vLoginCnt = 0
-
End If
-
rsFourth(38) = vLoginCnt
-
rsFourth(39) = rsFifth(4)
-
rsFourth.UpdateBatch
-
rsSixth.MoveNext
-
Next
-
rsSecond.MoveNext
-
Next
-
rsFirst.MoveNext
-
Next
-
End If
-
rsMain.MoveNext
-
Loop
-
Call createAPR
-
vsday = Day(CDate(sdate))
-
vsmonth = Format(sdate, "mmm")
-
vsyear = Right(Year(sdate), 2)
-
veday = Day(CDate(edate))
-
vemonth = Format(edate, "mmm")
-
veyear = Right(Year(edate), 2)
-
' fName = "AgentRaw_" & vsday & vsmonth & vsyear & "_" & veday & vemonth & veyear & ".xls"
-
oWB.SaveAs vRepPath & fName
-
oWB.Close
-
oExcel.Quit
-
Set oExcel = Nothing
-
Set oWB = Nothing
-
Set oWS = Nothing
-
MsgBox "Please collect the APR from " & Left(vRepPath, Len(vRepPath) - 1)
-
'rsExcel.Close
-
Set conExcel = Nothing
-
Set rsMain = Nothing
-
Set rsFirst = Nothing
-
Set rsSecond = Nothing
-
Set rsThird = Nothing
-
Set rsFourth = Nothing
-
Set rsFifth = Nothing
-
Set rsSixth = Nothing
-
Set conMain = Nothing
-
vStatus = SysCmd(acSysCmdClearStatus)
-
DoCmd.Hourglass False
-
Exit Sub
-
errr:
-
MsgBox Err.Description
-
'rsExcel.Close
-
rsMain.Close
-
Set rsExcel = Nothing
-
Set conExcel = Nothing
-
Set rsMain = Nothing
-
Set rsFirst = Nothing
-
Set rsSecond = Nothing
-
Set rsThird = Nothing
-
Set rsFourth = Nothing
-
Set rsFifth = Nothing
-
Set rsSixth = Nothing
-
Set conMain = Nothing
-
vStatus = SysCmd(acSysCmdClearStatus)
-
DoCmd.Hourglass False
-
End Sub
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
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
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
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
Sorry, but this is not working....
Please suggest alternate fix...
Can you elaborate what's not working ?
Nic;o)
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)
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..
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)
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)
I used this query on the RAW table: -
TRANSFORM Sum(RAW.Seconds) AS SumOfSeconds
-
SELECT RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, RAW.Category
-
FROM RAW
-
GROUP BY RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, RAW.Category
-
PIVOT RAW.Actions;
-
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)
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: -
TRANSFORM Sum(RAW.Seconds) AS SumOfSeconds
-
SELECT RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, RAW.Category
-
FROM RAW
-
GROUP BY RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, RAW.Category
-
PIVOT RAW.Actions;
-
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)
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)
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)
Start with creating a union query named "qryUnionTrick" like: -
SELECT RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, [Category] & "-mm" AS Cat, Sum(RAW.Seconds) AS SumOfSeconds
-
FROM RAW
-
GROUP BY RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, [Category] & "-mm"
-
-
UNION SELECT RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, [Category] & "-count" AS Cat, count(*) AS SumOfSeconds
-
FROM RAW
-
GROUP BY RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, [Category] & "-count";
-
Now we can create the final cross-table query based on the union like: -
TRANSFORM Sum(qryUnionTrick.SumOfSeconds) AS SumOfSumOfSeconds
-
SELECT qryUnionTrick.Report_Date, qryUnionTrick.Login_ID, qryUnionTrick.LOB_Name
-
FROM qryUnionTrick
-
GROUP BY qryUnionTrick.Report_Date, qryUnionTrick.Login_ID, qryUnionTrick.LOB_Name
-
PIVOT qryUnionTrick.Cat;
-
Getting the idea ?
Nic;o)
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: -
SELECT RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, [Category] & "-mm" AS Cat, Sum(RAW.Seconds) AS SumOfSeconds
-
FROM RAW
-
GROUP BY RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, [Category] & "-mm"
-
-
UNION SELECT RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, [Category] & "-count" AS Cat, count(*) AS SumOfSeconds
-
FROM RAW
-
GROUP BY RAW.Report_Date, RAW.Login_ID, RAW.LOB_Name, [Category] & "-count";
-
Now we can create the final cross-table query based on the union like: -
TRANSFORM Sum(qryUnionTrick.SumOfSeconds) AS SumOfSumOfSeconds
-
SELECT qryUnionTrick.Report_Date, qryUnionTrick.Login_ID, qryUnionTrick.LOB_Name
-
FROM qryUnionTrick
-
GROUP BY qryUnionTrick.Report_Date, qryUnionTrick.Login_ID, qryUnionTrick.LOB_Name
-
PIVOT qryUnionTrick.Cat;
-
Getting the idea ?
Nic;o)
Yep, no problem. Even directly calling the database from e.g. VB wilt work.
Nic;o)
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)
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)
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 :-)
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)
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)
Just check the "docmd.transferspreadsheet" command in the helpfile to see how to export a saved query to an excel workbook.
Nic;o)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: abcd |
last post by:
kutthaense Secretary Djetvedehald H. Rumsfeld legai predicted eventual
vicmadhlary in Iraq mariyu Afghmadhlaistmadhla, kaani jetvedehly after "a
ljetvedehg, hard slog," mariyu vede legai pressed...
|
by: William Gill |
last post by:
I have been trying to pass parameters as indicated in the api.
when I use:
sql= 'select * from %s where cusid = %s ' % name,recID)
Cursor.execute(sql)
it works fine, but when I try :
sql=...
|
by: Tarren |
last post by:
Hi:
The problem I am having is when I validate an xml file to a schema, it is
erroring out every element. I think this has something to do with me
defining/referencing the namespaces. I have...
|
by: Jeremy Weiss |
last post by:
I'm trying to build a database that will handle the monthly billing needs of
a small company. I'm charting everything out and here's what I see:
table for customers
sub table to track payments...
|
by: Chad |
last post by:
I have a problem that I am desperate to understand.
It involves dynamically adding controls to a Table control that is built as a result of performing a database query.
I am not looking to...
|
by: Aaron |
last post by:
Complete code follows.
I am new to .NET programming (and programming in general) and I am having a
difficult time understanding how to fill a variable in one sub, and then
access it from...
|
by: Siv |
last post by:
Hi,
If I run the following:
strSQL = "Select * FROM Clients;"
da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter
cb = New OleDb.OleDbCommandBuilder(da) ...
|
by: rdemyan via AccessMonster.com |
last post by:
My application has a table that contains information on buildings. The
building data comes from another database and is imported from a spreadsheet
into my application.
Originally, I thought...
|
by: cryon.b |
last post by:
Hi To All,
I took up the IBM sample testfor Exam 700 today and I have the test
tomorrow,I have some questions for which Iam not sure about the right
answer,can anyone please guide me as to what is...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |