SET amount=(SELECT COUNT... problem..

[DROFNUD]

I think I'm approaching this problem wrong; I simply want to update the count of records in one table with the number of records in another.

TABLE1: ID, amount
TABLE2: ID, rec

I've tried...
UPDATE table1
SET amount=(SELECT COUNT(rec) FROM table2 WHERE table1.id=table2.rec);

and I tried...
UPDATE table1
SET amount=DCOUNT(rec,table2,rec=[id]);

I'm obviously missing something cos I get the "Too few parameters. Expected X" error for both of them!

Any Ideas?
Thanks in advance...


----------------------------------------

http://www.itspeedway.net

 

[Remou]

How about:

UPDATE table1
SET amount=DCOUNT("rec"," table2","rec=" & ID)

The above assumes that rec is a numeric field.

 

[PHV]

Why storing calculated/derived values ?

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DROFNUD]

Thanks,
I got it in the end:

UPDATE table1
SET amount=DCOUNT('[rec]','table2','[rec]='&table1.id)

I'm not actually storing the result in the real application, this was just an example that showed the problem.

I'm using the result of DCOUNT in a calculation based on the number of entries in another table.



----------------------------------------

http://www.itspeedway.net