Problem Updating Count from Table 1 to Table 1 1

[burcher]

I have 2 tables involved:[Voucher Data Table] and [UDS Age & Gender Table]. I need to update the total NumOfEnctrs for each person in a given year(i.e. how many times each id has seen the doctor that year). I have created a query that gives me the correct countCount Encounters Query)
PARAMETERS [Enter Calendar Year] Value;
SELECT [UDS Age & Gender Table].pID, Count([Voucher Data Table].[Voucher #]) AS [Count]
FROM [Voucher Data Table] RIGHT JOIN [UDS Age & Gender Table] ON [Voucher Data Table].pID = [UDS Age & Gender Table].pID
WHERE (((DatePart("yyyy",[Date Issued]))=[Enter Calendar Year]))
GROUP BY [UDS Age & Gender Table].pID;

My problem has been getting the [UDS Age & Gender Table] updated with the correct number of encounters, It keeps giving me all encounters regardless of the year they were seen. Here is the sql I'm using:
PARAMETERS [Enter Calendar Year] Value;
UPDATE [Voucher Data Table] RIGHT JOIN [UDS Age & Gender Table] ON [Voucher Data Table].pID=[UDS Age & Gender Table].pID SET [UDS Age & Gender Table].NumOfEnctrs = DCount("*","[Voucher Data Table]","[Voucher Data Table].pId=" & [UDS Age & Gender Table].pID)
WHERE (((DatePart("yyyy",[Date Issued]))=[Enter Calendar Year]));
I know there must be an easier way, can you help?
Thanks

 

[dhookom]

Is there a good reason why the [Voucher Data Table] table is included in the query. It should only be necessary in the DCount().

Personally, I don't generally store values that can be calculated.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[scriverb]

Try this code:

UPDATE [Voucher Data Table] as A INNER JOIN [Count Encounters Query] as B ON A.pID = B.pID SET A.NumOfEnctrs = B.[Count];

This code uses the initial query that is working and giving you a record for each ID with the correct encounter count by prompted year and matches those records with the table [Voucher Data Table] on the pID fields. Then it uses the Count field to update the NumOfEnctrs field in your table.

The Alias field [Count] in the query [Count Encounters Query] should be renamed.( CountEncByYear ???) Count as a field name is not recommended as it is an ACCESS function. ACCESS could get confused by this. When you rename this field in the initial query also change it in the UPDATE query.

Hope this works for you.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[burcher]

I'm actually trying to update the [UDS Age & Gender Table] with the number of vouchers a person has been issued. I did rename the COUNT field, (I know better than that, even if I am a novice at this.) It couldn't find the NumOfEnctrs field (since it is in the other table) and said that: The Operation must us an Updatable query.
So, I tweaked the sql you gave me to this:
UPDATE [UDS Age & Gender Table] as A INNER JOIN [Count Encounters Query] as B ON A.pID = B.pID SET A.NumOfEnctrs = B.[CountEncByYear];
But it still says: Operation must use an updatable query.
Is it doing that because of the counting query?

I wouldn't normally transfer all this data to a separate field, but since the UDS report wanted so much different kinds of information, I made the UDS table to transfer all this data into one area that I can run reports on already formatted the way the report calls for it. Which will be simpler for a beginner like me
Thanks for any help.

 

[dhookom]

It isn't clear where the [Date Issued] field is coming from. If it is from [Voucher Data Table] then you might get by with this SQL:
PARAMETERS [Enter Calendar Year] Value;
UPDATE [UDS Age & Gender Table]
SET NumOfEnctrs = DCount("*","[Voucher Data Table]", "pId=" & pID &
" AND DatePart('yyyy',[Date Issued])= " & [Enter Calendar Year]);


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[burcher]

That worked perfect. I really appreciate your help.