update query

  • Thread starter Thread starter Vjeran
  • Start date Start date
V

Vjeran

I have combo box DJECA on a form RADNI_STAZ with row source:

SELECT Count(*) AS jmbg FROM DJECA WHERE radni_staz.jmbg=djeca.jmbg;


I need an update query which will update field br_djece (in table
RADNI_STAZ) with value given in statement above...

what does it do? it counts number of children (from table DJECA) for every
employee (in table RADNI_STAZ)
 
Vjeran

?You already have a way to count the number of children (your SELECT
statement)? It generally is not a good design idea to store calculated data
in your tables.

Could you explain more why you think you need to store the count/calculated
value?
 
i need this for further calculations (calculating number of days for
holiday, and this is rather complex in my firm)... so it would help me a lot
if I could use an update query to calculate number of children (or number of
children with disabilities) and store it in a table...

there must be a simple way to do this?
 
Yes, the simpler way is to use a query to do your calculation. Then, in
your subsequent query used to calculate holiday days, you can use the
earlier query that calculates the number of children.
 
but that is the problem... I don't know how to count them... can I do it
with dcount function? how would syntax look in my case?
 
i have tried this, but it's not working

UPDATE RADNI_STAZ INNER JOIN DJECA ON RADNI_STAZ.jmbg = DJECA.jmbg SET
RADNI_STAZ.go_br_djeca = (SELECT Count(*) AS jmbg FROM djeca WHERE
djeca.jmbg=radni_staz.jmbg);
 
Vjeran

From your SQL statement, it looks like you are still trying to do an update
.... my recommendation, based on what you've described so far, is that you
NOT do an update. Instead, consider using queries to do the calculation.

Perhaps I'm not visualizing your situation well. Can you provide an example
of the kind of data you are storing in your tables, and an example of what
the "correct" output would look like?
 
As noted, it is generally (not quite always) a bad idea to store calculated
data in your tables.

Since Access won't let you use any aggregate SQL functions in an update
query you use the VBA DCOUNT function to get the values you need.

UPDATE RADNI_STAZ INNER JOIN DJECA
ON RADNI_STAZ.jmbg = DJECA.jmbg
SET RADNI_STAZ.go_br_djeca = DCount("*","djeca","jmbg=" & Chr(34) &
radni_staz.jmbg & Chr(34))

If jmbg is not a text field but is a number field than remove the & Chr(34)
from the SQL statement.

A second way to accomplish this is to use a temporary table and append
records with the jmbg and count to it. You can use the temporary table to
update the Radni_Staz table or just append the temporary table to your
query(ies) when you need the counts.
 
thanx a lot Jeff and John !!!

dcount worked fine ...(i tried with it before, but wrong syntax)


greetings from Croatia
Vjeran
(Vyeran)
 
Back
Top