Updating a form based on a query using the "total" feature

  • Thread starter Thread starter James
  • Start date Start date
J

James

I have set up an ACCESS database of journal articles.
Each article has one or more authors. I have created a
form listing the article title, the name of the journal
etc. Included is a subform listing each of the authors.
The authors are listed in the order that their names
appear in the article. I wrote a query to sort the
articles alphabetically according the name of the first
author (in the subform). The main form is based on this
query so that all the articles are sorted according to
the name of the first author. However, the query uses
the "First" feature in the "Total" row on the query. The
use of this feature precludes me from updating
information in the form that is based on the query. Is
there a way around this problem and if so what is it?
 
Hi,


Any aggregate make the query not updateable, you have to push it in a
sub-query in the WHERE clause, ugly.


SELECT LAST(b.pk), LAST(b.whatever)
FROM a INNER JOIN b ON ...


is not updateable, but


SELECT a.*, b.*
FROM a INNER JOIN b ON ...
WHERE b.pk IN (SELECT LAST(b.pk)
FROM a INNER JOIN b ON ... )


is (should), as long as a and b are, individually.


Now, if you also have a GROUP BY clause, you probably have, that is worse,
uglier, sine the IN won't do, and an EXISTS, a complex one, would be
required. Much preferable is to make a temporary table out of your total
query, use that temp table to update other stuff. Delete the temp table once
done.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top