Syntax error (missing operator) in query expression

G

Guest

Hello,

I can't figure out why am I getting above message on this query:

SELECT
dbo_mainCustomer_Florida.customerID,qDRI2.tDRI2,qDRISF.tDRISF,qRRI.tRRI,dbo_mainCustomer_Florida.agencyName,dbo_mainCustomer_Florida.state
,dbo_mainCustomer_Florida.availCredits
FROM dbo_mainCustomer_Florida INNER Join
(SELECT customerID,COUNT(*) tDRI2 FROM dbo_mainCustomer_Florida MC JOIN
dbo_evaluators2_Florida EV ON MC.customerID = EV.Customer_ID JOIN
dri2_Offenders DRI2 ON
EV.password = DRI2.userpassword WHERE Cdate(DRI2.testDate) BETWEEN
#5/5/2005# AND #5/5/2006# GROUP BY customerID) qDRI2 ON
dbo_mainCustomer_Florida.customerID = qDRI2.customerID LEFT OUTER JOIN
(SELECT customerID, COUNT(*) tDRISF FROM dbo_mainCustomer_Florida MC JOIN
dbo_evaluators2_Florida EV ON MC.customerID = EV.Customer_ID JOIN
drisf_Offenders DRISF ON
EV.Password = DRISF.userpassword WHERE cdate(DRISF.testDate) BETWEEN
#5/5/2005# AND #5/5/2006# GROUP BY customerID) qDRISF ON
dbo_mainCustomer_Florida.customerID = qDRISF.customerID LEFT OUTER JOIN
(SELECT customerID, COUNT(*) tRRI FROM dbo_mainCustomer_Florida MC JOIN
dbo_evaluators2_Florida EV ON MC.customerID = EV.Customer_ID JOIN
rri_Offenders RRI ON
EV.Password = RRI.userpassword WHERE Cdate(RRI.testDate) BETWEEN #5/5/2005#
AND #5/5/2006# GROUP BY customerID) qRRI ON
dbo_mainCustomer_Florida.customerID = qRRI.customerID

Query works fine in Query Analyzer if I change linked table names to actuall
table names and CDate to CAST:

SELECT
mainCustomer.customerID,qDRI2.tDRI2,qDRISF.tDRISF,qRRI.tRRI,mainCustomer.agencyName,mainCustomer.state ,mainCustomer.availCredits
FROM mainCustomer INNER Join
(SELECT customerID,COUNT(*) tDRI2 FROM mainCustomer MC JOIN evaluators2 EV
ON MC.customerID = EV.Customer_ID JOIN dri2_Offenders DRI2 ON
EV.password = DRI2.userpassword WHERE Cast(DRI2.testDate AS datetime)
BETWEEN '5/5/2005' AND '5/5/2006' GROUP BY customerID) qDRI2 ON
mainCustomer.customerID = qDRI2.customerID LEFT OUTER JOIN
(SELECT customerID, COUNT(*) tDRISF FROM mainCustomer MC JOIN evaluators2 EV
ON MC.customerID = EV.Customer_ID JOIN drisf_Offenders DRISF ON
EV.Password = DRISF.userpassword WHERE Cast(DRISF.testDate AS datetime)
BETWEEN '5/5/2005' AND '5/5/2006' GROUP BY customerID) qDRISF ON
mainCustomer.customerID = qDRISF.customerID LEFT OUTER JOIN
(SELECT customerID, COUNT(*) tRRI FROM mainCustomer MC JOIN evaluators2 EV
ON MC.customerID = EV.Customer_ID JOIN rri_Offenders RRI ON
EV.Password = RRI.userpassword WHERE Cast(RRI.testDate AS datetime) BETWEEN
'5/5/2005' AND '5/5/2006' GROUP BY customerID) qRRI ON
mainCustomer.customerID = qRRI.customerID

What am I missing?

Deki PA
 
G

Gary Walter

First, if it works for you in Query Analyzer,
then save that SQL as a passthrough and enjoy
how it "snaps back" the results (if that can work
for you).

Else, Jet query joins typically are

INNER JOIN
LEFT JOIN
RIGHT JOIN

plus, here's a snip from an old post
from John Viescas:

****quote****
The SQL Standard allows for both bracketed and non-bracketed syntax, but
requires brackets when the ON clause doesn't immediately follow the JOIN
clause. A FROM clause must consist of a <table-expression>.

<snip>

Personally, I find it much clearer to enclose each pair of base tables
inside parentheses, treat that as one "logical table", and then add the next
table and JOIN-ON clause. So, I would write it like this:

((((a inner join b on a.x = b.x)
inner join c on a.x = c.x)
inner join d on a.x = d.x)
left join e on a.x = e.x)
left join f on a.x = f.x
***unquote*****

this might do you.....

SELECT
dbo_mainCustomer_Florida.customerID,
qDRI2.tDRI2,
qDRISF.tDRISF,
qRRI.tRRI,
dbo_mainCustomer_Florida.agencyName,
dbo_mainCustomer_Florida.state,
dbo_mainCustomer_Florida.availCredits
FROM
dbo_mainCustomer_Florida
INNER JOIN
[SELECT
customerID,
COUNT(*) tDRI2
FROM
((dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
dri2_Offenders DRI2
ON
EV.password = DRI2.userpassword)
WHERE
Cdate(DRI2.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qDRI2
ON
dbo_mainCustomer_Florida.customerID = qDRI2.customerID
LEFT JOIN
[SELECT
customerID,
COUNT(*) tDRISF
FROM
((dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
drisf_Offenders DRISF
ON
EV.Password = DRISF.userpassword)
WHERE
cdate(DRISF.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qDRISF
ON
dbo_mainCustomer_Florida.customerID = qDRISF.customerID
LEFT JOIN
[SELECT
customerID,
COUNT(*) tRRI
FROM
((dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
rri_Offenders RRI
ON
EV.Password = RRI.userpassword)
WHERE
Cdate(RRI.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qRRI
ON
dbo_mainCustomer_Florida.customerID = qRRI.customerID
 
G

Gary Walter

or maybe this:

SELECT
dbo_mainCustomer_Florida.customerID,
qDRI2.tDRI2,
qDRISF.tDRISF,
qRRI.tRRI,
dbo_mainCustomer_Florida.agencyName,
dbo_mainCustomer_Florida.state,
dbo_mainCustomer_Florida.availCredits
FROM
((dbo_mainCustomer_Florida
INNER JOIN
[SELECT
customerID,
COUNT(*) tDRI2
FROM
(dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
dri2_Offenders DRI2
ON
EV.password = DRI2.userpassword
WHERE
Cdate(DRI2.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qDRI2
ON
dbo_mainCustomer_Florida.customerID = qDRI2.customerID)
LEFT JOIN
[SELECT
customerID,
COUNT(*) tDRISF
FROM
(dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
drisf_Offenders DRISF
ON
EV.Password = DRISF.userpassword
WHERE
cdate(DRISF.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qDRISF
ON
dbo_mainCustomer_Florida.customerID = qDRISF.customerID)
LEFT JOIN
[SELECT
customerID,
COUNT(*) tRRI
FROM
(dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
rri_Offenders RRI
ON
EV.Password = RRI.userpassword
WHERE
Cdate(RRI.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qRRI
ON
dbo_mainCustomer_Florida.customerID = qRRI.customerID
 
G

Guest

Thanks Gary,
Both queries show me syntax error so I created passthrough query and it
works fine. Now my question is, is it possible to run stored procedure as
passthrough query with parametars? Basically, dates you see in my query are
supposed to be parametars and I have created stored procedure, but don't know
how to connect it to Access.
Here is stored procedure:

Create Procedure dbo.usp_qFlorida
@fromDate datetime
,@toDate datetime
As

SELECT
mainCustomer.customerID
,qDRI2.tDRI2
,qDRISF.tDRISF
,qRRI.tRRI
,mainCustomer.agencyName
,mainCustomer.state
,mainCustomer.availCredits
FROM
mainCustomer
INNER JOIN
(SELECT customerID,
COUNT(*) tDRI2
FROM mainCustomer MC
JOIN evaluators2 EV
ON MC.customerID = EV.Customer_ID
JOIN dri2_Offenders DRI2
ON EV.password = DRI2.userpassword
WHERE Cast(DRI2.testDate AS datetime)
BETWEEN @fromDate AND @toDate
GROUP BY customerID) qDRI2
ON mainCustomer.customerID = qDRI2.customerID
LEFT OUTER JOIN
(SELECT customerID, COUNT(*) tDRISF
FROM mainCustomer MC JOIN
evaluators2 EV ON MC.customerID = EV.Customer_ID JOIN
drisf_Offenders DRISF ON
EV.password = DRISF.userpassword
WHERE Cast(DRISF.testDate AS datetime)
BETWEEN @fromDate AND @toDate
GROUP BY customerID) qDRISF
ON mainCustomer.customerID = qDRISF.customerID
LEFT OUTER JOIN
(SELECT customerID, COUNT(*) tRRI
FROM mainCustomer MC JOIN
evaluators2 EV ON MC.customerID = EV.Customer_ID JOIN
rri_Offenders RRI ON
EV.password = RRI.userpassword
WHERE Cast(RRI.testDate AS datetime)
BETWEEN @fromDate AND @toDate
GROUP BY customerID) qRRI
ON mainCustomer.customerID = qRRI.customerID



--
Deki PA



Gary Walter said:
or maybe this:

SELECT
dbo_mainCustomer_Florida.customerID,
qDRI2.tDRI2,
qDRISF.tDRISF,
qRRI.tRRI,
dbo_mainCustomer_Florida.agencyName,
dbo_mainCustomer_Florida.state,
dbo_mainCustomer_Florida.availCredits
FROM
((dbo_mainCustomer_Florida
INNER JOIN
[SELECT
customerID,
COUNT(*) tDRI2
FROM
(dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
dri2_Offenders DRI2
ON
EV.password = DRI2.userpassword
WHERE
Cdate(DRI2.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qDRI2
ON
dbo_mainCustomer_Florida.customerID = qDRI2.customerID)
LEFT JOIN
[SELECT
customerID,
COUNT(*) tDRISF
FROM
(dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
drisf_Offenders DRISF
ON
EV.Password = DRISF.userpassword
WHERE
cdate(DRISF.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qDRISF
ON
dbo_mainCustomer_Florida.customerID = qDRISF.customerID)
LEFT JOIN
[SELECT
customerID,
COUNT(*) tRRI
FROM
(dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
rri_Offenders RRI
ON
EV.Password = RRI.userpassword
WHERE
Cdate(RRI.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qRRI
ON
dbo_mainCustomer_Florida.customerID = qRRI.customerID
Click to expand...
 
G

Gary Walter

Yes, you can dynamically create a pass-through query in code.
"sp_do_report" is the name of the stored procedure.
"qry_exec_sp" is the name of the pass-through query.
Manually create the pass-through query first with any old thing in it, since
you will create the exec statement below. Enter the correct info in the
"ODBC Connect String" property of the pass-through query.

Create a passthrough query that executes
the stored procedure with any parameter dates
(say "qryExecSP")

Execute usp_qFlorida '2006-01-01' '2006-02-01'

then, change SQL of this query in code
to insert new parameters (say from form textboxes)

(example using DAO)

Dim strSQL As String

strSQL = "Execute usp_qFlorida '" & Format(Me.txtFromDate,"yyyy-mm-dd") _
& "' '" & Format(Me.txtToDate, "yyyy-mm-dd") & "'"
CurrentDb.QueryDefs("qryExecSP").SQL = strSQL



Deki said:
Both queries show me syntax error so I created passthrough query and it
works fine. Now my question is, is it possible to run stored procedure as
passthrough query with parametars? Basically, dates you see in my query
are
supposed to be parametars and I have created stored procedure, but don't
know
how to connect it to Access.
Here is stored procedure:

Create Procedure dbo.usp_qFlorida
@fromDate datetime
,@toDate datetime
As

SELECT
mainCustomer.customerID
,qDRI2.tDRI2
,qDRISF.tDRISF
,qRRI.tRRI
,mainCustomer.agencyName
,mainCustomer.state
,mainCustomer.availCredits
FROM
mainCustomer
INNER JOIN
(SELECT customerID,
COUNT(*) tDRI2
FROM mainCustomer MC
JOIN evaluators2 EV
ON MC.customerID = EV.Customer_ID
JOIN dri2_Offenders DRI2
ON EV.password = DRI2.userpassword
WHERE Cast(DRI2.testDate AS datetime)
BETWEEN @fromDate AND @toDate
GROUP BY customerID) qDRI2
ON mainCustomer.customerID = qDRI2.customerID
LEFT OUTER JOIN
(SELECT customerID, COUNT(*) tDRISF
FROM mainCustomer MC JOIN
evaluators2 EV ON MC.customerID = EV.Customer_ID JOIN
drisf_Offenders DRISF ON
EV.password = DRISF.userpassword
WHERE Cast(DRISF.testDate AS datetime)
BETWEEN @fromDate AND @toDate
GROUP BY customerID) qDRISF
ON mainCustomer.customerID = qDRISF.customerID
LEFT OUTER JOIN
(SELECT customerID, COUNT(*) tRRI
FROM mainCustomer MC JOIN
evaluators2 EV ON MC.customerID = EV.Customer_ID JOIN
rri_Offenders RRI ON
EV.password = RRI.userpassword
WHERE Cast(RRI.testDate AS datetime)
BETWEEN @fromDate AND @toDate
GROUP BY customerID) qRRI
ON mainCustomer.customerID = qRRI.customerID



--
Deki PA



Gary Walter said:
or maybe this:

SELECT
dbo_mainCustomer_Florida.customerID,
qDRI2.tDRI2,
qDRISF.tDRISF,
qRRI.tRRI,
dbo_mainCustomer_Florida.agencyName,
dbo_mainCustomer_Florida.state,
dbo_mainCustomer_Florida.availCredits
FROM
((dbo_mainCustomer_Florida
INNER JOIN
[SELECT
customerID,
COUNT(*) tDRI2
FROM
(dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
dri2_Offenders DRI2
ON
EV.password = DRI2.userpassword
WHERE
Cdate(DRI2.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qDRI2
ON
dbo_mainCustomer_Florida.customerID = qDRI2.customerID)
LEFT JOIN
[SELECT
customerID,
COUNT(*) tDRISF
FROM
(dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
drisf_Offenders DRISF
ON
EV.Password = DRISF.userpassword
WHERE
cdate(DRISF.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qDRISF
ON
dbo_mainCustomer_Florida.customerID = qDRISF.customerID)
LEFT JOIN
[SELECT
customerID,
COUNT(*) tRRI
FROM
(dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
rri_Offenders RRI
ON
EV.Password = RRI.userpassword
WHERE
Cdate(RRI.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qRRI
ON
dbo_mainCustomer_Florida.customerID = qRRI.customerID
Click to expand...
Click to expand...
 
G

Gary Walter

hope that wasn't confusing......
was going to paste in a reply,
but decided to type out instead
and forgot to delete first paragraph.
sorry..


Create a passthrough query that executes
the stored procedure with any parameter dates
(say "qryExecSP")

Execute usp_qFlorida '2006-01-01', '2006-02-01'

then, change SQL of this query in code
to insert new parameters (say from form textboxes)

(example using DAO)

Dim strSQL As String

strSQL = "Execute usp_qFlorida '" & Format(Me.txtFromDate,"yyyy-mm-dd") _
& "', '" & Format(Me.txtToDate, "yyyy-mm-dd") & "'"
CurrentDb.QueryDefs("qryExecSP").SQL = strSQL
Deki said:
Both queries show me syntax error so I created passthrough query and it
works fine. Now my question is, is it possible to run stored procedure as
passthrough query with parametars? Basically, dates you see in my query
are
supposed to be parametars and I have created stored procedure, but don't
know
how to connect it to Access.
Here is stored procedure:

Create Procedure dbo.usp_qFlorida
@fromDate datetime
,@toDate datetime
As

SELECT
mainCustomer.customerID
,qDRI2.tDRI2
,qDRISF.tDRISF
,qRRI.tRRI
,mainCustomer.agencyName
,mainCustomer.state
,mainCustomer.availCredits
FROM
mainCustomer
INNER JOIN
(SELECT customerID,
COUNT(*) tDRI2
FROM mainCustomer MC
JOIN evaluators2 EV
ON MC.customerID = EV.Customer_ID
JOIN dri2_Offenders DRI2
ON EV.password = DRI2.userpassword
WHERE Cast(DRI2.testDate AS datetime)
BETWEEN @fromDate AND @toDate
GROUP BY customerID) qDRI2
ON mainCustomer.customerID = qDRI2.customerID
LEFT OUTER JOIN
(SELECT customerID, COUNT(*) tDRISF
FROM mainCustomer MC JOIN
evaluators2 EV ON MC.customerID = EV.Customer_ID JOIN
drisf_Offenders DRISF ON
EV.password = DRISF.userpassword
WHERE Cast(DRISF.testDate AS datetime)
BETWEEN @fromDate AND @toDate
GROUP BY customerID) qDRISF
ON mainCustomer.customerID = qDRISF.customerID
LEFT OUTER JOIN
(SELECT customerID, COUNT(*) tRRI
FROM mainCustomer MC JOIN
evaluators2 EV ON MC.customerID = EV.Customer_ID JOIN
rri_Offenders RRI ON
EV.password = RRI.userpassword
WHERE Cast(RRI.testDate AS datetime)
BETWEEN @fromDate AND @toDate
GROUP BY customerID) qRRI
ON mainCustomer.customerID = qRRI.customerID



--
Deki PA



Gary Walter said:
or maybe this:

SELECT
dbo_mainCustomer_Florida.customerID,
qDRI2.tDRI2,
qDRISF.tDRISF,
qRRI.tRRI,
dbo_mainCustomer_Florida.agencyName,
dbo_mainCustomer_Florida.state,
dbo_mainCustomer_Florida.availCredits
FROM
((dbo_mainCustomer_Florida
INNER JOIN
[SELECT
customerID,
COUNT(*) tDRI2
FROM
(dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
dri2_Offenders DRI2
ON
EV.password = DRI2.userpassword
WHERE
Cdate(DRI2.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qDRI2
ON
dbo_mainCustomer_Florida.customerID = qDRI2.customerID)
LEFT JOIN
[SELECT
customerID,
COUNT(*) tDRISF
FROM
(dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
drisf_Offenders DRISF
ON
EV.Password = DRISF.userpassword
WHERE
cdate(DRISF.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qDRISF
ON
dbo_mainCustomer_Florida.customerID = qDRISF.customerID)
LEFT JOIN
[SELECT
customerID,
COUNT(*) tRRI
FROM
(dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
rri_Offenders RRI
ON
EV.Password = RRI.userpassword
WHERE
Cdate(RRI.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qRRI
ON
dbo_mainCustomer_Florida.customerID = qRRI.customerID
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

It was a little bit, but I got it. Thanks! I think I'm gonna redo all queries
into stored procedures. Thanks again!
--
Deki PA



Gary Walter said:
hope that wasn't confusing......
was going to paste in a reply,
but decided to type out instead
and forgot to delete first paragraph.
sorry..


Create a passthrough query that executes
the stored procedure with any parameter dates
(say "qryExecSP")

Execute usp_qFlorida '2006-01-01', '2006-02-01'

then, change SQL of this query in code
to insert new parameters (say from form textboxes)

(example using DAO)

Dim strSQL As String

strSQL = "Execute usp_qFlorida '" & Format(Me.txtFromDate,"yyyy-mm-dd") _
& "', '" & Format(Me.txtToDate, "yyyy-mm-dd") & "'"
CurrentDb.QueryDefs("qryExecSP").SQL = strSQL
Deki said:
Both queries show me syntax error so I created passthrough query and it
works fine. Now my question is, is it possible to run stored procedure as
passthrough query with parametars? Basically, dates you see in my query
are
supposed to be parametars and I have created stored procedure, but don't
know
how to connect it to Access.
Here is stored procedure:

Create Procedure dbo.usp_qFlorida
@fromDate datetime
,@toDate datetime
As

SELECT
mainCustomer.customerID
,qDRI2.tDRI2
,qDRISF.tDRISF
,qRRI.tRRI
,mainCustomer.agencyName
,mainCustomer.state
,mainCustomer.availCredits
FROM
mainCustomer
INNER JOIN
(SELECT customerID,
COUNT(*) tDRI2
FROM mainCustomer MC
JOIN evaluators2 EV
ON MC.customerID = EV.Customer_ID
JOIN dri2_Offenders DRI2
ON EV.password = DRI2.userpassword
WHERE Cast(DRI2.testDate AS datetime)
BETWEEN @fromDate AND @toDate
GROUP BY customerID) qDRI2
ON mainCustomer.customerID = qDRI2.customerID
LEFT OUTER JOIN
(SELECT customerID, COUNT(*) tDRISF
FROM mainCustomer MC JOIN
evaluators2 EV ON MC.customerID = EV.Customer_ID JOIN
drisf_Offenders DRISF ON
EV.password = DRISF.userpassword
WHERE Cast(DRISF.testDate AS datetime)
BETWEEN @fromDate AND @toDate
GROUP BY customerID) qDRISF
ON mainCustomer.customerID = qDRISF.customerID
LEFT OUTER JOIN
(SELECT customerID, COUNT(*) tRRI
FROM mainCustomer MC JOIN
evaluators2 EV ON MC.customerID = EV.Customer_ID JOIN
rri_Offenders RRI ON
EV.password = RRI.userpassword
WHERE Cast(RRI.testDate AS datetime)
BETWEEN @fromDate AND @toDate
GROUP BY customerID) qRRI
ON mainCustomer.customerID = qRRI.customerID



--
Deki PA



:

or maybe this:

SELECT
dbo_mainCustomer_Florida.customerID,
qDRI2.tDRI2,
qDRISF.tDRISF,
qRRI.tRRI,
dbo_mainCustomer_Florida.agencyName,
dbo_mainCustomer_Florida.state,
dbo_mainCustomer_Florida.availCredits
FROM
((dbo_mainCustomer_Florida
INNER JOIN
[SELECT
customerID,
COUNT(*) tDRI2
FROM
(dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
dri2_Offenders DRI2
ON
EV.password = DRI2.userpassword
WHERE
Cdate(DRI2.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qDRI2
ON
dbo_mainCustomer_Florida.customerID = qDRI2.customerID)
LEFT JOIN
[SELECT
customerID,
COUNT(*) tDRISF
FROM
(dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
drisf_Offenders DRISF
ON
EV.Password = DRISF.userpassword
WHERE
cdate(DRISF.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qDRISF
ON
dbo_mainCustomer_Florida.customerID = qDRISF.customerID)
LEFT JOIN
[SELECT
customerID,
COUNT(*) tRRI
FROM
(dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
rri_Offenders RRI
ON
EV.Password = RRI.userpassword
WHERE
Cdate(RRI.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qRRI
ON
dbo_mainCustomer_Florida.customerID = qRRI.customerID
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Syntax error (missing operator) in query expression ‘tblDUES.’. 5
Syntax error (missing operator) in expression 2
3-Way Query: Syntax Error (missing operator) in query expression 4
3075 Syntax error (missing operator) in query expression 2
3075: Syntax error (missing operator) in query expression. 5
Syntax error (missing operator) in Query Expression bug 5
UPDATE - missing operator in expression 18
Syntax error (missing operator) in query expression message 1

Top