作者 主題: access sql syntax  (閱讀 6895 次)

0 會員 與 1 訪客 正在閱讀本文。

小徒兒

  • 鑽研的研究生
  • *****
  • 文章數: 621
    • 檢視個人資料
access sql syntax
« 於: 2006-09-29 02:03 »

代碼: [選擇]

<!--#include file="dbconn.asp"-->
<script Language="VBScript">
sub fun_excel2()
RegionID = selRegionFilter.value



if ViewActive.checked = "True"  then
msgbox("view active checked regionid" & ViewActive.checked & RegionID)
sqlstring = "SELECT employees.id as id, employees.lastname + ', ' +employees.firstname AS name FROM employees WHERE (((employees.RegionID)="& RegionID & ") AND ((employees.Active)=True))ORDER BY employees.LastName, employees.FirstName;"

if RegionID=12  then
sqlstring = "SELECT employees.id as id, employees.lastname + ', ' +employees.firstname AS name FROM employees WHERE ( ((employees.Active)=True))ORDER BY employees.LastName, employees.FirstName;"
'msgbox("am i trigger? for all checked")
end if
else
msgbox("view active No checked regionid" & ViewActive.checked & RegionID)

sqlstring = "SELECT employees.id as id, employees.lastname + ', ' +employees.firstname AS name FROM employees WHERE (((employees.RegionID)="& RegionID & "))ORDER BY employees.LastName, employees.FirstName;"

if RegionID=12  then
sqlstring = "SELECT employees.id as id, employees.lastname + ', ' +employees.firstname AS name FROM employees ORDER BY employees.LastName, employees.FirstName;"
'msgbox("am i trigger? for all unchecked")


end if

end if



ServerStr = "http://<%=Request.ServerVariables("SERVER_NAME")%>"
set rds = CreateObject("RDS.Dataspace")
Set df = rds.CreateObject("iacRDSObjAccess.rsop",ServerStr)
strDivision = "<%=session("Division")%>"

if strDivision="WB" then
set rs = df.ReturnRs("skillset_DT97.mdb",sqlstring )

       
        else
        set rs = df.ReturnRs("skillset_DT97_db.mdb",sqlstring )

        end if
       ' test.innerHTML="SELECT employees.id as id, employees.lastname + ', ' +employees.firstname AS name FROM employees WHERE (((employees.RegionID)="& RegionID & ")) ORDER BY employees.LastName, employees.FirstName;"
       
       
       
selEmployee.Options.length =0

Do While Not rs.EOF
Set objOption = Document.createElement("OPTION")
objOption.text = rs("name")
objOption.value = rs("id")
                rs.MoveNext
j = j + 1
selEmployee.Add(objOption)
Loop
       
       
end sub

sub fun_excel()
Dim rds,rs,df,ServerStr
dim courseID
dim sessionID
EmployeeID=selEmployee.value

'msgbox(selEmployee.value)
if selEmployee.value <>"" then
EmployeeID = selEmployee.value
else
EmployeeID = 0
end if

ActiveEmployee.checked = "false"

ServerStr = "http://<%=Request.ServerVariables("SERVER_NAME")%>"
set rds = CreateObject("RDS.Dataspace")
Set df = rds.CreateObject("iacRDSObjAccess.rsop",ServerStr)
strDivision = "<%=session("Division")%>"

if strDivision="WB" then
set rs = df.ReturnRs("skillset_DT97.mdb","SELECT  Employees.FirstName, Employees.LastName, Employees.Password, Employees.AsiaName, Employees.Role as Role, Employees.RegionID as Region , Employees.Email as Email ,Active,EmployeeNumber FROM Employees WHERE (((Employees.ID)=" & employeeID &"));" )

       
        else
        set rs = df.ReturnRs("skillset_DT97_db.mdb","SELECT  Employees.FirstName, Employees.LastName, Employees.Password, Employees.AsiaName, Employees.Role as Role, Employees.RegionID as Region ,Employees.Email as Email,Active,EmployeeNumber  FROM Employees WHERE (((Employees.ID)=" & employeeID &"));" )

        end if
       

'test.innerHTML= "SELECT Employees.EmployeeNumber, Employees.FirstName, Employees.LastName, Employees.Password, Role AS Role, Regions as Region, Employees.AsiaName FROM (Employees INNER JOIN Regions ON Employees.RegionID = Regions.ID) INNER JOIN Roles ON Employees.Role = Roles.ID WHERE (((Employees.ID)=" & employeeID &"));"

'Do While Not rs.EOF
' Set objOption = Document.createElement("OPTION")
' objOption.text = rs("name")
' objOption.value = rs("id")
        '        rs.MoveNext
' j = j + 1
' selCurrilum.Add(objOption)
'Loop
'set rs=nothing

inputEmployeeNumber.value = rs("EmployeeNumber")
inputFirstName.value = rs("FirstName")
inputLastName.value = rs("LastName")
inputEmployeeNumber.value = rs("EmployeeNumber")

if (rs("Active"))= true then ActiveEmployee.checked = "true" end if

if len((rs("Email")))>0 then

inputEmail.value = rs("Email")

else
inputEmail.value=""
end if

'AsiaName.value = rs("AsiaName")
'Password.value = rs("Password")
SelRole.value = rs("Role")
SelRegion.value = rs("Region")
        'msgbox(inputEmployeeNumber.value)

end sub

sub inactive()
'msgbox(inputEmployeeNumber.value )

ServerStr = "http://<%=Request.ServerVariables("SERVER_NAME")%>"
set rds = CreateObject("RDS.Dataspace")
Set df = rds.CreateObject("iacRDSObjAccess.rsop",ServerStr)
strDivision = "<%=session("Division")%>"

if strDivision="WB" then
set rs = df.ReturnRs("skillset_DT97.mdb","select  employees.Active from employees WHERE (((employees.EmployeeNumber)=" & inputEmployeeNumber.value & "));" )
        else
        set rs = df.ReturnRs("skillset_DT97_db.mdb","select  employees.Active from employees WHERE (((employees.EmployeeNumber)=" & inputEmployeeNumber.value & "));" )

        end if

if (rs("Active")= true) then


if strDivision="WB" then
set rs = df.ReturnRs("skillset_DT97.mdb","UPDATE employees SET employees.Active = false WHERE (((employees.EmployeeNumber)=" & inputEmployeeNumber.value & "));" )
        else
        set rs = df.ReturnRs("skillset_DT97_db.mdb","UPDATE employees SET employees.Active = false WHERE (((employees.EmployeeNumber)=" & inputEmployeeNumber.value & "));" )

        end if
       
        else
       
        if strDivision="WB" then
set rs = df.ReturnRs("skillset_DT97.mdb","UPDATE employees SET employees.Active = true WHERE (((employees.EmployeeNumber)=" & inputEmployeeNumber.value & "));" )
        else
        set rs = df.ReturnRs("skillset_DT97_db.mdb","UPDATE employees SET employees.Active = true WHERE (((employees.EmployeeNumber)=" & inputEmployeeNumber.value & "));" )

        end if
       
       
        end if
       

       
end sub



sub add()
        dim FirstName,rsTemp,TempNumber                                                                 
FirstName = inputFirstname.value
LastName = inputLastname.value
Email = inputEmail.value

if Email = "" then Email = "  " end if

'strPassword = inputPassword.value
'msgbox Email & strPassword
Region = SelRegion.value
Role = Selrole.value
' EmployeeNumber=inputEmployeeNumber.value                                         
                                                                                   
        'msgbox "insert into Employee ( EmployeeNumber,LastName, FirstName, Role ,RegionID)   VALUES ("&EmployeeNumber&", "&LastName&", " & FirstName &", " & "Region" & ", " & Role )"
                                                                                           
ServerStr = "http://<%=Request.ServerVariables("SERVER_NAME")%>"                   
set rds = CreateObject("RDS.Dataspace")                                             
Set df = rds.CreateObject("iacRDSObjAccess.rsop",ServerStr)                         
on error resume next                                                               
'msgbox "insert into Attendees ( SessionID, AttendeeID)  VALUES ("&sessionID&", "&AttendeeID&")"

'msgbox("insert into Employees ( EmployeeNumber,LastName, FirstName, RegionID,Role )  VALUES ("&EmployeeNumber&", "&LastName&", " & FirstName &", " & Region & ", " & Role & ");" )

strDivision = "<%=session("Division")%>"

if strDivision="WB" then
'df.ReturnRs "skillset_DT97.mdb", "insert into Employees ( EmployeeNumber,LastName, FirstName, RegionID,Role,Email,Password )  VALUES ("&EmployeeNumber&", '"&LastName&"', '" & FirstName &"', " & Region & ", " & Role & ",'" &  Email & "','" & strPassword &"');"


set rsTemp = df.ReturnRs("skillset_DT97.mdb","SELECT Max(EmployeeNumber)+1 AS nextEmployeeNu FROM Employees;")

df.ReturnRs "skillset_DT97.mdb", "insert into Employees ( EmployeeNumber,LastName, FirstName, RegionID,Role,Email,Active)  VALUES ( " & rsTemp("nextEmployeeNu") & " , '"&LastName&"', '" & FirstName &"', " & Region & ", " & Role & ",'" &  Email & "', Yes);"

'df.ReturnRs "skillset_DT97.mdb", "insert into Employees ( LastName, FirstName, RegionID,Role,Email)  VALUES ('" & LastName&"', '" & FirstName &"', " & Region & ", " & Role & ",'" &  Email & "');"

'msgbox  "insert into Employees ( LastName, FirstName, RegionID,Role,Email)  VALUES ('" & LastName&"', '" & FirstName &"', " & Region & ", " & Role & ",'" &  Email & "');"

else

set rsTemp = df.ReturnRs("skillset_DT97_db.mdb","SELECT Max(EmployeeNumber)+1 AS nextEmployeeNu FROM Employees;")

df.ReturnRs "skillset_DT97_db.mdb",  "insert into Employees ( EmployeeNumber,LastName, FirstName, RegionID,Role,Email,Active)  VALUES ( " & rsTemp("nextEmployeeNu") & " , '"&LastName&"', '" & FirstName &"', " & Region & ", " & Role & ",'" &  Email & "',Yes);"

'df.ReturnRs "skillset_DT97_db.mdb", "insert into Employees ( LastName, FirstName, RegionID,Role,Email )  VALUES ( '"&LastName&"', '" & FirstName &"', " & Region & ", " & Role & ",'" &  Email & "');"

end if

test.innerHTML= "insert into Employees ( EmployeeNumber,LastName, FirstName, RegionID,Role,Email)  VALUES ( " & rsTemp("nextEmployeeNu") & " , '"&LastName&"', '" & FirstName &"', " & Region & ", " & Role & ",'" &  Email & "');"
'test.innerHTML= "insert into Employees ( EmployeeNumber,LastName, FirstName, RegionID,Role,Email,Password )  VALUES ("&EmployeeNumber&", '"&LastName&"', '" & FirstName &"', " & Region & ", " & Role & ",'" &  Email & "','" & strPassword &"');"

msgbox("FSE Data added" )

'window.location.reload true


'fun_excel

exit sub

end sub

sub closewindow()
window.close                                                                             
end sub                                                                                     






</script>


<%
On Error Resume Next

Set Conn = Server.CreateObject("ADODB.Connection")
Set Cmd = Server.CreateObject("ADODB.Command")
Set RSRegion = Server.CreateObject("ADODB.Recordset")
Set RSRole = Server.CreateObject("ADODB.Recordset")
Set RS = Server.CreateObject("ADODB.Recordset")
Set RSSession = Server.CreateObject("ADODB.Recordset")

conn.Open session("connectstring")


RS.open "SELECT employees.id as id, employees.lastname + ', ' +employees.firstname AS name FROM employees ORDER BY employees.LastName, employees.FirstName;",conn,1,3
RSRole.open "SELECT Roles.ID, Roles.Name FROM Roles ORDER BY Roles.ID;",conn,1,3
RSRegion.open "SELECT Regions.ID, Regions.Name from Regions;",conn,1,3



if Err.Number<> 0 then

response.write err.description
response.write ("The database has encountered an error while trying to access.")

response.end
end if
%>
<html>

  <head>
  <link href="styles.css" rel="stylesheet" type="text/css" />
<style type="text/css">
<!--
.barItem {
cursor: hand;
color: #666666;
font-size: 14;
font-family: Arial, Helvetica, sans-serif;
line-height: 42px;
}
.barItem:hover {
cursor: hand;
color:  #C84777;
font-size: 14
}
a. { cursor: hand; font-size: 12; color: navy; text-decoration: none }
a:active {
cursor: hand;
color: #666666
}
a.cc:hover {
cursor: hand;
color: #333333;
text-decoration: underline
}
.box {
position: absolute
}

-->
</style>
<link href="SpryAssets/SpryMenuBarHorizontal.css" rel="stylesheet" type="text/css" />
<style type="text/css">
<!--
-->
</style>
  </head>
<body scroll="yes">


<div name="test" id = "test"> </div>
   <table width="100%" border="0" cellspacing="0" cellpadding="10">
 <tr>
     
 <td width="38%" align="left" valign="top">
 <!--
 <span class="style3">Employee Number: </span>
   
-->
 <span class="style3">Region: </span>

       <select name="selRegionFilter" onchange="fun_excel2"  style="color:Black;border-color:Silver;border-style:Solid;font-family:Arial;font-size:13px;width:200px;" >
     <option value=12 >All        </option>
       <option value=1 >Americas        </option>
<option value=2 >Europe          </option>
<option value=3 >Singapore       </option>
<option value=4 >China           </option>
<option value=5 >Japan           </option>
<option value=6 >Korea           </option>
<option value=7 >Thailand        </option>
<option value=9 >Malaysia        </option>
<option value=10 >Philippines     </option>
<option value=11 >Taiwan          </option>
 </select>
 <INPUT TYPE=CHECKBOX NAME="ViewActive" onClick="fun_excel2"><span class="style3">View Active Only</span>

 <br>
       <input name="inputEmployeeNumber" TYPE="HIDDEN"  id="inputEmployeeNumber"  />
         </br><span class="style3">First Name: </span>
       <input name="inputFirstName" type="text" id="inputFirstName" style="color:Black;border-color:Silver;border-style:Solid;font-family:Arial;font-size:13px;width:300px;" />
     </br><span class="style3">Last Name: </span>
       <input name="inputLastName" type="text" id="inputLastName" style="color:Black;border-color:Silver;border-style:Solid;font-family:Arial;font-size:13px;width:300px;" />
    <INPUT TYPE=CHECKBOX NAME="ActiveEmployee"><span class="style3">Active</span>

     </br><span class="style3">Email: </span>
       <input name="inputEmail" type="text" id="inputEmail" style="color:Black;border-color:Silver;border-style:Solid;font-family:Arial;font-size:13px;width:300px;" />
   
  </br><span class="style3">Role: </span>
       <select name="selRole" type="text" id="selRole" style="color:Black;border-color:Silver;border-style:Solid;font-family:Arial;font-size:13px;width:300px;" />
   <option value =0> </option>
    <%
while not RSRole.EOF
Response.Write ("<option ")
Response.write("value=" & RSRole(0) & ">")
Response.Write (RSRole(1))
Response.Write ("</option>")
RSRole.MoveNext
wend
%>
</select>
  </br> <span class="style3">Region: </span>
         <select name="SelRegion" type="text" id="SelRegion" style="color:Black;border-color:Silver;border-style:Solid;font-family:Arial;font-size:13px;width:300px;" />
  <option value =0> </option>
       <%
while not RSRegion.EOF
Response.Write ("<option ")
Response.write("value=" & RSRegion(0) & ">")
Response.Write (RSRegion(1))
Response.Write ("</option>")
RSRegion.MoveNext
wend
%>
</select>
 
 
    <input name="buttonNew" type="button" id="buttonNew" onClick="add" value="   New  "/> 
   
  <input name="buttonDelete" type="button" id="buttonDelete" onClick="closeWindow" value="  close  "/>
  <input name="buttonInactive" type="button" id="buttonInactive" onClick="inactive" value="  Inactive/Active  "/>
 
                </span>
        <br>
   </p></td>

 <td width="52%" align="left" valign="top">
 <span class="style3">
Employees:</span><br>
<select id="selEmployee" name="selEmployee" onchange="fun_excel" size="7" style="color:Black;border-color:Silver;border-style:Solid;font-family:Arial;font-size:13px;width:450px;">
<%
while not RS.EOF
Response.Write ("<option ")
Response.write("value=" & RS(0) & ">")
Response.Write (RS(1))
Response.Write ("</option>")
RS.MoveNext
wend
%>
</select>
</td>
</tr>
 <tr>     
 <td width="88%" align="left" valign="top">
   <table width="200" border="0">
   <tr> <p></p>
     <td>   
        </p></td>
   </tr>
             
   
   
   
   
   
 </table>   </td>
 <td width="2%" align="left" valign="middle">
   <table width="2%" border="0">
       
     
   </table>   </td>
 
<td width="10%"></td>
</tr>
</table>

</body>
</html>

List of the references that you must set when you work with Access 2000 or Access 97 database

http://support.microsoft.com/kb/197110/en-us

http://72.14.205.104/search?q=cache:jb1HP--JlQMJ:bbs.flash2u.com.tw/FlashTopic18622_148_8_1.htm+access+%E8%A8%BB%E8%A7%A3&hl=en&gl=us&ct=clnk&cd=4&lr=lang_zh-TW


***將活動日期以月月/年年年年顯示Format([ActivityTime],"mm\/yyyy")


***欄位與欄位的字串相加
[Month] & "/" & [Year]

***取出參照日期的年及月Year([RefDate]) & "/" & Month([refdate])

*** 取出輸入日期enterdon及時間enteredAt_1, 再找出第一次回覆的時間,
找出經過了多少小時,再透過avg 取平均值

Avg(DateDiff("h",DateAdd("d",[enteredon],[enteredAt_1]),[activitytime]))

*** 取出輸入日期enterdon及時間enteredAt_1, 再找出第一次回覆的時間,
找出經過了多少小時,再透過max 取最大值

Max(DateDiff("h",DateAdd("d",[enteredon],[enteredAt_1]),[activitytime]))

*** 取出輸入日期enterdon及時間enteredAt_1, 再找出第一次回覆的時間,
找出經過了多少小時,再透過min 取最小值

Min(DateDiff("h",DateAdd("d",[enteredon],[enteredAt_1]),[activitytime]))

*** 特定table 的特定欄位        
[f_activity_local].[Initial State]


*** 將得到的時間顯示為 2006/05/10    
Year([ActivityTime]) & "/" & Format(Month([ActivityTime]),"00") & "/" & Format(Day([ActivityTime]),"00")


WHERE

***時間判斷(((Issue_record_local.IssueDate)>#7/31/2005#)

***字串判斷((ProcessedActivities.[Initial State])="New")

***字串判斷((Issue_record_local.ModifiedBy_1)="Boon Kheong Tiah")

***空值判斷((testIssues.issuenumber) Is Null))

***模糊判斷((Issue_record_memo_local.IssueNumber) Like [Issue_record_local].[issuenumber] & "*"))


*** group by
GROUP BY Format([ActivityTime],"mm\/yyyy")


*** order by
ORDER BY Format([ActivityTime],"mm\/yyyy");



----------sub query



***判斷是否在今天之前,或在4/6/1997以後>#4/6/1997# And <Date()


***判斷CountOfIssueNumber是否為空值
如果是,就給0,如果不是,就顯示CountOfIssueNumber
IIf(IsNull([subqClosedIssueCount].[CountOfIssueNumber]),0,[subqClosedIssueCount].[CountOfIssueNumber])


***找出新的issue數目,以輸入日來判斷當天此issue為新的issue,以count來計算資料比數SELECT Issue_record_local.EnteredOn, Count(Issue_record_local.IssueNumber) AS CountOfIssueNumber
FROM Issue_record_local
GROUP BY Issue_record_local.EnteredOn
ORDER BY Issue_record_local.EnteredOn;


***透過判斷DateClosed 是否為空值,以count來計算資料比數,以結案日來groupSELECT Issue_record_local.DateClosed, Count(Issue_record_local.IssueNumber) AS CountOfIssueNumber
FROM Issue_record_local
GROUP BY Issue_record_local.DateClosed
HAVING (((Issue_record_local.DateClosed) Is Not Null))
ORDER BY Issue_record_local.DateClosed;



***調整ActivityTime時間格式為yyyy/mm/dd,再作group跟計算資料比數
SELECT Year([ActivityTime]) & "/" & Format(Month([ActivityTime]),"00") & "/" & Format(Day([ActivityTime]),"00") AS Expr1, Count(f_activity_local.ActivityIssueNumber) AS CountOfActivityIssueNumber
FROM f_activity_local
WHERE (((f_activity_local.ActivityTime)>#5/1/2006#) AND ((f_activity_local.ActivityOwner)="xxxx"))
GROUP BY Year([ActivityTime]) & "/" & Format(Month([ActivityTime]),"00") & "/" & Format(Day([ActivityTime]),"00")
ORDER BY Year([ActivityTime]) & "/" & Format(Month([ActivityTime]),"00") & "/" & Format(Day([ActivityTime]),"00");



***兩個先去掉null再相減是也IIf(IsNull([subqClosedIssueCountWitsTeam].[CountOfIssueNumber]),0,[subqClosedIssueCountWitsTeam].[CountOfIssueNumber])
-IIf(IsNull([subqNewIssueCountWitsTeam].[CountOfIssueNumber]),0,[subqNewIssueCountWitsTeam].[CountOfIssueNumber])



***找出模組為ultra,但是record type 並非為enhancement及eval
SELECT Issue_record_local.DateClosed, Count(Issue_record_local.IssueNumber) AS CountOfIssueNumber
FROM Issue_record_local
WHERE (((Issue_record_local.Type) Not Like "*enhancement*" And
(Issue_record_local.Type) Not Like "*eval*") AND
((Issue_record_local.ModelNumber) Like "ultra"))
GROUP BY Issue_record_local.DateClosed
HAVING (((Issue_record_local.DateClosed) Is Not Null))
ORDER BY Issue_record_local.DateClosed;



UPDATE Skills
SET Skills.BackupOldName =Skills.Name

***找出現在還沒結案的(open)的issue
SELECT Issue_Record.IssueNumber, Issue_Record.Status, Issue_Record.EnteredOn, Issue_Record.LastActivityDate, Issue_Record.Severity
FROM Issue_Record
WHERE (((Issue_Record.Status)<>"*closed*"));


***找出subquery
SELECT Month([IssueDate]) & "/" & Year([IssueDate]) AS Month,
Sum(qryDailyCountNewIssues.CountOfIssueNumber) AS SumOfCountOfIssueNumber,
Avg(qryDailyCountNewIssues.CountOfIssueNumber) AS AvgOfCountOfIssueNumber
FROM qryDailyCountNewIssues
GROUP BY Month([IssueDate]) & "/" & Year([IssueDate]), Year([IssueDate]), Month([IssueDate]);


***找出一個issue經歷了多少時間,從tblElapsedTime去除以24求出幾天,除以30求出幾個月
SELECT tblElapsedTime.IssueNumber, tblElapsedTime.State, [ElapsedTime]/24 AS Days, tblElapsedTime.DateIn, StateIndex.Index, Int([ElapsedTime]/24/30) AS Period, Issue_record_local.Owner, Issue_record_local.Status
FROM (tblElapsedTime LEFT JOIN StateIndex ON tblElapsedTime.State = StateIndex.Name) INNER JOIN Issue_record_local ON tblElapsedTime.IssueNumber = Issue_record_local.IssueNumber
WHERE (((Issue_record_local.Status) Not Like "*closed*") AND ((tblElapsedTime.DateOut) Is Null))
ORDER BY [ElapsedTime]/24 DESC , Int([ElapsedTime]/24/30) DESC;

***找出正在進行中的process,並以Issue及相關Activity的時間來排序SELECT ProcessedActivities.ActivityIssueNumber,
 ProcessedActivities.ActivityTime,
 ProcessedActivities.[Initial State],
ProcessedActivities.[Resultant State],
DateAdd("d",[EnteredOn],[EnterTime]) AS Entered
FROM ProcessedActivities INNER JOIN Issue_record_local
ON ProcessedActivities.ActivityIssueNumber = Issue_record_local.IssueNumber
WHERE (((ProcessedActivities.[Resultant State])="new"
Or (ProcessedActivities.[Resultant State])="initial response"
Or (ProcessedActivities.[Resultant State])="assigned/working"
Or (ProcessedActivities.[Resultant State])="resolution determined"
Or (ProcessedActivities.[Resultant State])="resolution available"
Or (ProcessedActivities.[Resultant State])="resolution implemented"
Or (ProcessedActivities.[Resultant State])="complete/customer accepted"))
ORDER BY ProcessedActivities.ActivityIssueNumber, ProcessedActivities.ActivityTime;




*** subquery如果ActivityIsseNumber是屬於tiah,並且狀態及擁有者非空白
SELECT ProcessedActivities.ActivityIssueNumber, ProcessedActivities.ActivityTime, ProcessedActivities.[Initial State], ProcessedActivities.[Resultant State], ProcessedActivities.[Initial Owner], ProcessedActivities.[Resultant Owner], Issue_record_local.Owner, Issue_record_local.Originator, Issue_record_local.Type, Issue_record_local.PPM, Issue_record_local.DDTS, DateAdd("d",[EnteredOn],[EnterTime]) AS Entered
FROM ProcessedActivities INNER JOIN Issue_record_local ON ProcessedActivities.ActivityIssueNumber = Issue_record_local.IssueNumber
WHERE (
               (
                  (ProcessedActivities.ActivityIssueNumber) In
                  (SELECT ProcessedActivities.ActivityIssueNumber
                     FROM ProcessedActivities
                     WHERE (((ProcessedActivities.[Resultant Owner]) Like "*tiah*"))
                  )
               )
               AND
               (
                  ((ProcessedActivities.[Resultant State])<>"")
                  OR
                ((ProcessedActivities.[Resultant Owner])<>"")
               )
            )
ORDER BY ProcessedActivities.ActivityIssueNumber, ProcessedActivities.ActivityTime;



*** 找出是issue狀態改變的activity
SELECT f_activity_local.ActivityIssueNumber, f_activity_local.ActivityTime, Mid([activitydescription],InStr([activitydescription],"Issue State changed from ")+25,InStr(InStr([activitydescription],"Issue State changed from "),[activitydescription]," to ")-InStr([activitydescription],"Issue State changed from ")-25) AS [Initial State], Mid([ActivityDescription],InStr(InStr([activitydescription],"Issue State changed from "),[activitydescription]," to ")+4,Len([activitydescription])-InStr(InStr([activitydescription],"Issue State changed from "),[activitydescription]," to ")-5) AS [Resultant State]
FROM f_activity_local
WHERE (((f_activity_local.ActivityDescription) Like "*issue state*"))
ORDER BY f_activity_local.ActivityIssueNumber, f_activity_local.ActivityDescription;




*** select case 找到了
SELECT
Switch([type]="SoftwareEvaluation","Jill",
[responsiblediscipline]="Sapres","Elaine",
[SupportGroupID]=2,"MEG",
[SupportGroupID]=3,"Miriam",
[SupportGroupID]=4,"Adriana",
[SupportGroupID]=1,"Benny")
 AS [Support Group]

, OpenIssues.IssueNumber, OpenIssues.EnteredOn, OpenIssues.Status, OpenIssues.Priority, OpenIssues.Severity, OpenIssues.LastActivityDate, DateDiff("d",[lastactivitydate],Date()) AS [No Activity], OpenIssues.Type, OpenIssues.ModelNumber, OpenIssues.ResponsibleDiscipline
FROM OpenIssues INNER JOIN ModelLookupGroups ON OpenIssues.ModelNumber = ModelLookupGroups.ModelNumber

ORDER BY
Switch([type]="SoftwareEvaluation","Jill",
[responsiblediscipline]="Sapres","Elaine",
[SupportGroupID]=2,"MEG",
[SupportGroupID]=3,"Miriam",
[SupportGroupID]=4,"Adriana",
[SupportGroupID]=1,"Benny")

, OpenIssues.Severity,
 OpenIssues.LastActivityDate;


solve the problem of weekend response
代碼: [選擇]
SELECT Issue_record_local.IssueNumber, ProcessedActivities.ActivityTime, Switch(

          (
                     (Weekday([EnteredOn])=2 And [EnteredAt_1]>="08:00:00")
                 Or (Weekday([EnteredOn])=6 And [EnteredAt_1]<="17:00:00")
                 Or Weekday([EnteredOn])=3
                 Or Weekday([EnteredOn])=4
                 Or Weekday([EnteredOn])=5
          )

          ,
           DateDiff("h",DateAdd("d",[enteredon],[enteredAt_1]),[activitytime])
,          
(
         (Weekday([EnteredOn])=6 And [EnteredAt_1]>"17:00:00")
         Or (Weekday([EnteredOn])=2 And [EnteredAt_1]<"08:00:00")
         Or Weekday([EnteredOn])=7
         Or Weekday([EnteredOn])=1
)

And
(
(Weekday([ProcessedActivities].[ActivityTime])=6 And Hour([EnteredAt_1])>17)
Or (Weekday([ProcessedActivities].[ActivityTime])=2 And Hour([EnteredAt_1])<8)
Or Weekday([ProcessedActivities].[ActivityTime])=7
Or Weekday([ProcessedActivities].[ActivityTime])=1
)

And
(
DateDiff("d",DateAdd("d",[enteredon],[enteredAt_1]),[activitytime])<4
),
           0
,          
(
           (Weekday([EnteredOn])=6 And [EnteredAt_1]>"17:00:00")
           Or (Weekday([EnteredOn])=2 And [EnteredAt_1]<"08:00:00")
           Or Weekday([EnteredOn])=7
           Or Weekday([EnteredOn])=1
)

And
(
     (Weekday([ProcessedActivities].[ActivityTime])=6 And Hour([EnteredAt_1])>17)
Or (Weekday([ProcessedActivities].[ActivityTime])=2 And Hour([EnteredAt_1])<8)
Or Weekday([ProcessedActivities].[ActivityTime])=7
Or Weekday([ProcessedActivities].[ActivityTime])=1
)

And
(
DateDiff("d",DateAdd("d",[enteredon],[enteredAt_1]),[activitytime])>4
)
,
DateDiff("h",DateAdd("d",[enteredon],[enteredAt_1]),[activitytime])

,
(
         (Weekday([EnteredOn])=6 And [EnteredAt_1]>"17:00:00")
         Or (Weekday([EnteredOn])=2 And [EnteredAt_1]<"08:00:00")
         Or Weekday([EnteredOn])=7
         Or Weekday([EnteredOn])=1
)


and
(
(Weekday([ProcessedActivities].[ActivityTime])=2 And Hour([EnteredAt_1])>=8)
Or (Weekday([ProcessedActivities].[ActivityTime])=6 And Hour([EnteredAt_1])<=17)
Or Weekday([ProcessedActivities].[ActivityTime])=3
Or Weekday([ProcessedActivities].[ActivityTime])=4
Or Weekday([ProcessedActivities].[ActivityTime])=5
)
,

DateDiff("h",DateAdd("d",[enteredon],[enteredAt_1]),[activitytime])

) AS responsetime, [enteredon], Issue_record_local.EnteredAt_1, Weekday([EnteredOn]) AS WeekDay1
FROM Issue_record_local INNER JOIN ProcessedActivities ON Issue_record_local.IssueNumber = ProcessedActivities.ActivityIssueNumber
WHERE (((ProcessedActivities.[Initial State])="New"))
ORDER BY Issue_record_local.IssueNumber DESC;




SELECT Format([IssueDate],"mm\/yyyy") AS DateChanged, (CInt(Avg(DateDiff("h",DateAdd("d",[enterDate],[EnterTime]),DateAdd("d",[DateClosed],[TimeClosed])))))/24 AS AvgResponseTime, (Max(DateDiff("h",DateAdd("d",[enterDate],[EnterTime]),DateAdd("d",[DateClosed],[TimeClosed]))))/24 AS MaxResponseTime, (Min(DateDiff("h",DateAdd("d",[enterDate],[EnterTime]),DateAdd("d",[DateClosed],[TimeClosed]))))/24 AS MinResponseTime, Count(*) AS issuenumber
FROM (Issue_record_local LEFT JOIN testIssues ON Issue_record_local.IssueNumber = testIssues.issuenumber) INNER JOIN ProcessedActivities ON Issue_record_local.IssueNumber = ProcessedActivities.ActivityIssueNumber
WHERE (((Issue_record_local.IssueDate)>#1/1/2006#) AND ((ProcessedActivities.[Initial State])="New") AND ((testIssues.issuenumber) Is Null) AND ((Issue_record_local.DateClosed) Is Not Null))
GROUP BY Format([IssueDate],"mm\/yyyy"), Format([IssueDate],"yyyy\/mm")
ORDER BY Format([IssueDate],"yyyy\/mm");

« 上次編輯: 2010-10-12 16:02 由 小徒兒 »

小徒兒

  • 鑽研的研究生
  • *****
  • 文章數: 621
    • 檢視個人資料
Access VBA
« 回覆 #1 於: 2006-11-13 10:01 »
Option Compare Database
Option Explicit

Function makeElapsedTimeReport()
    On Error GoTo Err_makeElapsedTimeReport
   
    Dim db As Database
    Dim tdf As TableDef
    Dim fld As Field
    Dim rst As Recordset
    Dim rstOut As Recordset
    Dim currentIssue As String
    Dim elapsedTime As Date
    Dim lastActivityTime As Date
    Dim lastState As String
   
    Set db = CurrentDb()
   
    For Each tdf In db.TableDefs
        If tdf.Name = "tblElapsedTime" Then
            db.TableDefs.Delete "tblElapsedTime"
        End If
    Next
   
    Set tdf = db.CreateTableDef("tblElapsedTime")
   
    Set fld = tdf.CreateField("IssueNumber", dbText, 50)
    tdf.Fields.Append fld
    Set fld = tdf.CreateField("State", dbText, 50)
    tdf.Fields.Append fld
    Set fld = tdf.CreateField("ElapsedTime", dbLong)
    tdf.Fields.Append fld
    Set fld = tdf.CreateField("DateIn", dbDate)
    tdf.Fields.Append fld
    Set fld = tdf.CreateField("DateOut", dbDate)
    tdf.Fields.Append fld
   
    tdf.Fields.Refresh
    db.TableDefs.Append tdf
    db.TableDefs.Refresh
   
    Set rstOut = db.OpenRecordset("tblElapsedTime", dbOpenDynaset)
   
    Set rst = db.OpenRecordset("qryProcessedActivities", dbOpenDynaset)
   
    If Not rst.BOF Then
        currentIssue = rst("ActivityIssueNumber")
        lastActivityTime = rst("Entered")
        If rst("Initial State") = "New" Or rst("Initial State") = "<Empty>" Then
            elapsedTime = DateDiff("h", rst("Entered"), rst("activitytime"))
            rstOut.AddNew
            rstOut("IssueNumber") = rst("ActivityIssueNumber")
            rstOut("State") = "New"
            rstOut("ElapsedTime") = elapsedTime
            rstOut("DateIn") = lastActivityTime
            rstOut("DateOut") = rst("activitytime")
            rstOut.Update
        Else
            'elapsedTime = DateDiff("h", lastActivityTime, rst("activitytime"))
        End If
       
        lastState = rst("Resultant State")
        lastActivityTime = rst("ActivityTime")
        rst.MoveNext
        If Not rst.EOF Then
            While Not rst.EOF
                If rst("ActivityIssueNumber") <> currentIssue Then
                    If lastState <> "Complete/Customer Accepted" Then
                        'record the last state of the previous issue
                        rstOut.AddNew
                        rstOut("IssueNumber") = currentIssue
                        rstOut("State") = lastState
                        rstOut("ElapsedTime") = DateDiff("h", lastActivityTime, Date)
                        rstOut("DateIn") = lastActivityTime
                        'rstOut("DateOut") = ""
                        rstOut.Update
                    End If
               
                    currentIssue = rst("ActivityIssueNumber")
                    lastState = "RESET"
                    lastActivityTime = rst("Entered")
                End If
                'If rst("ActivityIssueNumber") = "KSWG9078" Then
                '    MsgBox "test"
                'End If
               
                If rst("Initial State") = "New" Or rst("Initial State") = "<Empty>" Then
                    elapsedTime = DateDiff("h", rst("Entered"), rst("activitytime"))
                    If lastState = "RESET" Then
                        rstOut.AddNew
                        rstOut("IssueNumber") = rst("ActivityIssueNumber")
                        rstOut("State") = "New"
                        rstOut("ElapsedTime") = elapsedTime
                        rstOut("DateIn") = lastActivityTime
                        rstOut("DateOut") = rst("activitytime")
                        rstOut.Update
                    End If
                Else
                    elapsedTime = DateDiff("h", lastActivityTime, rst("activitytime"))
                    If rst("Initial State") = lastState Then
                        rstOut.AddNew
                        rstOut("IssueNumber") = rst("ActivityIssueNumber")
                        rstOut("State") = rst("Initial State")
                        rstOut("ElapsedTime") = elapsedTime
                        rstOut("DateIn") = lastActivityTime
                        rstOut("DateOut") = rst("activitytime")
                        rstOut.Update
                    End If
                End If
               
               
                lastState = rst("Resultant State")
                lastActivityTime = rst("activitytime")
               
                rst.MoveNext
            Wend
            If lastState <> "Complete/Customer Accepted" Then
                'record the last state of the previous issue
                rstOut.AddNew
                rstOut("IssueNumber") = currentIssue
                rstOut("State") = lastState
                rstOut("ElapsedTime") = DateDiff("h", lastActivityTime, Date)
                rstOut("DateIn") = lastActivityTime
                'rstOut("DateOut") = ""
                rstOut.Update
            End If
        End If
    End If
    MsgBox "silvia done makeElapsedTimeReport"
Exit_makeElapsedTimeReport:
    Exit Function
Err_makeElapsedTimeReport:
    MsgBox Err.Description
    Resume Exit_makeElapsedTimeReport
End Function

Function makeStateHistoryReport()
    On Error GoTo Err_makeStateHistoryReport
   
    Dim db As Database
    Dim tdf As TableDef
    Dim fld As Field
    Dim rst As Recordset
    Dim rstOut As Recordset
    Dim currentIssue As String
    Dim lastActivityTime As Date
    Dim currentState As String
    Dim nextState As String
    Dim currentOwner As String
    Dim nextOwner As String
    Dim thisDate As Date
    Dim nextDate As Date
    Dim valid As Boolean
   
    Set db = CurrentDb()
   
    For Each tdf In db.TableDefs
        If tdf.Name = "tblStateHistory" Then
            db.TableDefs.Delete "tblStateHistory"
        End If
    Next
   
    Set tdf = db.CreateTableDef("tblStateHistory")
   
    Set fld = tdf.CreateField("IssueNumber", dbText, 50)
    tdf.Fields.Append fld
    Set fld = tdf.CreateField("DateIn", dbDate)
    tdf.Fields.Append fld
    Set fld = tdf.CreateField("DateOut", dbDate)
    tdf.Fields.Append fld
    Set fld = tdf.CreateField("State", dbText, 50)
    tdf.Fields.Append fld
    Set fld = tdf.CreateField("NextState", dbText, 50)
    tdf.Fields.Append fld
    Set fld = tdf.CreateField("ElapsedTime", dbLong)
    tdf.Fields.Append fld
    Set fld = tdf.CreateField("Owner", dbText, 50)
    tdf.Fields.Append fld
   
    tdf.Fields.Refresh
    db.TableDefs.Append tdf
    db.TableDefs.Refresh
   
    Set rstOut = db.OpenRecordset("tblStateHistory", dbOpenDynaset)
   
    Set rst = db.OpenRecordset("qryStateOwnerHistory", dbOpenDynaset)
   
    If Not rst.BOF Then
        currentIssue = rst("ActivityIssueNumber")
        valid = False
        If rst("Initial State") = "New" Or rst("Initial State") = "<Empty>" Then
            valid = True
            currentState = "New"
            nextState = rst("Resultant State")
            If nextState = "" Then
                nextState = "_"
            End If
            thisDate = rst("Entered")
            nextDate = rst("activitytime")
           
            If Not IsNull(rst("Initial Owner")) Then
                currentOwner = rst("Initial Owner")
                nextOwner = rst("Resultant Owner")
            Else
                currentOwner = rst("Originator")
            End If
        End If
       
        If currentIssue <> "" And currentState <> "" And currentOwner <> "" Then
            rstOut.AddNew
            rstOut("IssueNumber") = rst("ActivityIssueNumber")
            rstOut("State") = currentState
            rstOut("NextState") = nextState
            rstOut("ElapsedTime") = DateDiff("h", thisDate, nextDate)
            rstOut("Owner") = currentOwner
            rstOut("DateIn") = thisDate
            rstOut("DateOut") = nextDate
            rstOut.Update
        End If
       
        currentState = rst("Resultant State")
        If Not IsNull(rst("Resultant Owner")) Then
            currentOwner = rst("Resultant Owner")
        End If
        thisDate = nextDate
       
       
        rst.MoveNext
        If Not rst.EOF Then
            While Not rst.EOF
                If rst("ActivityIssueNumber") <> currentIssue Then
                    If valid = True Then
                        'record the last state of the previous issue
                        rstOut.AddNew
                        rstOut("IssueNumber") = currentIssue
                        rstOut("State") = currentState
                        rstOut("NextState") = "_"
                        'rstOut("ElapsedTime") = 0
                        rstOut("ElapsedTime") = DateDiff("h", thisDate, Now())
                        rstOut("Owner") = currentOwner
                        rstOut("DateIn") = thisDate
                        'rstOut("DateOut") = ""
                        rstOut.Update
                    End If
               
                    valid = False
                    currentIssue = rst("ActivityIssueNumber")
                    currentOwner = rst("Originator")
                    nextState = "RESET"
                    thisDate = rst("Entered")
                End If
               
                nextDate = rst("ActivityTime")
               
                If rst("Initial State") <> "" Then
                    currentState = rst("Initial State")
                    nextState = rst("Resultant State")
                    If nextState = "" Then
                        nextState = "_"
                    End If
                End If
               
                If rst("Initial Owner") <> "" Then
                    currentOwner = rst("Initial Owner")
                    nextOwner = rst("Resultant Owner")
                End If
               
                If valid = False And (rst("Initial State") = "New" Or rst("Initial State") = "<Empty>") Then
                    valid = True
                End If
                If valid = True Then
                    If nextState = "RESET" Then
                            rstOut.AddNew
                            rstOut("IssueNumber") = currentIssue
                            rstOut("State") = "New"
                            rstOut("NextState") = nextState
                            rstOut("ElapsedTime") = DateDiff("h", thisDate, nextDate)
                            rstOut("Owner") = currentOwner
                            rstOut("DateIn") = thisDate
                            rstOut("DateOut") = nextDate
                            rstOut.Update
                    Else
                        'If rst("Initial State") = nextState Then
                            rstOut.AddNew
                            rstOut("IssueNumber") = currentIssue
                            rstOut("State") = currentState
                            rstOut("NextState") = nextState
                            rstOut("ElapsedTime") = DateDiff("h", thisDate, nextDate)
                            rstOut("Owner") = currentOwner
                            rstOut("DateIn") = thisDate
                            rstOut("DateOut") = nextDate
                            rstOut.Update
                       ' End If
                    End If
                End If
               
                If rst("Resultant Owner") <> "" Then
                    currentOwner = rst("Resultant Owner")
                End If
               
                If rst("Resultant State") <> "" Then
                    currentState = rst("Resultant State")
                End If
               
                thisDate = rst("ActivityTime")
               
                rst.MoveNext
            Wend
            If nextState <> "Complete/Customer Accepted" Then
                'record the last state of the previous issue
                rstOut.AddNew
                rstOut("IssueNumber") = currentIssue
                rstOut("State") = nextState
                rstOut("NextState") = nextState
                'rstOut("ElapsedTime") = 0
                rstOut("ElapsedTime") = DateDiff("h", thisDate, Now())
                rstOut("Owner") = currentOwner
                rstOut("DateIn") = nextDate
                'rstOut("DateOut") = ""
                rstOut.Update
            End If
        End If
    End If

MsgBox "silvia done makeStateHistoryReport"
Exit_makeStateHistoryReport:
    Exit Function
Err_makeStateHistoryReport:
    MsgBox Err.Description
    Resume Next
    Resume Exit_makeStateHistoryReport
End Function

Sub generateDateTable()
    On Error GoTo Err_generateDateTable
   
    Dim db As Database
    Dim tdf As TableDef
    Dim fld As Field
    Dim rst As Recordset
    Dim rstOut As Recordset
    Dim currentIssue As String
    Dim elapsedTime As Date
    Dim lastActivityTime As Date
    Dim lastState As String
    Dim tableName As String
    Dim startDate As Date
    Dim endDate As Date
    Dim thisDate As Date
   
    Dim fldLoop As Field
    Dim prpLoop As Property
   
    tableName = "tblDateReference"
    startDate = #1/1/1997#
    endDate = #12/31/2010#
   
    Set db = CurrentDb()
   
    For Each tdf In db.TableDefs
        If tdf.Name = tableName Then
            For Each fldLoop In tdf.Fields
                    Debug.Print "    " & fldLoop.Name
           
                    For Each prpLoop In fldLoop.Properties
                        ' Properties that are invalid in the context of
           
            ' TableDefs will trigger an error if an attempt
                        ' is made to read their values.
                        On Error Resume Next
                        Debug.Print "        " & prpLoop.Name & " - " & _
                            IIf(prpLoop = "", "[empty]", prpLoop)
                        'On Error GoTo 0
                    Next prpLoop
           
                Next fldLoop
           
           
       
       
            db.TableDefs.Delete tableName
        End If
    Next
   
    Set tdf = db.CreateTableDef(tableName)
   
    Set fld = tdf.CreateField("ID", dbLong)
    tdf.Fields.Append fld
    Set fld = tdf.CreateField("RefDate", dbDate)
    tdf.Fields.Append fld
   
    tdf.Fields.Refresh
    For Each fld In tdf.Fields
        If fld.Name = "ID" Then
            fld.Attributes = 17
        End If
    Next
    db.TableDefs.Append tdf
    db.TableDefs.Refresh
   
    tdf.Fields.Refresh
    db.TableDefs.Append tdf
    db.TableDefs.Refresh
   
    Set rstOut = db.OpenRecordset(tableName, dbOpenDynaset)
   
    thisDate = startDate
    While thisDate < endDate
        rstOut.AddNew
        rstOut("RefDate") = thisDate
        rstOut.Update
        thisDate = thisDate + 1
    Wend
   
   
   
   
Exit_generateDateTable:
    Exit Sub
Err_generateDateTable:
    MsgBox Err.Description
    Resume Next
    Resume Exit_generateDateTable
End Sub


Function makeNextActivityReport()
    On Error GoTo Err_makeNextActivityReport
   
    Dim db As Database
    Dim tdf As TableDef
    Dim fld As Field
    Dim rst As Recordset
    Dim rstOut As Recordset
    Dim currentIssue As String
    Dim elapsedTime As Date
    Dim lastActivityTime As Date
    Dim lastState As String
    Dim flagIR As Boolean
    Dim flagFind As Boolean
   
    Set db = CurrentDb()
   
    For Each tdf In db.TableDefs
        If tdf.Name = "tblNextActivityTime" Then
            db.TableDefs.Delete "tblNextActivityTime"
        End If
    Next
   
    Set tdf = db.CreateTableDef("tblNextActivityTime")
   
    Set fld = tdf.CreateField("IssueNumber", dbText, 50)
    tdf.Fields.Append fld
    'Set fld = tdf.CreateField("State", dbText, 50)
    'tdf.Fields.Append fld
    Set fld = tdf.CreateField("ElapsedTime", dbLong)
    tdf.Fields.Append fld
    Set fld = tdf.CreateField("DateIn", dbDate)
    tdf.Fields.Append fld
    Set fld = tdf.CreateField("DateOut", dbDate)
    tdf.Fields.Append fld
   
    tdf.Fields.Refresh
    db.TableDefs.Append tdf
    db.TableDefs.Refresh
   
    Set rstOut = db.OpenRecordset("tblNextActivityTime", dbOpenDynaset)
   
    Set rst = db.OpenRecordset("select * from ProcessedActivities order by ActivityIssueNumber, ActivityTime", dbOpenDynaset)
   
   
    flagIR = False
    flagFind = False
   
   
    If Not rst.BOF Then
        currentIssue = rst("ActivityIssueNumber")
        flagFind = True
       
        lastState = rst("Resultant State")
        lastActivityTime = rst("ActivityTime")
       
        If lastState = "Initial Response" Then
            flagIR = True
        End If
       
        rst.MoveNext
       
        If Not rst.EOF Then
            While Not rst.EOF
                If rst("ActivityIssueNumber") <> currentIssue Then
                   
                    flagIR = False
                    flagFind = False
               
                    currentIssue = rst("ActivityIssueNumber")
                    lastState = "RESET"
                End If
               
                If flagIR = True Then
                    flagIR = False
                    rstOut.AddNew
                    rstOut("IssueNumber") = rst("ActivityIssueNumber")
                    'rstOut("State") = rst("Initial State")
                    rstOut("ElapsedTime") = DateDiff("H", lastActivityTime, rst("activitytime"))
                    rstOut("DateIn") = lastActivityTime
                    rstOut("DateOut") = rst("activitytime")
                    rstOut.Update
                End If
               
               
                lastState = rst("Resultant State")
                lastActivityTime = rst("activitytime")
                If lastState = "Initial Response" Then
                    flagIR = True
                End If
       
               
                rst.MoveNext
            Wend
            If rst("ActivityIssueNumber") <> currentIssue Then
               
                flagIR = False
                flagFind = False
           
                currentIssue = rst("ActivityIssueNumber")
                lastState = "RESET"
            End If
            If flagIR = True Then
                'record the last state of the previous issue
                rstOut.AddNew
                rstOut("IssueNumber") = currentIssue
                rstOut("ElapsedTime") = DateDiff("h", lastActivityTime, Date)
                rstOut("DateIn") = lastActivityTime
                'rstOut("DateOut") = ""
                rstOut.Update
            End If
        End If
    End If
    MsgBox "silvia done makeNextActivityReport"
   
Exit_makeNextActivityReport:
    Exit Function
Err_makeNextActivityReport:
    MsgBox Err.Description
    Resume Exit_makeNextActivityReport
End Function

Sub AccumNewIssueNr()
Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim rst As Recordset
Dim rstOut As Recordset


Set db = CurrentDb()

For Each tdf In db.TableDefs
    If tdf.Name = "AccumNewIssueNr" Then
        db.TableDefs.Delete "AccumNewIssueNr"
    End If
Next

Set tdf = db.CreateTableDef("AccumNewIssueNr")

Set fld = tdf.CreateField("IssueDate", dbText, 50)
tdf.Fields.Append fld
Set fld = tdf.CreateField("AccumNewIssueNr", dbInteger)
tdf.Fields.Append fld
Set fld = tdf.CreateField("AccumSolvedIssueNr", dbInteger)
tdf.Fields.Append fld

tdf.Fields.Refresh
db.TableDefs.Append tdf
db.TableDefs.Refresh

Set rstOut = db.OpenRecordset("AccumNewIssueNr", dbOpenDynaset)




Dim strSQL As String
Dim i As Integer
Dim intAccumNewIssue As Integer


strSQL = "SELECT Count(*) AS Expr1, Format([IssueDate],'mm\/yyyy') AS Expr2 FROM Issue_record_local INNER JOIN ProcessedActivities ON Issue_record_local.IssueNumber = ProcessedActivities.ActivityIssueNumber WHERE (((Format([IssueDate], 'yyyy')) = '2006')) GROUP BY Format([IssueDate],'mm\/yyyy'), Format([IssueDate],'yyyy\/mm') ,(ProcessedActivities.[Initial State])HAVING (((ProcessedActivities.[Initial State]) = 'new'))ORDER BY Format([IssueDate],'yyyy\/mm');"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

intAccumNewIssue = 0

For i = 1 To rst.RecordCount
intAccumNewIssue = intAccumNewIssue + rst(0).Value
rstOut.AddNew
rstOut("IssueDate") = rst(1).Value
rstOut("AccumNewIssueNr") = intAccumNewIssue
rstOut.Update
rst.MoveNext
Next i

strSQL = "SELECT Count(*) AS Expr1, Format([DateClosed],'mm\/yyyy') AS Expr2 FROM Issue_record_local INNER JOIN ProcessedActivities ON Issue_record_local.IssueNumber = ProcessedActivities.ActivityIssueNumber WHERE (((Format([DateClosed], 'yyyy')) = '2006') And ((Issue_record_local.DateClosed) Is Not Null)) GROUP BY Format([DateClosed],'mm\/yyyy'), Format([DateClosed],'yyyy\/mm') ORDER BY Format([DateClosed],'yyyy\/mm');"


Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
intAccumNewIssue = 0

For i = 1 To rst.RecordCount
intAccumNewIssue = intAccumNewIssue + rst(0).Value

Dim strDate
strDate = "0" & i & "/2006"
If Len(strDate) > 7 Then strDate = Right(strDate, 7)
strSQL = "update AccumNewIssueNr set AccumSolvedIssueNr =" & intAccumNewIssue & " where IssueDate = '" & strDate & "';"

 DoCmd.RunSQL strSQL

Next i



MsgBox "finish"





End Sub

小徒兒

  • 鑽研的研究生
  • *****
  • 文章數: 621
    • 檢視個人資料
Re: access sql syntax
« 回覆 #2 於: 2011-10-10 16:01 »
代碼: [選擇]
Private Sub Command1_Click()

Dim rs2 As New Recordset
Dim instance2 As New iacRDSObjAccess.rsop
'On Error GoTo CheckError

'Set rs2 = instance2.ReturnRs("C:\skillset_DT2003.mdb", "select * from employees")
Set rs2 = instance2.ReturnRs("skillset_DT97.mdb", "select * from employees")


Do While Not rs2.EOF
i = 0
Debug.Print rs2.Fields("lastname").Value

      'Selection1(i) = "出差申請單單號:" + "" + rs2!gvm005002 + "  出差日期:" + Left(rs2!text5, 10)
      rs2.MoveNext
     
i = i + 1
Loop

End Sub