Solved

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

Posted on 2007-03-22
13
285 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
  • 7
  • 6
13 Comments
 
LVL 10

Expert Comment

by:ksaul
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:wendelina
Comment Utility
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
Comment Utility
What database and version?  I tested it in MS SQL 2000.
0
 

Author Comment

by:wendelina
Comment Utility
MS Access 2003
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 10

Expert Comment

by:ksaul
Comment Utility
Oh, that changes things.  Try this:

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

0
 

Author Comment

by:wendelina
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now