Re: Selecting records using criteria as arguments for a mathematical operation

Jeffrey Davis wrote:
On Dec 3, 8:06 pm, Tom van Stiphout <no.spam.tom7...@xxxxxxx> wrote:

On Sun, 2 Dec 2007 20:15:53 -0800 (PST), Jeffrey Davis

<jpm1...@xxxxxxxxx> wrote:

Show us your query and the data types of the fields involved. The
error message seems to indicate you're doing something that violates
the data type rules. E.g. Sum(FirstName) would trigger this error, or
"...where FirstName=Tom" (because the quotes are missing).

To start your application, set a startup form, or create an AutoExec
macro.

-Tom.

<clip>





Ok ,,, I've done the above ... not perfect but workable (still haven't
figured out how to create the macro) ,,,

but I'm trying to build a query that calculates the sum of a number of
records selected by the meeting the value of another field.

I keep getting type mismatches when I try to insert the criterion.

If I better specified (eg by specifying the fuill field and table name
in the correct syntax), would this work?

TIA

JD- Hide quoted text -

- Show quoted text -


I've just used the standard query app..

When it's run, it accurately extracts the relevant records with the
sales figures displayed for each of them.

SELECT package.Type, farms.Location, farms.farmID, farms.State,
farms.Sales
FROM farms INNER JOIN (coopMember INNER JOIN (package INNER JOIN
agreements ON package.Type = agreements.package) ON
coopMember.coopMemberID = agreements.coopMember) ON farms.farmID =
agreements.Restaurant
GROUP BY package.Type, farms.Location, farms.farmID, farms.State,
farms.Sales
HAVING (((package.Type)="p1"))

I tried to write some script to sum the three extracted records (i.e
all those with p1 as the value of the package type) in a new column,
but no dice.

In the Totals row of the query builder (where you see all those GroupBys), have you considered changing the Sales column from GroupBy to Sum?



In Excel, this would be fairly easy as you'd simply write an IF
referenced to the column with the p values specifying p1 and then sum
them at the bottom of the column, but in Access ...

Thanks.

JD





.

Relevant Pages

  • Re: Selecting records using criteria as arguments for a mathematical operation
    ... Show us your query and the data types of the fields involved. ... error message seems to indicate you're doing something that violates ... FROM farms INNER JOIN (coopMember INNER JOIN (package INNER JOIN ...
    (comp.databases.ms-access)
  • Re: Selecting records using criteria as arguments for a mathematical operation
    ... Show us your query and the data types of the fields involved. ... error message seems to indicate you're doing something that violates ... If I better specified (eg by specifying the fuill field and table name ...
    (comp.databases.ms-access)
  • Re: Acumulated Value
    ... An Error Message ... Try breaking your query down into parts and see where it breaks down. ... Save the above as qIndexBase. ... But I get SUM. ...
    (microsoft.public.access.queries)
  • Re: 1-year sum question
    ... Thanks once again for the your help Crystal, I am having a little trouble ... I am getting an error message before the query runs. ... My running sum column in the query is built with an SQL inner join query. ...
    (microsoft.public.access.queries)
  • Summing fields in two ways
    ... Code DateWeight ... Now I would like to make a query, in which I can sum ip ... The error message was: ...
    (microsoft.public.sqlserver.odbc)