Subqueries In Access/MSSQL
Jun 7, 2006
Hi,
To make a long story short, I'm trying to correlate the most inner query (two levels deep) with the most outer one, and it just doesn't work.
Is it because the correlation must be back-to-back, without any levels that stand before the two?
If you have no idea what I'm talking about, below is the query that does work, but doesn't produce the right results ("QUOTES" gives the same number for all records):
---------------
select printersGlobal.company, (select count(*) from (select subBidding.bid_company from bidding as subBidding where subBidding.bid_company = 'Printing Company 1' group by subBidding.bid_company, subBidding.project) group by subBidding.bid_company) as QUOTES,
count(date_picked) as wins
from printers as printersGlobal
left outer join bidding as biddingGlobal on printersGlobal.company = biddingGlobal.bid_company
group by printersGlobal.company
order by printersGlobal.company asc
----------------------
When I replace 'Printing Company 1' with printersGlobal.company (to correlate and produce dynamic and correct results), I get errors.
I would appreciate if anybody can point me in the right direction.
Thanks.
View 14 Replies
ADVERTISEMENT
Aug 14, 2003
We developed an application . The application is an ADP with MSSQL.
What i need to do if i want to deploy to
users ? Because all the forms - record source belong to my server.
View 1 Replies
View Related
Apr 30, 2007
Hi, what is the best way to go to migrate from access to MSSQL on a remote (hosted) server? do I have to create manually de tables and then move the data? or is there a way to create the creation scripts from the ACCESS database?
Thank you
Diego
View 2 Replies
View Related
Mar 15, 2005
We use a program that creates a access database for each of our payperiods. Its a pain to get a full history of a person from each one. I want to setup a way I can combine all of the access files into one table in mssql. I have a DTS I created into a .bas file. My plan is to make the vb.net code get a directory of the files, then run a function (the .bas file) on each directory appending the access table to the mssql table. The struckture of the access files are that each file is in its own directory, like below:
\servernameTAcompany 7-25-04Trans.mdb
\servernameTAcompany 8-08-04Trans.mdb
\servernameTAcompany 8-22-04Trans.mdb
The table is called "Transactions" and they are the same in each mdb file in terms of fields. Every two weeks there is a new directory that is added to the company directory. So I can either dump all and append all, or only append new data and only use the dump/append when there is a problem.
I am just not sure how to do that. Any ideas?
View 3 Replies
View Related
May 24, 2005
Is it possible to install MsSQL / Access on Linux? The reason I am asking is I have a linux virtual dedicated server, and develop both php/mysql and asp/mssql/access database, is there another program or something I can run on my server to allow development of programs needs mssql/access?
Thanks in advance for replies,
Jeff
EDIT: I am new to Linux, however have found this: http://www.knoda.org/ Is this what I might need?
View 2 Replies
View Related
Oct 24, 2005
I run the following statement from an update query in access but I can't find the way to run this same query in MSSQL. Please give me some ideas how to modify and run this in MSSQL.
Thank you
"UPDATE DISTINCTROW ZipToTerr, leadsUS SET leadsUS.Terr = [ZipToTerr]![TerrNum] WHERE ((([ZipToTerr].[BU]='W') AND (([ZipToTerr].[ZipFrom])<=[zip]) And (([ZipToTerr].[ZipTo])>=[zip])) And (([leadsUS].[terr]) = 1 ));"
View 8 Replies
View Related
Oct 31, 2007
Hi im new to SSIS even i did some basice things in SSIS. Now i run in to the problem, I hawe a access file with arount one milion records and i won to transfare this records in Ms SqlServer . But befora i transfare that i nead to check if that record exsist by ID if exsist i must do update else i must do insert.
Can some one Help me how can i do it..
THX
Sorry for my bad englisht
View 3 Replies
View Related
Jun 19, 2004
Hello,
I am working on a couple fo the ASP.NET walkthoughs and I would like to practice with a Database that I created in Access. I realize that I could use the Access DB, but I would like to learn to work with a more industrial strength DB. Is there a method to export to MSSQL server 2000 from Access ?
Regards,
James
View 4 Replies
View Related
Jul 19, 2007
Hi,
I need to access the mssql database which is inbuilt in Mcafee.
through jdbc i'm trying to conenct mssql but its getting the tables from the default database which in Mcafee not from the mssql.
if any one have any idea please let me know to solve this.
Thanks for your help in advance.
Regs
rbkl.
View 1 Replies
View Related
Dec 10, 2007
Have recently migrated an Access database to MSSQL.
The following code worked in Access but fails in MSSQL
<input <%If (CStr(Recordset1.Fields.Item("Disclose").Value) = CStr("TRUE")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="Disclose" value="1">
<input <%If (CStr((Recordset1.Fields.Item("Disclose").Value)) = CStr("FALSE")) Then Response.Write("CHECKED") : Response.Write("")%> name="Disclose" type="radio" value="0" >
In Query Analyzer, when I run SELECT Disclose from TABLE, it returns the number 1.
On an ASP page, when I run Response.Write(Recordset1.Fields.Item("Disclose").Value) it returns TRUE.
Have tried changing the = CStr("TRUE") portion to = 1 but it fails to work either way.
Any ideas?
View 1 Replies
View Related
Jan 21, 2004
Oke here is the problem. I hope sombody can help me with it.
After a lot of discussion, I finaly got the people that control the MS SQL server with my appertment as far to allow me to upgrade my access database to the MS SQL server.
After this I've created an access project. Devellopping in access I concluded I made a mistake in the rowsource property of one of the tablefields. The people that control the MS SQL server don'n allow me to make changes to the database structure from within access. So I've to write a SQL statement to change this.
example:
fields table1: id_document, id_document_type, document_name
fields tabel2: id_document_type, document_type_description.
the field id_document_type from table 1 is defined as a combo/list box tha contains the values from the field document_type_description from table 2.
I hope somebody can tell me if it is possible to write an SQL statement for this problem and how it shoud look like ?
thank you
View 1 Replies
View Related
Oct 15, 2007
Hi All,
I am converting an old MS Access database to MSSQL. While I do get some data into MSSQL, some weird things are happening that are beyond my capabilities.
The database I am trying to convert exists of several tables. The first few are converted perfectly, but one of the last is giving difficulties. What I do is:
SET @SQLQuery = 'INSERT INTO TableName ( ' +
'[field1], ' +
'[field2], ' +
'[field3], ' +
'[field4], ' +
'[Date], ' +
'[field6]) ' +
'SELECT ' +
'[field1], ' +
'[field2], ' +
'[field3], ' +
'[field4], ' +
'[Date], ' +
'[field6]) ' +
' FROM '+ RTRIM(@LinkedServerName) +'...TableName'
EXEC (@SQLQuery)
I do the same for each table, all with their own TableNames and field names. 80% of the tables have a date field, which is of type DBTYPE_DBTIMESTAMP in Access and datetime in MSSQL.
As mentioned: the first tables are converted perfectly, however one fails with error message:
Msg 8114, Level 16, State 8, Server XXXXXX, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.
If I leave the date field out of the query for that table, it all works (obviously, with an empty date column). The weird thing is that most of the other tables have a date column as well, they are the same data types as in this column and the conversion query is very similar as well.
I have Googled on the full error message and on DBTYPE_DBTIMESTAMP alone, but all results I get seem different to what I have. I have tried using convert to put the date in the correct format (format in the date tables is DD-MM-YYYY), but this doesn't help. Would have been funny if it had helped, as all other tables use the same date format, and according to the specs, DBTYPE_DBTIMESTAMP should be automatically converted to datetime anyway (which in all other tables works)...
Basically, what I am saying is: I have 8 extremely similar tables, which I all try to convert using the exact same method, 7 tables succeed and one fails...
Thanks in advance for your help!
Freddy
View 3 Replies
View Related
Apr 5, 2006
Hi,
I have few SQL user who has permissions on different databases. When they are accessing MSSQL server from Enterprise Manager they can see all Databases but can not access them and they can change file size allocated to them. I want to restrict this and they can only see database belongs to respective user and restrict user to change allocated size. Please help me with this as I am not able to find the solution.
Thanks & Regards,
Gaurav
View 1 Replies
View Related
Mar 6, 2008
To whom it may concern,
I have developed a web application using ASP 3.0 with a Access DB 2002 backend. I have decided to recently to upgrade the database to SQL 2005 but I am having issues that I cannot work out because I am new to MsSql Language. It appears that the FORMAT function in Access does not work in MsSql ( does not recognise the function ) along with some of the PIVOT and TRANSFORM calls (coming up with "Syntax error near "..."") . Please see the below code. The areas that are showing up as errors when the application is run are highlighted in red.
Thanking you in advance
Darren
<!-- #include file="./../../common/includes/i_header_login.asp" -->
<link href="../../common/css/proceduremanual.css" rel="stylesheet" type="text/css">
<%Sub RecordsetToCSV(ByRef RS, ByVal CSVFilePath, ByVal IncludeFieldNames)
Set objCSVFile = CreateObject("ADODB.Stream")
Call objCSVFile.Open
If IncludeFieldNames Then
'string concatenation issues aren't a problem for a small string of field names
For Each Field In RS.Fields
If FieldNames = "" Then
FieldNames = Field.Name
Else
FieldNames = FieldNames & "," & Field.Name
End If
Next
FieldNames = FieldNames & vbCRLF
Call objCSVFile.WriteText(FieldNames, 1)
End If
Call objCSVFile.WriteText(FieldNames & RS.GetString(adClipString, , ",", vbCRLF, ""))
Call objCSVFile.SaveToFile(CSVFilePath, 2)
Set objCSVFile = Nothing
End Sub
%>
<%
function evaluate(pFormula, pUserID, pMonth, pYear)
if isnull(pFormula) then
evaluate = 0
exit function
end if
x = 0
key = ""
zero = "0"
nine = "9"
eFormula = ""
lf = len(pFormula)
for i = 1 to lf
c = mid(pFormula,i,1)
if x = 1 and c >= zero and c <= nine then key = key & c
if x = 1 and (c < zero or c > nine or i = lf) then
set rsd = cn.execute("select sum(total) from Data where UserID = " & pUserID & " and month(weekEnding) = " & pMonth & " and year(weekEnding) = " & pYear & " and GroupTaskID=" & key)
eFormula = eFormula & rsd.fields(0)
rsd.close
key = ""
if c < zero or c > nine then x = 0
end if
if c = "v" then
key = ""
x = 1
end if
if x = 0 then eFormula = eFormula & c
next
on error resume next
evaluate = round(eval(eFormula),0)
'if err then evaluate = err.description & ": " & eformula
if err then evaluate = 0
end function
%>
<%
period = split(request.form("period"),",")
if ubound(period) = 1 then
mmmm = period(0)+0
yyyy = period(1)+0
else
mmmm = 0
yyyy = 0
end if
%>
<div align="left" class="MainBodyText" style="width: 100%; height: 100%">
<div align="left" valign="top" width="200" style="margin:5; padding:3; font-family:arial; font-size:10pt; font-weight:bold; font-color000000;" >
<% if session("groupid") = 1 then%>
<a href="../main/administration.asp"> <<< Administration Home</a><br><br>
<a href="manager_report.asp"> <<< Managers Report Home</a>
<%end if %>
<%if session("groupid") = 2 then%>
<a href="../main/manager.asp"> <<< Manager Home</a><br><br>
<a href="manager_report.asp"> <<< Managers Report Home</a>
<%end if %>
<%if session("groupid") = 3 then%>
<a href="../main/user.asp"> <<< User Home</a>
<%end if %>
<%if session("groupid") = 4 then%>
<a href="../main/general.asp"> <<< Home</a><br><br>
<a href="manager_report.asp"> <<< Managers Report Home</a>
<%end if %>
</div>
<p align="center" class="MainBodyText">
<img src="../../images/si.gif" align="center"><br><b>Month to Date Dashboard</b></p>
<p align="center" class="MainBodyText">
To use the Print Page function please select the period required then select
view report. You can then select Print Page which will print out the rendered
page.</p>
<form name="f1" method="POST" action="dialmtd.asp">
<div id="printReady">
<table align="center" border="0" width="663" style="border-collapse: collapse" bordercolor="#111111" cellpadding="2">
<tr>
<td align="left" width="127"><font face="Arial" size="2">
<select name="benchmarkgroup" style="color: #000000; border: 0px solid #000000; background-color: #FFE737">
<option value="0" <%if request.form("benchmarkgroup") = "" then response.write "selected"%>>Select Report
<% set rs = cn.execute("select TaskGroupID,TaskGroup from BenchmarkGroups where Active = 1 order by TaskGroupID")
do until rs.eof
if request.form("taskgroupid")+0 = rs.fields(0) then selected = "selected" else selected = "" %>
<option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>
<td align="left" width="143"><font face="Arial" size="2">
<select name="period">
<option value="" <%if request.form("period") = "" then response.write "selected"%>>Select Period
<% set rs = cn.execute("select year(WeekEnding), month(WeekEnding), format(Weekending,""mmmm yyyy"") from data group by Year(WeekEnding), Month(WeekEnding), format(WeekEnding,""mmmm yyyy"") order by 1,2")
do until rs.eof
x = rs.fields(1) & "," & rs.fields(0)
if request.form("period") = x then selected = "selected" else selected = "" %>
<option value=<%=x & " " & selected%>><%=rs.fields(2)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>
<td align="left" width="117"><font face="Arial" size="2">
<select name="stateid">
<option value="0" <%if request.form("stateid") = "" then response.write "selected"%>>All States
<% set rs = cn.execute("select stateid,state from tblState where benchmarkactive order by state")
do until rs.eof
if request.form("stateid")+0 = rs.fields(0) then selected = "selected" else selected = "" %>
<option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>
<td align="left" width="127"><font face="Arial" size="2">
<select name="countryid">
<option value="0" <%if request.form("countryid") = "" then response.write "selected"%>>All Countries
<% set rs = cn.execute("select countryid,country from tblCountry where benchmarkactive order by country")
do until rs.eof
if request.form("countryid")+0 = rs.fields(0) then selected = "selected" else selected = "" %>
<option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>
<td align="left" width="129"><input type="image" src="../../Images/view-report.gif" onclick="f1.submit()" name="f1" alt="Click Here to Generate Report"></td>
<td width="127"><input type="image" src="../../Images/print-report.gif" rname="printMe" onClick="printSpecial()" alt="Click Here to Print the Page"></td>
</tr>
</table>
<% if mmmm > 0 then %>
<table border="0" width="450" align="left" style="margin-left:10px; border-collapse: collapse" bordercolor="#111111" cellpadding="2" cellspacing="4">
<thead>
<tr>
<% sql = " where 1 = 1 AND BenchmarkGroup =" & request("benchmarkgroup")
if request.form("stateid")+0 then sql = sql & " and stateid = " & request.form("stateid")
if request.form("countryid")+0 then sql = sql & " and countryid = " & request.form("countryid")
set rs = cn.Execute("TRANSFORM First(UserID) SELECT description, minimum, mintype, formula FROM BenchmarksActiveUsers" & sql & " GROUP BY displayOrder, description, minimum, mintype, formula ORDER BY displayOrder PIVOT Name")
lastfield = rs.fields.count-1
s = 4 %>
<td align="left" bgcolor="#C0C0C0"><font face="Arial" color="#000000" size="2"><b>Benchmark</b></font> </td>
<td align="center" bgcolor="#C0C0C0"><font face="Arial" color="#000000" size="2"><b>Company Average</b></font> </td>
<% for i = s to lastfield %>
<% next %>
</tr>
</thead>
<tbody>
<br>
<% dim v()
do until rs.eof
min = int(rs.fields("minimum"))
mintype = rs.fields("mintype")
sumv = 0
avg = 0
n = 0
for j = s to lastfield
redim preserve v(j)
v(j) = evaluate(rs.fields("formula"),rs.fields(j),mmmm,yyyy)
sumv = sumv + v(j)
n = n + 1
next
if n > 0 then avg = round(sumv / n) %>
<tr><td></td><td align="center" valign="bottom"><font face="Arial" size="2">Target: <%=min%> <%=mintype%></font></td><td></td></tr>
<tr>
<td bgcolor="#FFFFFF">
<p align="right"><font face="Arial" size="2"><%=rs.fields("description")%></font></td>
<td bgcolor="#FFFFFF">
<p align="center"><font face="Arial" size="1"><% If (avg/min)*100 <=12 Then %><IMG SRC="../images/dial-1.gif">
<% ELSEIF (avg/min)*100 <=24 Then%><IMG SRC="../images/dial-2.gif">
<% ELSEIF (avg/min)*100 <=36 Then%><IMG SRC="../images/dial-3.gif">
<% ELSEIF (avg/min)*100 <=48 Then%><IMG SRC="../images/dial-4.gif">
<% ELSEIF (avg/min)*100 <=60 Then%><IMG SRC="../images/dial-5.gif">
<% ELSEIF (avg/min)*100 <=72 Then%><IMG SRC="../images/dial-6.gif">
<% ELSEIF (avg/min)*100 <=86 Then%><IMG SRC="../images/dial-7.gif">
<% ELSEIF (avg/min)*100 <=99 Then%><IMG SRC="../images/dial-8.gif">
<% ELSEIF (avg/min)*100 =100 Then%><IMG SRC="../images/dial-9.gif">
<% ELSEIF (avg/min)*100 <=112 Then%><IMG SRC="../images/dial-10.gif">
<% ELSEIF (avg/min)*100 <=124 Then%><IMG SRC="../images/dial-11.gif">
<% ELSEIF (avg/min)*100 <=136 Then%><IMG SRC="../images/dial-12.gif">
<% ELSEIF (avg/min)*100 <=148 Then%><IMG SRC="../images/dial-13.gif">
<% ELSEIF (avg/min)*100 <=160 Then%><IMG SRC="../images/dial-14.gif">
<% ELSEIF (avg/min)*100 <=172 Then%><IMG SRC="../images/dial-15.gif">
<% ELSEIF (avg/min)*100 <=186 Then%><IMG SRC="../images/dial-16.gif">
<% ELSEIF (avg/min)*100 =>187 Then%><IMG SRC="../images/dial-17.gif">
<%END IF%></font></td><td><img src="../images/under-target.gif"><br><br><img src="../images/over-target.gif"></td>
</tr><tr><td></td><td valign="top" align="center"><font face="Arial" size="2">Actual: <%=avg%> <%=mintype%></font><hr></td><td></td></tr><% for j = s to lastfield
color = "#000000"
if int(v(j)) < min then color="#FF0000" %>
<% next %>
</tr>
<% rs.movenext
loop
rs.close
cn.close
set cn = nothing %>
</tbody>
</table>
<% end if %>
</div>
</form>
</div>
<!-- #include file="./../../common/includes/i_footer.asp" -->
View 2 Replies
View Related
Jan 30, 2007
I dont know how to arrange situation when application enduser needs to access data in two databases of mssql server concurently in those circumstances that access rights to the data should be restricted by password protected role (whose password is not known to the end user).
Detailed description of problem:
So far there was an application, that manipulated its data, saved in mssql server's database. End user authenticates to application by his (mssql server's) login name and password. The application authenticates the user by connecting to the database with the given name/password credentials, and then the application sets application role with hardcoded name/password. Thus application role sets the access rights for consequent end user's requests, delivered via application to the database server.
The goal is that end user cannot manipulate application database data when connects to the database by other means (e. g. via SQL server Manager), because he does not know the application role's password.
Now suppose that there are two applications (A1, A2), both using the same model for access restrictions. Each of them has its own database (A1DB, A2DB) and its own application role (A1R residing in A1DB, A2R residing in A2DB). End user (login) X can manipulate A1DB data when connects via A1, and A2DB data when connects via A2, and NO data when connects by other means.
Finally suppose that some subset of A2 data (let's say one table) is useful to see also via A1 application. There is no problem to add to A1DB view, that shows data from A2DB table together with A1DB tables. But when the user is connected via A1, he cannot see the data, because query on A1 view fails (user has not access rights on A2 data).
The access rights for A1 enduser cannot be set by no means i know because:
1) I cannot set the rights via public (guest) access because in that case they will be accessible to any users connected by any third party products, which is supposed to be security hole.
2) I cannot set the rights via dbuser or dbrole privileges, because they will not work when connected via A1 application (setting the app role suppresses the db privileges)
3) I cannot set the rights via application role because two application roles cannot be set concurrently.
4) I cannot abandon using application roles mechanism and use database roles mechanism, because db roles cannot be protected by independent password (not known to the enduser).
Please can anybody review my problem and either find the mistake in my approach, or propose other solution? So far I suppose the problem is my ignorance, because I am not great mssql expert.
View 3 Replies
View Related
Feb 26, 2008
To whom it may concern,
I have developed a web application using ASP 3.0 with a Access DB 2002 backend. I have decided to recently to upgrade the database to SQL 2005 but I am having issues that I cannot work out because I am new to MsSql Language. It appears that the FORMAT function in Access does not work in MsSql ( does not recognise the function ) along with some of the PIVOT and TRANSFORM calls (coming up with "Syntax error near "..."") . Please see the below code. The areas that are showing up as errors when the application is run are highlighted in red.
Thanking you in advance
Darren
<!-- #include file="./../../common/includes/i_header_login.asp" -->
<link href="../../common/css/proceduremanual.css" rel="stylesheet" type="text/css">
<%Sub RecordsetToCSV(ByRef RS, ByVal CSVFilePath, ByVal IncludeFieldNames)
Set objCSVFile = CreateObject("ADODB.Stream")
Call objCSVFile.Open
If IncludeFieldNames Then
'string concatenation issues aren't a problem for a small string of field names
For Each Field In RS.Fields
If FieldNames = "" Then
FieldNames = Field.Name
Else
FieldNames = FieldNames & "," & Field.Name
End If
Next
FieldNames = FieldNames & vbCRLF
Call objCSVFile.WriteText(FieldNames, 1)
End If
Call objCSVFile.WriteText(FieldNames & RS.GetString(adClipString, , ",", vbCRLF, ""))
Call objCSVFile.SaveToFile(CSVFilePath, 2)
Set objCSVFile = Nothing
End Sub
%>
<%
function evaluate(pFormula, pUserID, pMonth, pYear)
if isnull(pFormula) then
evaluate = 0
exit function
end if
x = 0
key = ""
zero = "0"
nine = "9"
eFormula = ""
lf = len(pFormula)
for i = 1 to lf
c = mid(pFormula,i,1)
if x = 1 and c >= zero and c <= nine then key = key & c
if x = 1 and (c < zero or c > nine or i = lf) then
set rsd = cn.execute("select sum(total) from Data where UserID = " & pUserID & " and month(weekEnding) = " & pMonth & " and year(weekEnding) = " & pYear & " and GroupTaskID=" & key)
eFormula = eFormula & rsd.fields(0)
rsd.close
key = ""
if c < zero or c > nine then x = 0
end if
if c = "v" then
key = ""
x = 1
end if
if x = 0 then eFormula = eFormula & c
next
on error resume next
evaluate = round(eval(eFormula),0)
'if err then evaluate = err.description & ": " & eformula
if err then evaluate = 0
end function
%>
<%
period = split(request.form("period"),",")
if ubound(period) = 1 then
mmmm = period(0)+0
yyyy = period(1)+0
else
mmmm = 0
yyyy = 0
end if
%>
<div align="left" class="MainBodyText" style="width: 100%; height: 100%">
<div align="left" valign="top" width="200" style="margin:5; padding:3; font-family:arial; font-size:10pt; font-weight:bold; font-color000000;" >
<% if session("groupid") = 1 then%>
<a href="../main/administration.asp"> <<< Administration Home</a><br><br>
<a href="manager_report.asp"> <<< Managers Report Home</a>
<%end if %>
<%if session("groupid") = 2 then%>
<a href="../main/manager.asp"> <<< Manager Home</a><br><br>
<a href="manager_report.asp"> <<< Managers Report Home</a>
<%end if %>
<%if session("groupid") = 3 then%>
<a href="../main/user.asp"> <<< User Home</a>
<%end if %>
<%if session("groupid") = 4 then%>
<a href="../main/general.asp"> <<< Home</a><br><br>
<a href="manager_report.asp"> <<< Managers Report Home</a>
<%end if %>
</div>
<p align="center" class="MainBodyText">
<img src="../../images/si.gif" align="center"><br><b>Month to Date Dashboard</b></p>
<p align="center" class="MainBodyText">
To use the Print Page function please select the period required then select
view report. You can then select Print Page which will print out the rendered
page.</p>
<form name="f1" method="POST" action="dialmtd.asp">
<div id="printReady">
<table align="center" border="0" width="663" style="border-collapse: collapse" bordercolor="#111111" cellpadding="2">
<tr>
<td align="left" width="127"><font face="Arial" size="2">
<select name="benchmarkgroup" style="color: #000000; border: 0px solid #000000; background-color: #FFE737">
<option value="0" <%if request.form("benchmarkgroup") = "" then response.write "selected"%>>Select Report
<% set rs = cn.execute("select TaskGroupID,TaskGroup from BenchmarkGroups where Active = 1 order by TaskGroupID")
do until rs.eof
if request.form("taskgroupid")+0 = rs.fields(0) then selected = "selected" else selected = "" %>
<option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>
<td align="left" width="143"><font face="Arial" size="2">
<select name="period">
<option value="" <%if request.form("period") = "" then response.write "selected"%>>Select Period
<% set rs = cn.execute("select year(WeekEnding), month(WeekEnding), format(Weekending,""mmmm yyyy"") from data group by Year(WeekEnding), Month(WeekEnding), format(WeekEnding,""mmmm yyyy"") order by 1,2")
do until rs.eof
x = rs.fields(1) & "," & rs.fields(0)
if request.form("period") = x then selected = "selected" else selected = "" %>
<option value=<%=x & " " & selected%>><%=rs.fields(2)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>
<td align="left" width="117"><font face="Arial" size="2">
<select name="stateid">
<option value="0" <%if request.form("stateid") = "" then response.write "selected"%>>All States
<% set rs = cn.execute("select stateid,state from tblState where benchmarkactive order by state")
do until rs.eof
if request.form("stateid")+0 = rs.fields(0) then selected = "selected" else selected = "" %>
<option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>
<td align="left" width="127"><font face="Arial" size="2">
<select name="countryid">
<option value="0" <%if request.form("countryid") = "" then response.write "selected"%>>All Countries
<% set rs = cn.execute("select countryid,country from tblCountry where benchmarkactive order by country")
do until rs.eof
if request.form("countryid")+0 = rs.fields(0) then selected = "selected" else selected = "" %>
<option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>
<td align="left" width="129"><input type="image" src="../../Images/view-report.gif" onclick="f1.submit()" name="f1" alt="Click Here to Generate Report"></td>
<td width="127"><input type="image" src="../../Images/print-report.gif" rname="printMe" onClick="printSpecial()" alt="Click Here to Print the Page"></td>
</tr>
</table>
<% if mmmm > 0 then %>
<table border="0" width="450" align="left" style="margin-left:10px; border-collapse: collapse" bordercolor="#111111" cellpadding="2" cellspacing="4">
<thead>
<tr>
<% sql = " where 1 = 1 AND BenchmarkGroup =" & request("benchmarkgroup")
if request.form("stateid")+0 then sql = sql & " and stateid = " & request.form("stateid")
if request.form("countryid")+0 then sql = sql & " and countryid = " & request.form("countryid")
set rs = cn.Execute("TRANSFORM First(UserID) SELECT description, minimum, mintype, formula FROM BenchmarksActiveUsers" & sql & " GROUP BY displayOrder, description, minimum, mintype, formula ORDER BY displayOrder PIVOT Name")
lastfield = rs.fields.count-1
s = 4 %>
<td align="left" bgcolor="#C0C0C0"><font face="Arial" color="#000000" size="2"><b>Benchmark</b></font> </td>
<td align="center" bgcolor="#C0C0C0"><font face="Arial" color="#000000" size="2"><b>Company Average</b></font> </td>
<% for i = s to lastfield %>
<% next %>
</tr>
</thead>
<tbody>
<br>
<% dim v()
do until rs.eof
min = int(rs.fields("minimum"))
mintype = rs.fields("mintype")
sumv = 0
avg = 0
n = 0
for j = s to lastfield
redim preserve v(j)
v(j) = evaluate(rs.fields("formula"),rs.fields(j),mmmm,yyyy)
sumv = sumv + v(j)
n = n + 1
next
if n > 0 then avg = round(sumv / n) %>
<tr><td></td><td align="center" valign="bottom"><font face="Arial" size="2">Target: <%=min%> <%=mintype%></font></td><td></td></tr>
<tr>
<td bgcolor="#FFFFFF">
<p align="right"><font face="Arial" size="2"><%=rs.fields("description")%></font></td>
<td bgcolor="#FFFFFF">
<p align="center"><font face="Arial" size="1"><% If (avg/min)*100 <=12 Then %><IMG SRC="../images/dial-1.gif">
<% ELSEIF (avg/min)*100 <=24 Then%><IMG SRC="../images/dial-2.gif">
<% ELSEIF (avg/min)*100 <=36 Then%><IMG SRC="../images/dial-3.gif">
<% ELSEIF (avg/min)*100 <=48 Then%><IMG SRC="../images/dial-4.gif">
<% ELSEIF (avg/min)*100 <=60 Then%><IMG SRC="../images/dial-5.gif">
<% ELSEIF (avg/min)*100 <=72 Then%><IMG SRC="../images/dial-6.gif">
<% ELSEIF (avg/min)*100 <=86 Then%><IMG SRC="../images/dial-7.gif">
<% ELSEIF (avg/min)*100 <=99 Then%><IMG SRC="../images/dial-8.gif">
<% ELSEIF (avg/min)*100 =100 Then%><IMG SRC="../images/dial-9.gif">
<% ELSEIF (avg/min)*100 <=112 Then%><IMG SRC="../images/dial-10.gif">
<% ELSEIF (avg/min)*100 <=124 Then%><IMG SRC="../images/dial-11.gif">
<% ELSEIF (avg/min)*100 <=136 Then%><IMG SRC="../images/dial-12.gif">
<% ELSEIF (avg/min)*100 <=148 Then%><IMG SRC="../images/dial-13.gif">
<% ELSEIF (avg/min)*100 <=160 Then%><IMG SRC="../images/dial-14.gif">
<% ELSEIF (avg/min)*100 <=172 Then%><IMG SRC="../images/dial-15.gif">
<% ELSEIF (avg/min)*100 <=186 Then%><IMG SRC="../images/dial-16.gif">
<% ELSEIF (avg/min)*100 =>187 Then%><IMG SRC="../images/dial-17.gif">
<%END IF%></font></td><td><img src="../images/under-target.gif"><br><br><img src="../images/over-target.gif"></td>
</tr><tr><td></td><td valign="top" align="center"><font face="Arial" size="2">Actual: <%=avg%> <%=mintype%></font><hr></td><td></td></tr><% for j = s to lastfield
color = "#000000"
if int(v(j)) < min then color="#FF0000" %>
<% next %>
</tr>
<% rs.movenext
loop
rs.close
cn.close
set cn = nothing %>
</tbody>
</table>
<% end if %>
</div>
</form>
</div>
<!-- #include file="./../../common/includes/i_footer.asp" -->
View 5 Replies
View Related
Aug 2, 2004
Hi,
I'm about to embark on writing some code in perl or VBscript that automatically synchronises a constantly updated Access database with an MSSQL database.
I know MSSQL has an import tool built into Enterprise manager but I'm wondering if theres a stored procedure that does this?
The way I'm thinking of doing it is to read the all the access tables into separate hash arrays and then INSERTing them into the MSSQL database after checking for any duplicates. This all sounds a bit time consuming (there are a large number of tables) and processor intensive.
If anyones done anything like this before, I'd love to hear their views......!
Thanks!
View 9 Replies
View Related
Dec 16, 2005
I have an application that uses Acces as a backend and VBA as front end. Application is secured and is supplied on a CD with setup.exe.
Can I use VB 2005 and MS SQL to achieve the same?
Would I be able to package my application with all the neccessery files (assuming that client does not have any e.g. SQL server) so that multiple front ends can access data from common source?
Would I be able to secure such an application using only VS 2005?
What would I need to quickly learn how to achieve the above ( any books you can suggest maybe)?
View 1 Replies
View Related
Nov 18, 2006
I have a database that is in mssql and I'm using an odbc link to an access database where I want to add records to the mssql table. When I open the linked table in access it does not allow me to add a record. I have created a user account in mssql that has ownership to the database and I use this user in setting up the odbc link.
View 3 Replies
View Related
Oct 16, 2007
Hello everybody,
I'am currently testing a MSSQL 2005 mirroring implementation with automatically failover.
1 principal server ====> Server A
1 mirror server ====> Server B
1 witness server ====> Server C
When server A fails Server B becomes Principal. ( which is the general idea ) This is working perfectly.
The problem is when i try to connect to the mirror Server B ( actually the new principal )
I use the .NET connection string with the failover parameter ( even without the parameter ) the connection fails.
( the general error : unable to connect, or connection property has not been initialized )
Data Source=ServerA; Failover Partner=ServerB;Initial Catalog=DBMTest;uid=dbuser;pwd=dbpassword;
This connection property works fine when server A (original principal) is online.
When i run the SQL profiler with the mirror db as profile, i dont see the traffic.
So you think the mirror server has a network problem or was not connfigured for remote access.
i thought that also, but when i create a 'stand alone - non mirrored' database the same application with the same connetion string ( well.....different db) works fine.
The problem must be in the whole mirroring implementation....... ( my idea )
Does anyone had the same problem, or better, doed anyone know how to solve it?
Hope to hear from you soon!
Thank you in advance!
Regards,
W.schmitt
The netherlands
View 6 Replies
View Related
Jan 4, 2007
Hello,
I'm trying to access the MSSQl 'remotey'. I open telnet and go to 127.0.0.1 1433 but can't. It seems I have done everything I can to fix it, but nothing solves the problem.
My firewall is disabled.
I have TCP/IP enabled in "SQL Server Configuration Manager" for both Client Protocol and protocols for MSSSQLSERVER (Shared Memory and NAmed Pipes are also both enabled). VIA is disabled.
In SQL Services, SQL Server Integration Services, Full Text Searc, SQL Server, SQL Server Analysis Services, and Server Browser are running. SQL Server Agent is disabled.
In Server Surface Area configuration, under Database Engine is running (automatic) and "Remote Connections" is enabled to TCP/IP and named pipes. Analysis Service has Local and Remote connections checked and is running (automatic). SQL Server agent is stopped.
Integration Services is running (Automatic) SQL Server Browser is running. (Automatic)
When I look in the error log, it shows...
Date 1/4/2007 3:21:45 AM
Log SQL Server (Current - 1/4/2007 3:21:00 AM)
Source Server
Message
Server is listening on [ 127.0.0.1 <ipv4> 1434].
Date 1/4/2007 3:21:45 AM
Log SQL Server (Current - 1/4/2007 3:21:00 AM)
Source Server
Message
Server is listening on [ 'any' <ipv4> 1433].
I have tried to telnet to port 1434 and 1433 but nothing happens. Does anyone know what else I need to be able to access that port??
Thanks
Foley
View 3 Replies
View Related
Feb 14, 2008
Writing to tables created by regular users on MSSQL2005
I have users creating tables through an application, I gave them ddl_admin, datareader, datawriter. They can create tables but cannot insert/update data (to their own tables), I cannot insert data either using Access or any other application to those tables created by them (under dbo schema) Is there something I am missing with permissions? Thank you very much
View 3 Replies
View Related
Apr 28, 2004
Trying to find which Employees have NOT entered a record (timecard).
Table #1 Employee_Hours
Table #2 Employee (List active)
Table #1
SELECT EID, DATEPART(wk,[Date]) AS Week, MAX([Date]) AS WeekEnding
FROM Employee_Hours
WHERE ([Date] Between '1/1/04' AND '12/31/04')
GROUP BY EID, DATEPART(wk,[Date])
HAVING MAX([Date]) = '1/24/04' --Pay period
Table #2
SELECT EID, DisplayName
FROM Employee
WHERE (Active=1)
I tried subqueries and temp tables but can't get the results I need. The first query will return the employees who entered their time for the given pay period.
Thanks
View 1 Replies
View Related
Jun 15, 2004
Hi,
I have the following sql statement:
INSERT INTO prg_lvl VALUES (
'Data Recovery',
'Data Recovery',
7,
'ENG',
0,
16,
'Menu=w_data_recovery_stk;',
( select max(menu_id) + 1 from prg_lvl ),
'K');
that works for Sybase and I wanna make it works for SQL Server.
The problem is the use ot the subquery (select max(menu_id) + 1 from prg_lvl).
Could I use subqueries in SQL Server in general or is there is any other method to overcome this problem
Thanks,
fady
View 3 Replies
View Related
Oct 14, 2005
Hi,
I would like to know
What is Corelated SubQueries??
Thanks in Advance
View 3 Replies
View Related
Jan 14, 2008
I am very new to SQL Server, working with it in class. I am trying to do procedures involving subqueries using the Northwind database, and am having a rough time.
For instance, I have the following code, which lists all orders for the month of November, 1996:
SELECT Customers.CustomerID, Customers.CompanyName, CAST(Orders.OrderDate AS CHAR (11) )AS OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
AND OrderDate BETWEEN '11/1/1996' AND '11/30/1996' ;
I have been asked to do the same thing, but using a subquery. I came up with the following:
SELECT CustomerID, CompanyName FROM Customers a
WHERE EXISTS (SELECT OrderDate FROM Orders b WHERE a.CustomerID = b.CustomerID
AND OrderDate BETWEEN '11/1/1996' AND '11/30/1996' ) ;
However, it does not list the Order Date, as I want it to, and the information is not correct (it is missing a few orders from November '97 that should show up. Can someone tell me what I am doing incorrectly?
Please note that I do not need anyone to give me the code, but simply give me an idea of what I am doing incorrectly, or let me know how it can be done, so that I may do it. =-)
View 16 Replies
View Related
Apr 12, 2006
Hi all,I've seen mention that you can use nested subqueries down to as manylevels as you like but whenever I run the following:select * from table1where tab1ID in(select tab1ID from table2 where tab2ID in(select tab2ID from Table3 where Tab3ID=N))I get the error "incorrect syntax near the keyword 'where'"Can anyone confirm that sub-subqueries are possible? If so is thesyntax I'm using correct?I'm running SQL 2000 through a Delphi 5 app.RegardsJon
View 2 Replies
View Related
Jul 20, 2005
Guys,Got this query...---------------------------SELECT TOP 5 Tbl_admin_hotels.HotelName,(SELECT COUNT(Tbl_marketing_history.HotelID)FROM Tbl_marketing_historyWHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)AS CountTotal,(SELECT MAX(DateSent)FROM Tbl_marketing_historyWHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)AS LastSentFROM Tbl_admin_hotelsWHERE NOT Tbl_admin_hotels.HotelID = 99ORDER BY CountTotal DESC---------------------------Within the table Tbl_marketing_history there is also a 'Subject' column thatI require, the row to grab this Subject column from should relate to the rowI'm selecting in the SubQuery to grab 'DateSent'.I've tried and tried to grab multiple columns but failed miserably? Cananyone help pleaseeee!Cheers, Ash
View 12 Replies
View Related
Sep 27, 2007
In this stored procedure, I want to grab all the information for Sales Representatives that made less then 6 visits. I have done that in the subquery. But I also want to be able to display their visits by Dates where any number of visits was made. Meaning, i want to be able to show Everyday where a visit was made, but i only want the records that are less then 6 visits. So I want nulls to show up for the dates where no visits were made. So I can track the people that didnt make visits, on days other made visits (which means they should have visits too)
So basically my report looks like this:
09-10-2007 09-11-2007 09-14-2007
Tom Smith 1 3 4
Janice Cooper 3 5 5
Troy Slater 5 4 2
Kelly Short 4 4 4
When i want it to look like this:
09-10-2007 09-11-2007 09-12-2007 09-13-2007 09-14-2007
Tom Smith 1 3 null null 4
Janice Cooper 3 5 null null 5
Troy Slater 5 4 null null 2
Kelly Short 4 4 null null 4
Its an exception report, so i want to show what was not done , on days that are supposed to have things done, i cant show this, if dates are not displayed before there were no visits less then 6. I want to display all relevant dates, where any number of visits was mad. But only show the visits that were less then 6.
Here's my stored procedure that already has the less then 6 visits showing. But doesnt always show the dates. What should i do?
Code Block
SELECT dbo.Qry_Visits.Status,
dbo.Qry_Visits.Customer_code,
Qry_Sales_Group.Name,
dbo.Qry_Sales_Group.SR_Name,
dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,
dbo.Qry_Date_Dim.Date_Dimension_Date,
dbo.Qry_Date_Dim.Day_Of_Month,
dbo.Qry_Sales_Group.Region,
dbo.Qry_Visits.period_code,
dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,
dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,
dbo.Qry_Date_Dim.Date_Dimension_Year,
dbo.Qry_Date_Dim.Date_Dimension_Period,
CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,
dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code],
B.VisitsTotal
FROM dbo.Qry_Visits
INNER JOIN dbo.Qry_Sales_Group
ON dbo.Qry_Visits.[SR Code]
COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
COLLATE Latin1_General_CI_AS
INNER JOIN dbo.Qry_Date_Dim
ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)
INNER JOIN ( Select COUNT(Visits)as VisitsTotal,[Sales Responsible],CONVERT(VARCHAR,(Qry_Visits.time_log),110)TheDate,Qry_Visits.Status
FROM dbo.Qry_Visits
WHERE Qry_Visits.Status=2
GROUP by [Sales Responsible] , CONVERT(VARCHAR,(Qry_Visits.time_log),110),Qry_Visits.Status
HAVING SUM(Visits) < 6)B
ON dbo.Qry_Sales_Group.SR_Name COLLATE Latin1_General_CI_AS = B.[Sales Responsible] COLLATE Latin1_General_CI_AS AND
CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = B.TheDate
WHERE Qry_Visits.Status=2
ORDER BY dbo.Qry_Sales_Group.SR_Name, CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110)
View 5 Replies
View Related
Dec 23, 2007
Hey everyone, I'm running into an issue where I have a select statement that retrieves all employee records from a employee table. In this statement I'm displaying first and last name, titlte and the employees manager id(which is the employees id). What I want is a query that instead of displaying the manager's id, display the manager's name. The query that I'm using below doesn't work. I was hoping that someone could help me out:
SELECT LastName, FirstName, Title, Extension,
(SELECT FirstName, LastName
FROM Employees
WHERE (ReportsTo = EmployeeID)) AS Reports
FROM Employees
Here's the data that I'm using:
LastName, First Name Title Extension Reprots To
Davolio
Nancy
Sales Representative
5467
13
Fuller
Andrew
Vice President, Sales
3457
0
Leverling
Janet
Sales Representative
3355
12
When I run this query I get a error saying "Only one expression can be specified in the select list when the subquery is not introduced with Exists"
Thanks in advance for your help.
View 5 Replies
View Related
Sep 20, 2007
So I already no this can't be done... but I need a suitable alternative (if one exists) so I don't have to competely re-write this already too huge query.
Anyways, in my select I have something like this:
sum( case when code in (1,2,3,4) then 0 else 1 end ) as total
which has now increase from four static values to a more dynamic format, that being a table with 47 values which may shrink or grow over time. Attempting the following fails:
sum( case when code in (select code_id from ExcludedCodes) then 0 else 1 end ) as total
because apparently you can't use selects or aggregates within an aggregate.
So any ideas on how I can get this working... is there no Array or List type variable I could just substitute in? I've already tried using a Table Variable, but that failed as well.
Please keep in mind, this is one line of a very large select containing many other fields and aggregates (on a fair amount of joins) which is used in at least four differerent reporting queries. If there is no quick and easy replacement trick I can do just let me know so I can start re-writing all of them (which is seriously going to make my head hurt).
View 4 Replies
View Related
Dec 23, 2005
Hi, I have searched through this forum and haven't found the answer to a problem I am having so I figured I will post it.
My SQL statement:
Select * FROM tblData WHERE LastName=
(SELECT tblData.LastName FROM tblData GROUP BY tblData.LastName HAVING (Count(tblData.LastName)>1))"
What I am trying to do: I am trying to make an SQL filter that I can apply to my form in order to only show records with duplicate last names. The sub query returns names that are already in the table. I then compare what is found to be duplicate with the original table in order to just show only the duplicate records. Everything works fine as long as there is only one name thats duplicated.
When there are multiple duplicate names then I run into an error.
When the statement is put into a string and executed in VBA I get this error:
"Run-time error '3021': No current record."
When the statement is put into a query and run against the DB I get this error:
"At most one record can be returned by this subquery"
So yeah, any help would be greatly appreciated. Am I going about this all wrong or am I just forgetting something? Thanks for any help.
View 2 Replies
View Related
Jun 15, 2008
Hello,
Lets consider two tables : CUSTOMERS and ORDERS.
I would like to know which of the following query is the fastest:
select * from customers C
where exists (select 0 from ORDERS O
where C.Name like 'A%' and O.Charged = 1)
select * from customers C
where exists (select 0 from ORDERS O
where O.Charged = 1) and C.Name like 'A%'
I don't want to use a JOIN clause. I just want to know if there is a difference of efficiency when the condition on C.Name is inside or outside the sub query.
Thank you in advance for any advices,
regards,
mathmax
View 2 Replies
View Related
