Solved

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

Posted on 2007-03-22
13
288 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

738 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