SUBQUERY WOES

[BillA]

Hello:
I have a Query that is used to support a read-only continuous form.
I am trying to include a Comments section in this form where only the most
recent comment is shown.
It was suggested through this forum to develop a SUBQUERY within the main
query to provide the data I seek.

I’ve been having difficulty with this subquery and hope to get a little help.
This is what I am working with in creating my subquery:

Tables
lk_Reviews tbl_Reviewer
ReviewID (pk) ReviewerID (pk)
ProjectID (fk) ...
CommentDate LastName
Comments (etc...)
ReviewerID (fk)

(ProjectID foreign key is included in the main query)

I would like to include these fields in my subquery:
lk_Reviews.CommentDate
tbl_Reviewer.LastName
lk_Reviews.Comments

This is what I have thus far… and it’s not working:
Comment: (SELECT lk_Reviews.CommentDate, tbl_reviewer.LastName,
lk_Reviews.Comments FROM tbl_reviewer RIGHT JOIN lk_Reviews ON
tbl_reviewer.ReviewerID = lk_Reviews.ReviewerID WHERE lk_Reviews.ProjectID =
tbl_Project.ProjectID ORDER BY lk_Reviews.CommentDate DESC,
tbl_review.ReviewID)

I am getting this error message:
“You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise the
SELECT statement of the subquery to request only one field. (Error 3306)â€

I’m not even sure I’ve built a proper subquery for my needs; this error put
me over the edge. Any advice or help would be greatly appreciated.

Bill

 

[Michel Walsh]

Indeed, a subquery in the SELECT clause must return one field, and one
record.


While some people prefers subquery, I prefer joins, when the syntax is easy,
cause they tend to be faster in execution (less I/O).

If there is at most one comment per reviewer, a simple inner join seems all
what is needed.

If there is more than one comment and desired only the latest one, *maybe*
it is faster with a subquery, like:


SELECT a.*, (SELECT TOP 1 comments FROM lk_reviews WHERE
ReviewerID=a.ReviewerID ORDER BY ? , ReviewID DESC )
FROM tbl_reviewer AS a


and you have to replace the ? by the field that supply the date_time stamp
where the comment has been done (else, how can we determine the latest one ?
maybe you count on ReviewID to be ever increasing ? if so, you can use only
that field, which happens to be the primary key).


Since ReviewID is the primary key, the ORDER BY is unique, without any
ex-equo, so the TOP 1 is also unique and the sub query return just one value
(comment) and just one row (TOP 1 on a unique ordering).


Hoping it may help,
Vanderghast, Access MVP

 

[BillA]

Hello Michel:
Thank you for your assistance. Your second scenario is closer to what I'm
trying to do; comments are as long as a sentence. The "lk_Reviews.Comments"
is a memo, and the "lk_Reviews.Date" will be used as the 'date_time' field.
I tried using your suggestion in my query and it failed. Although I tried
various methods to get it to work, ultimately I'm not understanding some of
your code, specifically "SELECT a.*," and "AS a."

Don't know if it matters, but lk_Reviews is a joining table between
tbl_Project and tbl_Reviewer.

This is my first time trying to write SQL, it may as well be my last.
Thank you for your time in helping me build this subquery.
Bill

 

[Michel Walsh]

in

FROM tbl_reviewer AS a


that means that if I use 'a' where a table name is intended, then that is
a reference to the mentioned table, tbl_reviewer. HERE, is just make the
name faster to type (it can have other uses, too, on the other hand, but it
is not used here). So, SELECT a.* means take all the columns form
tbl_reviewer.



SELECT a.*,
(SELECT TOP 1 b.comments
FROM lk_reviews AS b
WHERE b.ReviewerID=a.ReviewerID
ORDER BY b.Date, b.ReviewID DESC )
FROM tbl_reviewer AS a





note that in :

WHERE b.ReviewerID=a.ReviewerID

it simply means that ReviewerID from table lk_reviews should match the
value of ReviewerID from table tbl_reviewer, So, the inner most sub-query
take all the reviews of a given reviewer, a.ReviewerID, order them in
decreasing order of the date (and of the primary key to break cases of
equality), and only keep the first row, pumping the field b.comments out of
it as single result getting out of the sub-query.


Instead of a.*

in the SELECT clause, you can explicitly list all the fields you need, from
table tbl_reviewer (only, because the other table, 'b', is 'out of scope'),
at that point.


Now, since comments is a MEMO, that MAY create some problem, indeed. MEMO
are not easily moved around, being too ... fat ... to move, kind of. I
tested on standard not-memo field, and the construction works. If the memo
is the problem, you can always pump the primary key associated to it, rather
that itself:


SELECT a.*,
(SELECT TOP 1 b.ReviewID
FROM lk_reviews AS b
WHERE b.ReviewerID=a.ReviewerID
ORDER BY b.Date, b.ReviewID DESC )
FROM tbl_reviewer AS a


and them, later, use a lookup on that value to retrieve the memo associated
to it.




Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Can you post the SQL code (from the SQL view) ?


Vanderghast, Access MVP

 

[BillA]

Hello Michel:

After failing to get your code to function, I went back to the drawing
board. Your discussion regarding my memo field led me to change this field
to a ‘text’ field set to 75 characters.
When I changed the memo field to text – I changed 2 field names:
CommentDate became IssueDate
Comments became Issue.

As mentioned in my last thread, I received an “EXISTS†error when I tried to
run this (note my feeble attempt to get your code to get it running):

Comment: (SELECT a.*, (SELECT TOP 1 b.ReviewID FROM
lk_reviews AS b WHERE b.ReviewerID=a.ReviewerID ORDER BY
b.Date, b.ReviewID DESC ) FROM lk_reviews AS a)

I was partially successful with new code, yet I don’t have all the fields I
need in my Form's comment section. I am only able to include a single field;
I would like to include tbl_Reviewers.LastName and lk_Reviews.IssueDate (note
2 tables). When I attempt code with these additional fields I get the same
“EXISTS†error.

Although this doesn’t have all the fields I need, this did work:
Issue: (SELECT TOP 1 Issue FROM lk_Reviews WHERE lk_Reviews.ProjectID =
tbl_Project.ProjectID ORDER BY lk_Reviews.IssueDate DESC)

I think I’m close, but still learning.
Again, thank you for your interest and assistance.
__________________________________________________________________

 

[Michel Walsh]

I think I see a part of the problem. I was supplying, tentatively, the whole
query, in am SQL view, not just one single field, in the graphical designer.
Your last tentative was indeed, the solution to use, in the case you use the
graphical editor.


Sorry for the initial confusion.

You should add the primary key, in the order by:

Issue: (SELECT TOP 1 Issue FROM lk_Reviews WHERE lk_Reviews.ProjectID =
tbl_Project.ProjectID ORDER BY lk_Reviews.IssueDate DESC, ReviewsID )



that will make sure there is just ONE row returned (TOP n return ALL the
ex-equo, in position n, if there are many candidates for being the n-th
record; adding the primary key, that will arbitrary make just one of these
the real n-th record, no ex-equo).






Vanderghast, Access MVP

 

[BillA]

Michel:
It works! Thank you for pointing me in the right direction.
One more question; can you tell me how to incorporate 2 additional fields:
tbl_Reviewers.LastName and lk_Reviews.IssueDate (note different tables). I
keep stumbling on what, I believe, shouldn't be so difficult. Each time I
attempt to add these fields I get the "EXISTS" error that has been my nemesis.

Thank you again for your time and advice.
Bill

 

[Michel Walsh]

IF these fields can be tracked through the ReviewsID value...


bring the table(s) having the information, and drop, in the grid, the field
that has to match de primary key of the reviewID, and, in the criteria
column, add the criteria:

= (SELECT TOP 1 ReviewsID FROM lk_Reviews WHERE lk_Reviews.ProjectID =
tbl_Project.ProjectID ORDER BY lk_Reviews.IssueDate DESC, ReviewsID )


Once that is done, pump the data you need from the said table you just
added, by dragging the required fields into the grid.



Hoping it may help,
Vanderghast, Access MVP