Solved

MS Access - updating field with count of records

Posted on 2009-07-02
7
1,094 Views
Last Modified: 2013-11-27
I think I have to push this out to a temp table then update - hoping there is a more direct way...

Table looks like

ID        State     Code
1          FL          123
2          FL           123
3          FL           456
5          AL           789

I want to update the SAME table to

ID        State     Code      Count of State           Count of Code
1          FL          123           3                               2
2          FL           123          3                               2
3          FL           456          3                               1
5          AL           789          1                               1

Is there a way to do this in a query or two.  As I said above I think I need to push the counts to a temp table then update....
I'm hoping I don't have to do that.
Thanks
Keith
0
Comment
Question by:keschuster
7 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24767009
try this query

select ID, State, Code, (select Count(*) from tablex A where A.State=Tablex.state) as CountOfState, (select Count(*) from tablex A where A.code=Tablex.code) as CountOfCode
from Tablex;
0
 

Author Comment

by:keschuster
ID: 24767092
That gives me the numbers - How do I update the two last columns.  I get the error, must use updatable query
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24767601
keschuster said:
>>How do I update the two last columns

With respect, you shouldn't try.  Those are derived data, and as such should not be stored in the db.  Rather,
you should calculate them on demand in your queries, reports, and/or forms.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 100 total points
ID: 24767635
just convert the query to a make table query


SELECT Tablex.ID, Tablex.State, Tablex.Code, (select Count(*) from tablex A where A.State=Tablex.state) AS CountOfState, (select Count(*) from tablex A where A.code=Tablex.code) AS CountOfCode INTO TableX_1
FROM Tablex;
0
 

Author Comment

by:keschuster
ID: 24767655
Cap -
Is was trying to avoid creating a new table
Is it possible to update TableX with those values?  
0
 
LVL 5

Accepted Solution

by:
Lawrence Barnes earned 400 total points
ID: 24767956
If the table is not large you could used a domain function.  In your update query you would place this in the "update to" portion.  If you have many records, then I would go with Capricorn's direction as domain functions take a long time on large tables.

* Something to consider:  Do you want the Code count to be across all states or the number of codes within the state?  I'll assume it will be the number of codes within the state for now.
* I'm also assuming that the code is a number format.

Domain function (I use if it is less than 10,000 rows.)
1) Create a new query with the table in it.  Right click on the table name in the query designer and rename it.  If the table name was [tblStateCodes], I would rename it to [aliastblStateCodes].
2) Place the code below into your update query's update to row.  (Make sure column names match.)

I've attached a sample db for you.  My guess is that Capricorn showed me this a few years ago in one of his posts.

Good luck,

LVBarnes
tblStateCodes
ID Autonumber
State Text
Code Long Integer
 
CountOfState: DCount("State","tblStateCodes","State='" & [aliastblStateCodes].[State] & "'")
 
CountOfCode: DCount("Code","tblStateCodes","State='" & [aliastblStateCodes].[State] & "' AND Code=" & [aliastblStateCodes].[Code])

Open in new window

Counts.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24770671
keschuster,

<Is it possible to update TableX with those values?  >
Again, as stated eariler, you really do not want to "Store" calculated data, except in rare circumstances.

Just FYI

JeffCoachman
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

809 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