SQL error message?!?

[ItIsHardToProgram]

SELECT ProjetBaseDon.[# Projet], [Valeur des honoraires]-[Total] AS [honoraires Restant]
FROM HPP, ProjetBaseDon
GROUP BY ProjetBaseDon.[# Projet];


As soon as I try grouping the entrys by Project numbers it gives me the fallowing error msg:

The function "[Valeur des honoraires]-[Total]" is not part of the agregate function.....

Any one knows why, should I instead do a inner join?!?

 

[JerryKlmns]

?
GROUP BY ProjetBaseDon.[# Projet], [Valeur des honoraires]-[Total]

Do they join or you want a cross product of those two tables?

 

[Golom]

If, for example you had
[tt]
# Projet honoraires Restant
1 25
1 27
1 33
[/tt]
SQL, because of the GROUP BY will report only one value for # Projet (1 in this case) but it doesn't know which of the values of honoraires Restant to report. You could do something like

SELECT ProjetBaseDon.[# Projet], 
       [COLOR=red]MAX([/color][Valeur des honoraires]-[Total][COLOR=red])[/color] AS [honoraires Restant]
FROM HPP, ProjetBaseDon
GROUP BY ProjetBaseDon.[# Projet];

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[ItIsHardToProgram]

I found a way by grouping the entrys in another query And then using that other query with a INNER JOIN.

If any one knows any better way that dosnt involve another query, plz go ahead and mention it.

 

[LNBruno]

What is HPP in the FROM clause? I don't see it referenced anywhere else in the SQL.

< M!ke >

 

[Golom]

If any one knows any better way ...

Not without seeing your code. Most queries can be written using in-line sub-queries but there are too many ways of doing that to provide a generalized prescription for it.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[ItIsHardToProgram]

HPP is actualy The table where TOTAL comes from.

I think I might get what you meant Golom. any way it works now...

another SQL problem... I was wondering if there was a way to WRITE in a table the values from another table in a QUERY... for example, I have my GROUPED ENTRYS in a query, and I would want the grouped entrys DEPENSE by [# projet] to write down in the [dépense] section in ProjetBaseDon, if so, can I do it in my already written query???

SELECT ProjetBaseDon.[# Projet], ProjetBaseDon.[Valeur des honoraires]-HPP.Total AS [honoraires Restant], ProjetBaseDon.Desc, ProjetBaseDon.[Heure supp], ProjetBaseDon.Dépenses, ProjetBaseDon.Payé, ProjetBaseDon.[Valeur des honoraires]
FROM ProjetBaseDon INNER JOIN HPP ON ProjetBaseDon.[# Projet] = HPP.No_projet2;

 

[ItIsHardToProgram]

Just making sure I don't ruin the databse I already have, could it work like this?

Select DPP.Depense INTO ProjetBaseDon.[dépense]
FROM DPP, ProjetBaseDon
WHERE DPP.No_Projet = ProjetBaseDon.[# Projet]

 

[Golom]

The INTO keyword creates a new table each time it is run. If you just want to append records to an existing table then look at the INSERT INTO SQL statement. Your statement looks more like you are attempting to change a value in an existing record. If that's the case then you need an UPDATE statement.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[ItIsHardToProgram]

Thank you for your help.

 

[ItIsHardToProgram]

When I use the fallowing SQL statement:

UPDATE ProjetBaseDon
SET ProjetBaseDon.[Dépenses] = DPP.TOT
WHERE ProjetBaseDon.[#projet] = Dpp.Projet;

It prompts me for the DPP.TOT value, which means it dosnt read it, since there is a record in DPP.....

Any ideas?!?

All my references are good im 100% sure.

 

[ItIsHardToProgram]

Sorry hit submit too fast.

My idea is you can't assign a column in another table in an update query?!?

 

[Golom]

It's prompting because it doesn't know that DPP is a table

UPDATE ProjetBaseDon INNER JOIN DPP
       ON ProjetBaseDon.[#projet] = Dpp.Projet

SET ProjetBaseDon.[Dépenses] = DPP.TOT

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[ItIsHardToProgram]

Ok now I noticed I have another problem.... DPP isnt a table it is actually a QUERY, and it is not updatable... It is the SUM of columns Can't you copy the values ?!? is there any way, should I go about CREATING a table and using the created table to use my update query...... if so I don't find that very efficient?!? for alot of entrys might take long?

Your thoughts?!?

Error msg: The operation requires a query that can be uptaded.

 

[ItIsHardToProgram]

Oups hit submit to fast, another question: Is there a way to remove the VALIDATION all the MSGS access prompts when creating/updating/replacing tables?!?

 

[ItIsHardToProgram]

docmd.setwarnings false

Still wondering about the efficientcy though...

 

[Golom]

You might try

UPDATE ProjetBaseDon 

SET ProjetBaseDon.[Dépenses] = 

    (SELECT DPP.TOT FROM DPP
     WHERE Dpp.Projet = ProjetBaseDon.[#projet])

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[ItIsHardToProgram]

No, still the same msg... Well I guess ill have to create the table and use that table for my updates... I just think it might not be the best way, but so far theres a low volume of entrys so I guess We can cope with that for now. Thanks alot for your help.