MS Access - updating field with count of records

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
keschusterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
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
keschusterAuthor Commented:
That gives me the numbers - How do I update the two last columns.  I get the error, must use updatable query
0
Patrick MatthewsCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rey Obrero (Capricorn1)Commented:
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
keschusterAuthor Commented:
Cap -
Is was trying to avoid creating a new table
Is it possible to update TableX with those values?  
0
Lawrence BarnesCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.