Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    3

    Unanswered:

    How to perform Set operation Minus in Access? I want to subtract a sum from many fields from a number in another table
    This is the query which i wrote and after i execute it ask me to "enter a parameter value"...but the value already exists in the field of table.

    "SELECT [date personale].Nume_prenume, [plati cotizatii].An_stag, [total suma].sumatotal - sum([plati cotizatii].suma_chitcotiz) AS restante
    FROM [date personale] INNER JOIN [plati cotizatii] ON [date personale].CNP=[plati cotizatii].CNP
    GROUP BY [date personale].Nume_prenume, [plati cotizatii].An_stag;
    "

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi balas - welcome to the forum

    A few things of interest:
    #1 - this doesn't sound like a minus set operation to me. Sounds like a mathematical subtraction.
    #2 - this is an old and, by the looks of it, b0rked thread. I would advise you start a brand spanking new one
    #3 - The ... after the "enter parameter value" is the useful bit of the error message! It is best to include that bit
    #4 - I think this is your problem:
    [total suma].
    What is [total suma]? It is not in your FROM clause.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2006
    Posts
    3
    This is my problem:3 tables: -name (nameid,name),
    -taxes (taxesid,sum, nameid,sumid )
    -totalsum (sumid, totalsum)
    SELECT [name].name, [totalsum].totalsum - sum([taxes].taxes) AS restante
    FROM [taxes],[totalsum], [name]
    where [taxes].name=[name].name and [totalsum].taxesid=[taxes].taxesid
    GROUP BY [name].Nume_prenume

    and the error is:
    you tried to execute a query that does not include the specified _expression:"totalsum - sum([taxes].taxes)" as part of an aggregate function.
    Can someone help me?
    Thanks!

  4. #4
    Join Date
    Aug 2006
    Posts
    3
    I thing the problem is that Access don't recognize "-" as a function.I know that there is a function like "except", but i don't know how to use it and if access accept it like function.Do you know another one?

    Thanks!

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That isn't the query you posted originally....

    You have new problems with this query. BTW - Access does recognise "-". I think that you chose a very poor thread to continue. The mathematical minus is totally and completely different to the Set Operation Minus\ Except hence my suggestion you started a new thread. Your problem is not related to the subject of this thread.

    #1 - I would recomend you use ANSI joins (as per your first query). You are creating cartesian products and then filtering on the where clause. This is old fashioned.
    #2 - If you use a group by clause you must either perform an aggregate function or goup on every column in your SELECT clause. Does this help you spot the error?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •