Solved

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

Posted on 2007-03-22
13
289 Views
Last Modified: 2010-03-20
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
ID DATA DATA DATA
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
0
Comment
Question by:wendelina
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 10

Expert Comment

by:ksaul
ID: 18773682
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 a.id = b.id
0
 

Author Comment

by:wendelina
ID: 18773817
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
LetterID
TodaysCount

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.


0
 
LVL 10

Expert Comment

by:ksaul
ID: 18773949
UPDATE a
SET a.TodaysCount = b.counts
FROM  tblLetterPrint a
INNER JOIN (SELECT LTRID, count(*) counts FROM LTRDATA GROUP BY LTRID) b
      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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:wendelina
ID: 18774425
It says I am missing an operator, can you review the code above and see if it is obvious what is missing?
0
 
LVL 10

Expert Comment

by:ksaul
ID: 18774713
What database and version?  I tested it in MS SQL 2000.
0
 

Author Comment

by:wendelina
ID: 18779226
MS Access 2003
0
 
LVL 10

Expert Comment

by:ksaul
ID: 18780103
Oh, that changes things.  Try this:

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

0
 

Author Comment

by:wendelina
ID: 18780545
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..
0
 
LVL 10

Expert Comment

by:ksaul
ID: 18781085
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
0
 

Author Comment

by:wendelina
ID: 18781282
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?
0
 
LVL 10

Accepted Solution

by:
ksaul earned 500 total points
ID: 18781552
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 & "'");
0
 

Author Comment

by:wendelina
ID: 18782229
Wow that works - but of course I want to know why.... you are the best thank you very much!!!!! That is a great help!  
0
 
LVL 10

Expert Comment

by:ksaul
ID: 18782372
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question