Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-03-22
13
Medium Priority
?
291 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

688 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