DCount Update

[burcher]

I have a table that has multiple pID (patient Numbers)in a table called [Voucher Table 6 Codes], I have another table called [Voucher Table 6 Codes UDS]. I am trying to count the number of unique pID's in the first table and update the [...UDS] table with the number of pID's in a field called [UserCount]for a certain diagnosis [t6ID] for a certain calendar year. Here's my sql, which doesn't work cause it gives me all 0's.

PARAMETERS Forms![UDS Report Form]![UDSyr] Value;
UPDATE [Voucher Table 6 Codes UDS] SET [Voucher Table 6 Codes UDS].UserCount = DCount("*","[Voucher Table 6 Codes]","t6ID=" & [t6ID] & " AND DatePart('yyyy',[Date Issued])= " & [Forms]![UDS Report Form]![UDSyr]);

Any help is appreciated, I have way to much time in trying to figure this out already. Thanks

 

[PHV]

Provided t6ID is the common column in your tables, you may try something like this :
UPDATE [Voucher Table 6 Codes UDS] SET UserCount=(SELECT Count(*) FROM [Voucher Table 6 Codes] AS t6c WHERE t6c.t6ID=[Voucher Table 6 Codes UDS].t6ID AND DatePart('yyyy',[Date Issued])=[Forms]![UDS Report Form]![UDSyr]);

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[burcher]

You are correct t6id is the common field in both tables. But when I used the sql it err'd saying you must use an updatable query. Is the select count the way you count unique records? the reason I ask is cause after I count the unique number of users, I then have to count all the encounter records... example, if 1 person came in 2 times, the UserCount would = 1 and EnctrCount =2.

I really appreciate your help getting me thru this. Let me know if you need any info.
-Scott