counting entries with update query

[pezz]

Helly my Experts,
i am working with access 2000 on windows 2000. I have a database with many tables. I would like to count the entries in one of this tables and show the totals in a field in the main tables.
I try to be more practical:
i am an epidemiologist and i have different labs (main table) each with different patients (second table). In the first table i would like access to create a new field and in thius field show me how many patients i have per lab. Was i sufficiently clear? Thank you very much.

[MMcCarthy]

Helly my Experts,
i am working with access 2000 on windows 2000. I have a database with many tables. I would like to count the entries in one of this tables and show the totals in a field in the main tables.
I try to be more practical:
i am an epidemiologist and i have different labs (main table) each with different patients (second table). In the first table i would like access to create a new field and in thius field show me how many patients i have per lab. Was i sufficiently clear? Thank you very much.

In the second patient table have you got a foreign key to the primary key of the first lab table e.g. LabID

Assuming you do you could try setting the default value of the PatientCount field in the patient table to the following:

DCount("[PatientID]", "tblPatients", "[LabID]=" & [LabID])

I'm not sure if this will work but it's worth a try. I had to guess at the field and table names so substitute your own.

Mary

[MMcCarthy]

In the second patient table have you got a foreign key to the primary key of the first lab table e.g. LabID

Assuming you do you could try setting the default value of the PatientCount field in the patient table to the following:

DCount("[PatientID]", "tblPatients", "[LabID]=" & [LabID])

I'm not sure if this will work but it's worth a try. I had to guess at the field and table names so substitute your own.

Mary

If you just need the results in an UPDATE query.

UPDATE tblLabs SET PatientCount = DCount("[PatientID]", "tblPatients", "[LabID]=" & [LabID]);

[NeoPa]

Mary didn't say this explicitly, but it is a better idea generally, to calculate this value on the fly rather than to keep it stored in your table. See Normalisation and Table structures for an explanation of why.