Count records in one table and update count field in another table?

How do I structure a SQL query statement so that the result of the execution is updating a field in table A with the count of records from table B where the ID fields match?
For example, table A contains
ID and COUNT fields
Table B contains transactions such as
and I want to write a SQL statement that when executed leaves the COUNT field in table A containing the number of records in table B where ID = ID -
Make sense?
thank you
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You would have to use a subquery or a temp table to accumulate the counts first.  Here is a subquery example:

update A
set A.COUNT = B.counts
from A  a
inner join (select id, count(*) counts from B group by ID) b
      on =
wendelinaAuthor Commented:
Let me add the actual table and field names to make sure that I "get" what you are saying since I am not familiar with subqueries or temp tables.  

First table - this is what I referred to as Table A above
tblLetterPrint - contains fields

Second table - this is what I referred to as Table B above
LTRDATA - contains many fields but field
LTRID is the field which will join it to tblLetterPrint.

So, there is a one to many relationship, with tblLetterPrint being the 'one' side, and LTRDATA being the many side.

thanks in advance for rewriting the above SQL statement to use the real table and field names.

SET a.TodaysCount = b.counts
FROM  tblLetterPrint a
      ON a.LTRID= b.LTRID

So, the subquery SELECT LTRID, count(*) counts FROM LTRDATA GROUP BY LTRID returns the ID with the count.  The subquery is aliased as "b" and the count is aliased as "counts"  so you update a.TodaysCount with b.counts and inner join on LTRID
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

wendelinaAuthor Commented:
It says I am missing an operator, can you review the code above and see if it is obvious what is missing?
What database and version?  I tested it in MS SQL 2000.
wendelinaAuthor Commented:
MS Access 2003
Oh, that changes things.  Try this:

UPDATE tblLetterPrint
SET tblLetterPrint.TodaysCount = DCount("LTRID","LTRDATA","LTRID = " & [tblLetterPrint].[LTRID]);

wendelinaAuthor Commented:
Well gee I guess I should have mentioned that sooner - sorry that was an oversight on my part...
It likes that code much better, but I am getting an error that states: 3 records (the number in the tblLetterPrint) were not updated due to type conversion errors.   I checked and made sure that the LTRID field is text 6 in both tables, and that TodaysCount is a number, integer to be specific.  Is there something more I need to check?
thank you..
Make sure LTRID is the same numeric type in both tables.  Also if it is possible that the count for each LTRID is > 32767 then you would need to change TodaysCount to a Long Integer
wendelinaAuthor Commented:
It is not greater than 32767 for any letter.  
LTRDATA has the letter records that we want to count and field LTRID is Text, 6 characters.
tblLetterPrint has the field as LetterID and it is Text, 6 characters.
tblLetterPrint has the TodaysCount field as Int, but I have tried changing TodaysCount to be long int, single, double, decimal, I still get the same error everytime.  Any thoughts?
Ok.  Bad case of making assumptions on my part - if LTRID is text you have to surround the criteria in the DCOUNT function with single quotes like this.

UPDATE tblLetterPrint SET tblLetterPrint.TodaysCount = DCount("LTRID","LTRDATA","LTRID = '" & tblLetterPrint.LTRID & "'");

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
wendelinaAuthor Commented:
Wow that works - but of course I want to know why.... you are the best thank you very much!!!!! That is a great help!  
DCount is a special MSAccess function that returns the count of the first parameter "LTRID" in the table specified in the second parameter "LTRDATA" for the condition stated in the third parameter.  That condition has to match the syntax for a SQL where clause.  For an integer that would be

"LTRID = 1"
You build it dynamically by contatenating another field: "LTRID = " & FieldName.

For an text field the proper syntax is:
"LTRID = '1'"
So, you build it dynamically as "LTRID = '" & FieldName &"'"

The two &'s contatenate the three string fragments.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.