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;
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:
What is [total suma]? It is not in your FROM clause.
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?
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?
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?