ACCESS SQL ?

G

Guest

Hi
I need to add filter or parameter that will only include include -1 from
a col named Use from my table tblwebcvp how can I add to the query that was
created in SQL? Thanks enclosed is the syntax



select
min(a.event_date) AS StartDate,
max(a.event_date) AS EndDate,
b.Site,
Count(*) as Surveys,
Case WHEN(SUM(c.CS) + SUM(c.HTS) + SUM(c.KS) + SUM(c.O) + SUM(c.RS)) IS NULL
THEN 0
ELSE CAST((CAST(CAST((SUM(c.CS) + SUM(c.HTS) + SUM(c.KS) + SUM(c.O) +
SUM(c.RS)) AS FLOAT)/(Sum(CVPID)*5) AS NUMERIC(3,2))) * 100 AS INT)
END AS DisSat,
Case WHEN(SUM(c.CSTop) + SUM(c.HTSTop) + SUM(c.KSTop) + SUM(c.OTop) +
SUM(c.RSTop)) IS NULL
THEN 0
ELSE CAST(CAST(CAST((SUM(c.CSTop) + SUM(c.HTSTop) + SUM(c.KSTop) +
SUM(c.OTop) + SUM(c.RSTop)) AS DECIMAL(10,2))/(Sum(CVPID)*5) AS
DECIMAL(10,4)) * 100 AS DECIMAL(10,3))
END AS SatTop
from tblwebcvp a
INNER JOIN tblEIEmployeeInfo b ON a.UID = b.[Employee ID]
INNER JOIN viewWebCVPSummary c ON a.uid = c.uid AND a.event_date =
c.event_date
where a.event_date between '2007-08-01 00:00:00' AND '2007-08-31 23:59:59'
and a.useable = '1'
and b.Site in ('AL','SAT')
and c.CS is not null
and c.HTS is not null
and c.KS is not null
and c.O is not null
group by b.Site
order by b.Site
 
J

John W. Vinson

Hi
I need to add filter or parameter that will only include include -1 from
a col named Use from my table tblwebcvp how can I add to the query that was
created in SQL? Thanks enclosed is the syntax



select
min(a.event_date) AS StartDate,
max(a.event_date) AS EndDate,
b.Site,
Count(*) as Surveys,
Case WHEN(SUM(c.CS) + SUM(c.HTS) + SUM(c.KS) + SUM(c.O) + SUM(c.RS)) IS NULL
THEN 0
ELSE CAST((CAST(CAST((SUM(c.CS) + SUM(c.HTS) + SUM(c.KS) + SUM(c.O) +
SUM(c.RS)) AS FLOAT)/(Sum(CVPID)*5) AS NUMERIC(3,2))) * 100 AS INT)
END AS DisSat,
Case WHEN(SUM(c.CSTop) + SUM(c.HTSTop) + SUM(c.KSTop) + SUM(c.OTop) +
SUM(c.RSTop)) IS NULL
THEN 0
ELSE CAST(CAST(CAST((SUM(c.CSTop) + SUM(c.HTSTop) + SUM(c.KSTop) +
SUM(c.OTop) + SUM(c.RSTop)) AS DECIMAL(10,2))/(Sum(CVPID)*5) AS
DECIMAL(10,4)) * 100 AS DECIMAL(10,3))
END AS SatTop
from tblwebcvp a
INNER JOIN tblEIEmployeeInfo b ON a.UID = b.[Employee ID]
INNER JOIN viewWebCVPSummary c ON a.uid = c.uid AND a.event_date =
c.event_date
where a.event_date between '2007-08-01 00:00:00' AND '2007-08-31 23:59:59'
and a.useable = '1'
and b.Site in ('AL','SAT')
and c.CS is not null
and c.HTS is not null
and c.KS is not null
and c.O is not null
group by b.Site
order by b.Site
Click to expand...

You've got lots of problems other than that, due to dialect differences
between JET and SQL/Server. JET doesn't support the CASE statement, the CAST
function; it uses # rather than ' as a date delimiter...

What version of Access are you using? In 2003 you can use the SQL92
compatibility setting in Options which may resolve part of these; but if you
want to use native Access this will have to be completely redone (since you're
making so much use of SQL-specific code).


John W. Vinson [MVP]
 
G

Guest

Hi John W. Vinson
I'm using Access 2002
The SQL query I posted works in Access enclosed is a report it produces.
StartDate EndDate Site Surveys DisSat SatTop
08/01/2007 09/01/2007 AL 2790 17 73.33
08/01/2007 09/01/2007 SAT 2931 15 75.4
I would like to filter only for 1 from a col named Use from my table
tblwebcvp Is this possible?

John W. Vinson said:
Hi
I need to add filter or parameter that will only include include -1 from
a col named Use from my table tblwebcvp how can I add to the query that was
created in SQL? Thanks enclosed is the syntax



select
min(a.event_date) AS StartDate,
max(a.event_date) AS EndDate,
b.Site,
Count(*) as Surveys,
Case WHEN(SUM(c.CS) + SUM(c.HTS) + SUM(c.KS) + SUM(c.O) + SUM(c.RS)) IS NULL
THEN 0
ELSE CAST((CAST(CAST((SUM(c.CS) + SUM(c.HTS) + SUM(c.KS) + SUM(c.O) +
SUM(c.RS)) AS FLOAT)/(Sum(CVPID)*5) AS NUMERIC(3,2))) * 100 AS INT)
END AS DisSat,
Case WHEN(SUM(c.CSTop) + SUM(c.HTSTop) + SUM(c.KSTop) + SUM(c.OTop) +
SUM(c.RSTop)) IS NULL
THEN 0
ELSE CAST(CAST(CAST((SUM(c.CSTop) + SUM(c.HTSTop) + SUM(c.KSTop) +
SUM(c.OTop) + SUM(c.RSTop)) AS DECIMAL(10,2))/(Sum(CVPID)*5) AS
DECIMAL(10,4)) * 100 AS DECIMAL(10,3))
END AS SatTop
from tblwebcvp a
INNER JOIN tblEIEmployeeInfo b ON a.UID = b.[Employee ID]
INNER JOIN viewWebCVPSummary c ON a.uid = c.uid AND a.event_date =
c.event_date
where a.event_date between '2007-08-01 00:00:00' AND '2007-08-31 23:59:59'
and a.useable = '1'
and b.Site in ('AL','SAT')
and c.CS is not null
and c.HTS is not null
and c.KS is not null
and c.O is not null
group by b.Site
order by b.Site
Click to expand...

You've got lots of problems other than that, due to dialect differences
between JET and SQL/Server. JET doesn't support the CASE statement, the CAST
function; it uses # rather than ' as a date delimiter...

What version of Access are you using? In 2003 you can use the SQL92
compatibility setting in Options which may resolve part of these; but if you
want to use native Access this will have to be completely redone (since you're
making so much use of SQL-specific code).


John W. Vinson [MVP]
Click to expand...
 
J

John W. Vinson

I would like to filter only for 1 from a col named Use from my table
tblwebcvp Is this possible?
Click to expand...

Sure. Add the column Use and set a criterion of 1.

....
where a.event_date between '2007-08-01 00:00:00' AND '2007-08-31 23:59:59'
and a.useable = '1'
and b.Site in ('AL','SAT')
and c.CS is not null
and c.HTS is not null
and c.KS is not null
and c.O is not null
and tblwebcvp.Use = 1

or Use = '1' if Use is of Text datatype.

John W. Vinson [MVP]
 
J

Jamie Collins

select
min(a.event_date) AS StartDate,
max(a.event_date) AS EndDate,
b.Site,
Count(*) as Surveys,
Case WHEN(SUM(c.CS) + SUM(c.HTS) + SUM(c.KS) + SUM(c.O) + SUM(c.RS)) IS NULL
THEN 0
ELSE CAST((CAST(CAST((SUM(c.CS) + SUM(c.HTS) + SUM(c.KS) + SUM(c.O) +
SUM(c.RS)) AS FLOAT)/(Sum(CVPID)*5) AS NUMERIC(3,2))) * 100 AS INT)
END AS DisSat,
Case WHEN(SUM(c.CSTop) + SUM(c.HTSTop) + SUM(c.KSTop) + SUM(c.OTop) +
SUM(c.RSTop)) IS NULL
THEN 0
ELSE CAST(CAST(CAST((SUM(c.CSTop) + SUM(c.HTSTop) + SUM(c.KSTop) +
SUM(c.OTop) + SUM(c.RSTop)) ASDECIMAL(10,2))/(Sum(CVPID)*5) AS
DECIMAL(10,4)) * 100 ASDECIMAL(10,3))
END AS SatTop
from tblwebcvp a
INNER JOIN tblEIEmployeeInfo b ON a.UID = b.[Employee ID]
INNER JOIN viewWebCVPSummary c ON a.uid = c.uid AND a.event_date =
c.event_date
where a.event_date between '2007-08-01 00:00:00' AND '2007-08-31 23:59:59'
and a.useable = '1'
and b.Site in ('AL','SAT')
and c.CS is not null
and c.HTS is not null
and c.KS is not null
and c.O is not null
group by b.Site
order by b.Site
Click to expand...

You've got lots of problems other than that, due to dialect differences
between JET and SQL/Server. JET doesn't support ... CAST
function
Click to expand...

Because Jet [no need to shout 'JET' <g>; Microsoft documentation
almost exclusively uses proper case 'Jet'] uses VBA's expression
services, it has explicit cast functions for data types in common:

CInt(n) for CAST(n AS SMALLINT) [SQL keywords, not shouting <g>]
CLng(n) for CAST(n AS INTEGER)
etc.

A few have no direct equivalents but are still achievable e.g.

Left$(CStr(t), 4) for CAST(t AS NCHAR(4))

One exception is CDec(), which is broken in Jet; see

http://support.microsoft.com/kb/225931
ACC2000: "Wrong Number of Arguments" Error When You Use CDec Function
in a Query

i.e. the OP may have a problem with
CAST(n AS DECIMAL(10,4)) etc

AFAIK the best that can be done is to *coerce* the result to
DECIMAL(28, 4) by operating on the value using another value of
decimal scale four, noting that the precision will always be 28 e.g.
consider this quick demo:

Sub DecCoerce()
Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"

' Use decimal literal of type DECIMAL(10, 4)
Dim rs As ADODB.Recordset
Set rs = .ActiveConnection.Execute( _
"SELECT CLNG(1)" & _
" + 1234567890.1234" & _
" - 1234567890.1234 AS test_result;")
With rs.Fields(0)

' Type=131 -> adNumeric -> DECIMAL
MsgBox _
"Value = " & .value & vbCr & _
"Type = " & .Type & vbCr & _
"Precision = " & .Precision & vbCr & _
"Numeric Scale = " & .NumericScale
End With
Set .ActiveConnection = Nothing
End With
End Sub

returns message:

Value - 1
Type = 131
Precision = 28
Numeric Scale = 4

Jamie.

--
 
Top