SQL Queries & MSAccess Databases / Dreamweaver Utl

Results 1 to 2 of 2

Thread: SQL Queries & MSAccess Databases / Dreamweaver Utl

  1. #1
    Join Date
    Dec 1969

    Default SQL Queries & MSAccess Databases / Dreamweaver Utl

    I think this is a fairly complex one, so I&#039;ll try to explain fully. I <BR>have a table in Access called "tbl_Quality_Alert" where there are <BR>various fields that store various snippets of information. The <BR>problem is that I need to create some statistics from the database <BR>entries. I need the database to return a count of records that fit <BR>the specific criteria ...<BR><BR>I want an SQL statement that works in UltraDev4 that looks up <BR>the "DatePart" of the [Date_Alert] field with a Request.Form("id") <BR>variable - specifically the "month" and "year" - which would be <BR>selected on a previous page as a drop-down menu (the month will be <BR>passed as "id" and the year will be passed as "idx"). Additionally, I <BR>need the SQL statement to find all records where [Hostpital_Clinic] <BR>Is Not Null. In addition to this, I only need the database to "Count" <BR>the results, this count figure will be manipulated later in some <BR>formula-packed VBScripting.<BR><BR>I have come up with the following SQL statement - but it returns an <BR>error "Too few Parameters, expected 3":<BR><BR>SELECT Count(tbl_Quality_Alert.Quality_Alert_ID) AS <BR>CountOfQuality_Alert_ID, tbl_Quality_Alert.Quality_Alert_ID, <BR>tbl_Quality_Alert.Department_ID, tbl_Quality_Alert.Hopital_Clinic<BR>FROM tbl_Quality_Alert<BR>WHERE (DatePart("m",[Date_Alert]))=varid AND (DatePart("yyyy",<BR>[Date_Alert]))=varidx <BR>GROUP BY tbl_Quality_Alert.Quality_Alert_ID, <BR>tbl_Quality_Alert.Department_ID, tbl_Quality_Alert.Hopital_Clinic <BR>HAVING (((tbl_Quality_Alert.Hopital_Clinic) Is Not Null Or <BR>(tbl_Quality_Alert.Hopital_Clinic)&#060;&#062; "N/A"));<BR><BR>Once I manage to get this to work, I can then add some formula to the <BR>results of the query. For those interested, the formula is a Quality <BR>Standards formula for Injury-related Medical Treatment. The formula <BR>is:<BR><BR>No of Incidents (SQL Query Results) X 1,000,000 / No Hours Worked*<BR><BR>(* extracted from a different Table). This result will be <BR>called "MTIFR".<BR><BR>I can easily retrieve the No Hours Worked from a different Table by <BR>using similar Request.Form("id") variables (as mentioned earlier). <BR>This table is called tbl_Statistics with fields [Month_Year] and <BR>[Hours_Worked] and a few others, but they&#039;re the main ones.<BR><BR>The end result will draw up a table on the web page like:<BR><BR>&#124 Hours(m) &#124 Hours (ytd) &#124 No of MTI(m) &#124 No of MTI(ytd) &#124 MTIFR &#124<BR>&#124 5026 &#124 9706 &#124 1 &#124 1 &#124 103.02 &#124<BR><BR>Where MTI(m) is the result of my niggling SQL Query that wont work! <BR>I can get MTI(ytd) to work - is fairly easy - by using "between <BR>01/01/01 to 31/12/01") but this will need to be updated each year. I <BR>would prefer to pass the DatePart("yyyy",[Date_Alert]) as Request.Form<BR>("idx") variable - that way no need to update the code for the next <BR>10 years!<BR><BR>Hmmm - I really hope someone can help me with this as I&#039;m flicking <BR>through my WROX ASP books, my DreamWeaver UltraDev4 book, my SQL <BR>(SAMS) book - and none help me!!<BR><BR>Thanks in advance,<BR>Ranz

  2. #2
    Join Date
    Dec 1969

    Default RE: SQL Queries & MSAccess Databases / Dreamweaver

    Use Response.Write in your asp page,<BR>check names of your fields in db and in your query ...<BR>...<BR>strSQL="SELECT Count(tbl_Quality_Alert.Quality_Alert_ID) AS <BR>CountOfQuality_Alert_ID, tbl_Quality_Alert.Quality_Alert_ID, <BR>tbl_Quality_Alert.Department_ID, tbl_Quality_Alert.Hopital_Clinic <BR>FROM tbl_Quality_Alert <BR>WHERE (DatePart("m",[Date_Alert]))=varid AND (DatePart("yyyy", <BR>[Date_Alert]))=varidx <BR>GROUP BY tbl_Quality_Alert.Quality_Alert_ID, <BR>tbl_Quality_Alert.Department_ID, tbl_Quality_Alert.Hopital_Clinic <BR>HAVING (((tbl_Quality_Alert.Hopital_Clinic) Is Not Null Or <BR>(tbl_Quality_Alert.Hopital_Clinic)&#060;&#062; "N/A"));" <BR>&#039;see what you get in query<BR>Response.Write "strSQL="&(strSQL)&"<BR>"<BR>

Posting Permissions

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