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

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Michel Walsh (vanderghast_at_VirusAreFunnierThanSpam)
Date: 08/27/04 

Date: Fri, 27 Aug 2004 08:29:13 -0400

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

"James" <anonymous@discussions.microsoft.com> wrote in message
news:c33b01c489db$ee7dee10$a401280a@phx.gbl...
>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?

Relevant Pages

  • Re: update query: still having problems
    ... I will have to adapt the other query. ... So I will need the inner join. ... UPDATE Department INNER JOIN Sheet1 ON Department.employeeID = ... If this is what you want, fine, else, add a WHERE clause to ...
    (microsoft.public.access.queries)
  • Re: Sum based on date range
    ... FROM dbo_EMPLOYEE INNER JOIN dbo_EMP_ABSENCE ON ... And I am not sure why the Where clause has three date references in it when I am trying to get today as one date and the date 6 months ago as the other date. ... You can then try to switch back to design view or try to run the query. ... I need to be able to query by EMP_ID total number SEVERITY over the last ...
    (microsoft.public.access.queries)
  • Re: SQL to Linq - Left, Right and Inner Joins
    ... if the where clause is on an indexed column I'd ... be *hugely* surprised if the inner join were fully evaluated by SQL ... select distinct c.* from customers c inner join orders o ... The second query has less reads than the first query. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: update query: still having problems
    ... new query, BUT using the one that works right now). ... UPDATE Department INNER JOIN Sheet1 ON Department.employeeID = ... If this is what you want, fine, else, add a WHERE clause to limit ... SELECT Department.*, sheet1.* ...
    (microsoft.public.access.queries)
  • Re: SQL to Linq - Left, Right and Inner Joins
    ... I'd certainly hope that the query optimiser would do the job there though. ... if the where clause is on an indexed column I'd be *hugely* surprised if the inner join were fully evaluated by SQL server before looking at the index. ... select distinct c.* from customers c inner join orders o ...
    (microsoft.public.dotnet.languages.csharp)