Dear Christopher:
Maybe it's just me, but when I try to write rather complex queries,
getting nasty reactions from Jet is not news. Nearly 3 years ago I
had accumulated a number of queries that I had re-written and
re-written before I could get them to work.
I had just about started to believe I really didn't know how to write
queries. Then I switched to MSDE. Suddenly, everything I tried to do
worked.
Now, I have found bugs in MSDE, but I'd have to say they are very rare
compared to Jet. Also, if you find a bug in MSDE and report it, it
gets fixed, and fairly quickly.
Not so with Jet. That's sad.
Now, the switch from Jet to MSDE is not painless. There's a lot of
learning to do, and conversions to be made. The switch needs careful
analysis. Back when I made the switch, I was getting very desparate.
So, for me, the change was very beneficial.
If your query is run from an MDB and is not "pass through" then it is
run in Jet, even though the data is gathered from some other database
engine. If you can write queries for this "other engine" you might
try pass through.
Along with moving to MSDE, I've also long ago dropped using parameter
queries. Wouldn't it be better to have a combo box listing all the
Reviewers, and have the user pick one? Otherwise, the user will
misspell the name and complain it didn't work! Or, if it's misspelled
in the Reviewers table, they'll spell it correctly and compain about
that. Since you seem to be maturing in your use of Access, I strongly
recommend making this change. You can then reference the control in a
Jet query (but not with MSDE or pass through). But the universal
solution is to write the SQL in code, reading the value from the
control, and then send that as the query.
You cannot reference the AverageScore alias in an ORDER BY, but you
can place the entire subquery in the ORDER BY and it should work
(assuming it works in the SELECT clause, which ORDER BY did).
Tom; Thanks very much! I didn't have a Reviewers table, but created one.
As you suspected, the final query you provide prompts for parameter
A.ApplicantID; presumably, that's Jet missing the point? Unwrapping 'A' to
'Applicants' doesn't help.
The full query, with error, also crashes Access (2002 sp2) every time I
accidentally flip into Design View instead of SQL View: is this something MS
would like to know about, or old news? When I remove the COUNT(*) Select
expression, the query behaves fine.
This query is being fed through a proprietary web-database interface wrapper
into Access. I don't have access to the guts of the wrapper. I would guess
it uses Jet, from your comment and the error above, but if there's a way for
me to find out for certain via an SQL query or queries, let me know.
Whatever it is, it's my only option: I have no control over the interface,
and can only submit SQL to it.
Now that I see how this works I've got two additional questions:
--how *do* I turn this into a parameter query? I've tried using the syntax
[... WHERE T2.ReviewerName Like "*" & [Enter Reviewer Name] & "*" ... ],
which works fine for simpler queries, but fails with this one, giving a
"Syntax Error in FROM clause". The parameter has to be embedded in the SQL
somehow, so I can pass it through the web interface.
--Is it possible to order the results by the AverageScore? My novice
approaches produce errors: Access won't recognize "AverageScore" in the
ORDER BY clause; and I've also tried replicating the entire SELECT
expression for AvgScore after ORDER BY, but Access doesn't seem to like that
either. They want this query descending-ordered by score. Actually, they
want it [ORDER BY ReviewerCount DESC, AverageScore DESC ], if I can get
COUNT(*) working.
Thanks again. If you can't spare the time for these followups, I'm still
very grateful to you for getting me past the first roadblock.
--Chris
Dear Christopher:
I have to assume you have a table Reviewers with at least these
columns: ReviewerID and ReviewerName.
First you need a query that reduces Reviews to those by a certain
name. I'd suggest this:
SELECT T1.ApplicantID
FROM Reviews T1
INNER JOIN Reviewers T2 ON T2.ReviewerID = T1.ReviewerID
WHERE T2.ReviewerName = 'jsmith'
Then we can build the required query around this:
SELECT A.ApplicantID
FROM Applicants A
LEFT JOIN (SELECT T1.ApplicantID
FROM Reviews T1
INNER JOIN Reviewers T2 ON T2.ReviewerID = T1.ReviewerID
WHERE T2.ReviewerName = 'jsmith') R
ON R.ApplicantID = A.ApplicantID
WHERE R.ApplicantID IS NULL
The LEFT JOIN shows all rows from Applicants but only those rows from
the subquery (the first query we wrote above and stuffed into the
finished version). When there is NO row in this subquery meeting the
specification of the LEFT JOIN then the values of ALL the columns
produced by that subquery will be NULL. So, the final step in the
query is to choose only those where this column (ApplicantID) is null
from the subquery.
I have provided no facility here for you to vary the value of the
ReviewerName. It could be a parameter, a control from a form, or you
could change it dynamically in coding to create the SQL string.
Now on to adding the other requirements.
SELECT A.ApplicantID,
(SELECT COUNT(*)
FROM (SELECT DISTINCT ReviewerID
FROM Reviews R
WHERE R.ApplicantID = A.ApplicantID)) AS ReviewerCount,
(SELECT Avg(Score)
FROM Reviews R
WHERE R.ApplicantID = A.ApplicantID) AS AverageScore
FROM Applicants A
LEFT JOIN (SELECT T1.ApplicantID
FROM Reviews T1
INNER JOIN Reviewers T2 ON T2.ReviewerID = T1.ReviewerID
WHERE T2.ReviewerName = 'jsmith') R
ON R.ApplicantID = A.ApplicantID
WHERE R.ApplicantID IS NULL
I'm a bit worried about the subquery for ReviewerCount I wrote above.
If you're using Jet and not MSDE then it doesn't usually allow for the
reference "WHERE R.ApplicantID = A.ApplicantID" because A.ApplicantID
is a reference two levels of subquery above the current subquery. But
I felt you would need the DISTINCT subquery since you want "a count of
the reviewers" which I took to mean that 2 reviews by the same
reviewer count as only 1 here, not 2.
If you are using MSDE, you won't have this problem. The technique I
used is perfectly good SQL, but Jet usually doesn't handle it. That's
one of the reasons I got away from using Jet for my projects. <sigh>
It just always seemed that I had to dream up some complex way around
the stuff Jet doesn't do rather than just being able to write straight
forward code and expect it to work.
So, if you have this problem, please tell me how you are using the
query. I can see a couple of ways to handle this, but need some
guidance from you first. Is this for a report, or what?
On Wed, 8 Oct 2003 01:33:50 -0400, "Christopher Mackie"
I've been working with Access trying to build an SQL query, and can't
manage
the syntax. Any wiser heads out there?
Two tables: Applicants, and Reviews. Many Reviews to one Applicant.
Linked fields are Applicants.ApplicantID and Reviews.ApplicantID.
Applicants has field FullName. Reviews has fields ReviewerID and
EvalScore
(numeric).
I need to display the records in Applicants for which there are *no*
records
in Reviews corresponding to a particular ReviewerID (say, 'jsmith'). For
each applicant meeting this criterion, the query also has to display:
--a count of the reviewers who *have* completed a review for that
applicant
--the average EvalScore of all completed reviews for the applicant
Tx for any assistance, --Chris
Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
