summ and update

[sal21]

SELECT T.CARTA, Sum(T.IMPORTO) AS SommaDiIMPORTO
FROM T INNER JOIN DB_CARTE ON T.CARTA = DB_CARTE.CARTA
GROUP BY T.CARTA

Now, i need to update the field IMPORTO in DB_CARTE with SommaDiIMPORTO, possible?

[HansV]

You can't use a Totals query in an update query. Does this work?

Code: Select all

UPDATE DB_CARTE SET IMPORTO = DSum("IMPORTO", "T", "CARTA='" & CARTA & "'")

I have assumed that CARTA is a text field. If it is a number field, omit the single quotes '.

[sal21]

You can't use a Totals query in an update query. Does this work?

Code: Select all

UPDATE DB_CARTE SET IMPORTO = DSum("IMPORTO", "T", "CARTA='" & CARTA & "'")

I have assumed that CARTA is a text field. If it is a number field, omit the single quotes '.

Sorry me Hans but you just have posted a solution for this prob.
In other case tks.

[sal21]

You can't use a Totals query in an update query. Does this work?

Code: Select all

UPDATE DB_CARTE SET IMPORTO = DSum("IMPORTO", "T", "CARTA='" & CARTA & "'")

I have assumed that CARTA is a text field. If it is a number field, omit the single quotes '.

Hans, sorry me but...
Is possible to count how many occurence are compressed in DSum and put the count value into the correspondent field NrTot?

[HansV]

Perhaps

Code: Select all

UPDATE DB_CARTE SET IMPORTO = DSum("IMPORTO", "T", "CARTA='" & CARTA & "'"), NrTot = DCount("IMPORTO", "T", "CARTA='" & CARTA & "'")

[sal21]

Perhaps

Code: Select all

UPDATE DB_CARTE SET IMPORTO = DSum("IMPORTO", "T", "CARTA='" & CARTA & "'"), NrTot = DCount("IMPORTO", "T", "CARTA='" & CARTA & "'")

work perfect!
tks

[sal21]

Perhaps

Code: Select all

UPDATE DB_CARTE SET IMPORTO = DSum("IMPORTO", "T", "CARTA='" & CARTA & "'"), NrTot = DCount("IMPORTO", "T", "CARTA='" & CARTA & "'")

Based this sql is possible to summ the result to the current value in Importo and in nrtot with a where on db_carte field data1=12/11/2013?

[HansV]

You can add a WHERE clause to the SQL.

[sal21]

You can add a WHERE clause to the SQL.

ok for tath.
but i need to summ the result of query to the current value in the fields NrTot and IMPORTO...

before to run query i have:
NrTot =2
IMPORTO=100

result of query
NrTot =1
IMPORTO=500

i need:
NrTot =3
IMPORTO=600

[HansV]

OK, so you need to add the DSum result to the value of IMPORTO and of NrTot. So you use

... SET IMPORTO = IMPORTO + DSum(...), NrTot = NrTot + DSum(...) WHERE ...

[sal21]

OK, so you need to add the DSum result to the value of IMPORTO and of NrTot. So you use

... SET IMPORTO = IMPORTO + DSum(...), NrTot = NrTot + DSum(...) WHERE ...

peraphs:

NrTot = NrTot + DCount

[HansV]

Yes, OK, you're correct.