Counting Instances of a Number

[SealM]

This is probably another silly question, but I can't get DCOUNT to do this, perhaps I'm barking up the wrong command. Here's my problem:
in one table (named "data"), there are these fields, they're all numbers:

ID phone count
1 1111 0
2 1111 0
3 1112 0
4 1111 0


5 1110 0

The count is 0 for all rows. I need the count to be (from top to bottom) 3,3,1,3,1. That is, it counts the number of times "phone" occurs, and puts that number in the "count" field.

Thanks in advance,
Mikel

[alansidman]

Why not run an aggregate query to count the instances.

Look here: http://www.techonthenet.com/access/f...eric/count.php

[SealM]

Oops, I made a mistake. I was trying this, but I made the wrong example.... The "phone" field is a TEXT field, not a number. The only thing it will return when I do your example is ONE instance of count, and it is 5. Also, this is a SQL UPDATE, so when I select an UPDATE type query, I cannot use the TOTALS field at the bottom to build SQL in design view.

[alansidman]

Suggest you restate what you wish to do with correct examples. You have thoroughly confused me with your last explanation.

[SealM]

Sorry, here it is in an example tabled named "Data":

ID color count
1 blue 0
2 blue 0
3 red 0
4 red 0
5 blue 0
6 white 0

Okay, "ID" is an autonumber, "color" is a text field, and "count" is a number. I need an update query that looks at this table, and updates the count column from all zeros to 332231 (from top to bottom).
It would just update the "count" with the number of instances whatever is in the "color" field occurs.

Hope that is a better example! Sorry about the confusion.

[alansidman]

Why would you want to do an update of your table? It is not a good practice to put calculations in your table. The next time your table has data added it is out of date and will require you to do another update. If you don't do the update and a user extracts data it will be erroneous. If you are using the data in a report or for analysis then you can just do an aggregate query and publish it. Perhaps a better understanding of why you want to update your table would help here.

[SealM]

Okay, here goes:
This database is a series of queries that manipulate, prepare, and concantenate data from an electronic phone bill. It is a list of phone calls, approximately 15K calls per month. So I prepare these calls with all the calculations, then send them to individual people as an audit process so they can approve of their employees' calls on a monthly basis.
The particular query I am trying to do right here is an informational query that my director wants me to add to the information I present when I give supervisors a list of the calls made on their phone lines. The information they want to know is this:
How many times was a particular phone number called.
This information needs to be calculated and added to the table, it is just one of many calculations I have made to the data before I present it. This is not presented in a report, by the way, it is presented via e-mail and response. So, I cannot calculate it when I present it, it must be done before-hand.

This is the very last UPDATE QUERY I must perform, then I can move on to another portion of the project, but this informational update must be done. It seems like it would be so simple, but not only can I not do it, but I can't find ANY instance on the Internet where anyone else has done it successfully either. Most people are asking for a reported count, or questions go unanswered, but I haven't found a successful update query to do a COUNTIF equivalent in Access yet. Given the above example:
The phone number called is considered the "color"
The "count" field is already in place, and is set to 0
I need an update query that will look at all "color" field (it's actually called "number_called" in my table) and update the "count" field with how many times each one occurs.
Then, when I send the individual phone e-mails (in concantenated e-mail form), it gives all the information supervisors need about their phone calls, including how many times phone numbers get called, which indicates whether people are abusing particular non-business phone numbers or cell numbers.

I hope this is enough information to show that what I'm actually doing is what needs to be done. I can't really think of any other way to do this, but if you can, please share cause I'd love to hear it! Also, I know it's wrong, but the "number_called" field (or "color" in the example) is indeed a TEXT field, not a number. There's another nice long story for that as well, but I won't take up any more time here explaining it. heheh. Thanks again for reading!

[orange]

I agree with Alan - it's poor practice to store a calculated value in a table.
In your case, you should look at a report. It can use a properly structured query or table as a data source and do the required calculations and grouping (by supervisor if required).
Just because there is a Count column in a table doesn't justify poor design.
And, again, no one says you have to follow proper design principles - you can do it as you wish.
It's a little like using the butt end of a drill as a hammer -- not the preferred tool, but it works.

Good luck with your project.

[alansidman]

Ok, I can get you to a query that shows the results as you want. I cannot get it to update, but if you have the layout that you want, then you could do a make table query from this last one. Look at the three queries in the attached. Lastly, why couldn't you send out a query as an exported excel spreadsheet. In that format, it will resemble a table. I am having a hard time understanding the need to update a table when a query shown in datasheet format looks the same to the reader. I hope this helps you with your project.

[SealM]

Thanks for the examples, Alansidman. I used your last query, had it make a table from it's results. Then I just added an UPDATE query to update the "count" on my original table with the information from the newly created table. Then I delete the temp table. It's all in a macro, one-button magic.
Thanks again for sticking with me. I know it was a pain, but this resolved the issue!!