Re: Getting latest count quantities

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

From: Michel Walsh (vanderghast_at_VirusAreFunnierThanSpam)
Date: 04/05/04 

Date: Mon, 5 Apr 2004 06:20:21 -0400

Hi,

        You are missing a GROUP BY clause and/or aggregates. You probably
miss:
GROUP BY tblResources.ResourceName
but there is still the problem with tblStockTake.Quantity

Take a look at http://www.mvps.org/access/queries/qry0020.htm

    In a Total query, each field in the SELECT clause must be aggregated
(MIN, MAX, COUNT, SUM, FIRST, LAST, ... ) or appear in the GROUP BY clause.

Hoping it may help,
Vanderghast, Access MVP

"Mark" <mark_goode@wsahs.nsw.gov.au> wrote in message
news:vst170hl9gkfrsavff1tbierqhcl827v8e@4ax.com...
> Hello All,
> I'm trying to get the latest stocktake quantity and the latest
> stocktake date from my tables.
> Where am I going wrong?
> Thanks,
> Mark
> The below is from the SQL view of my query
> SELECT tblResources.ResourceName, Max([tblStockTake].[StockTakeDate])
> AS CountDate, tblStockTake.Quantity
> FROM tblResources INNER JOIN tblStockTake ON tblResources.ResourceID =
> tblStockTake.ResourceID;
>

Relevant Pages

  • Re: Cant get Access to run a query with a time field
    ... Since the user enters an exact value, why isn't it saved to the ... Second, a date time value is a floating point value, ... >> Vanderghast, Access MVP ...
    (microsoft.public.access.queries)
  • Re: Another variation
    ... >> Vanderghast, Access MVP ... > The documentation may need some maintenance. ... I couldn't locate the Jet4.0 SQL reference on ...
    (microsoft.public.access.queries)
  • Re: Display list of files in a folder on a hard drive in a form
    ... Dim str As String ... >> Vanderghast, Access MVP ... >>> then have it display all of the files located in the folder on a form. ...
    (microsoft.public.access.formscoding)
  • Re: lookup
    ... graphical capabilities of the designer. ... >> FROM myOriginalTable LEFT JOIN QuotesCodes ... >> Vanderghast, Access MVP ...
    (microsoft.public.access.queries)
  • Re: Finding the end of a recordset
    ... >> That error is generally due to some invalid data in the record to be ... >> Vanderghast, Access MVP ... >> they hit delete, ...
    (microsoft.public.access.queries)