Update sql

[radek225]

I don't know why, but Ms Access has some problem with operator in my statement:/

Dim strSQL As String
strSQL = "Update TblRozklad set [ProcentZcalosci] = ([Naklad] * 100 / Sum([Naklad].tblRozklad) * Forms!frmRozkladGlowny!IloscUzytkow / 100) GROUP BY ([Naklad] * 100 / Sum([Naklad].TblRozklad) * Forms!frmRozkladGlowny!IloscUzytkow / 100)"
CurrentDb.Execute (strSQL), dbFailOnError

 

[Minty]

I would do all the maths outside your SQL Statement then put them in as variables, it will make it much easier to debug.

 

[CJ_London]

you can't use a group by in an update query

 

[radek225]

you can't use a group by in an update query

If I try without group by, then Ms Access shows an error, about trying make query without group by :/
I can do this using DAO.Recordset but I suppose that it should be done with SQL statement?

 

[jdraw]

Can you tell us in plain English exactly what you are trying to do?
As CJ said, Group By is not relevant to UPDATE.

It sounds to me you are trying to save the result of a calculation to a table--- which is highly NOT recommended.

 

[radek225]

I need to count some proportions for every row "naklad" from table "tblRozklad". The results will help me count another value (by another code).

 

[jdraw]

Why do you feel you need an update query?

General format:

Select count(field1) from yourTable
  WHERE someCondition

 

[spikepl]

WHat is " Ms Access has some problem "" _ If you have an error message then provide it instead of keeping us guessing. Or else say what symptom exactly.


If Naklad is an Integer, then 100 * Naklad might give an overflow. Move the "* 100" to the end.

 

[radek225]

Can't show exactly Ms Access error description, because I don't have english language version. "Naklad" is a number - column in my tbl. So if this problem isn't clear I can do this using DAO.Recordset. Am I right? In DAO.Recordset everything works good.

 

[JHB]

What are you trying here, (.TblRozklad)?

 Sum([Naklad][B][COLOR=Red].TblRozklad[/COLOR][/B])

You must get some errornumber, then Google for that number, ("???? Errornumber+MS-Access"), then I think you'll get the errormessage you can post here.

 

[CJ_London]

Am I right? In DAO.Recordset everything works good?

You can have a group by query as a dao.recordset, but not as an update query - recordsets are based on tables or select queries, not update or other action queries