Share via

I am using Microsoft Access and want to calculate how many fields within a record contain the same value. How do I do that?

Anonymous
2023-05-01T21:24:24+00:00

I am using Microsoft Access (2016) and want to calculate how many fields within a record contain the same value. How do I do that?

All the count functions I have researched instead determine how many records contain fields of a certain value. I am kind of trying to do the opposite.

Let's say I have a table with 4 fields. Each field has an integer between 1 and 5. Let's call them Field A, Field B, Field C, and Field D.

I want to create 5 additional fields that count how many 1s, 2s, 3s, 4s, and 5s there are in the first 4 fields. So, one record could look like this:

Field A: 2

Field B: 5

Field C: 5

Field D: 1

Fields A to D 1 Count: 1

Fields A to D 2 Count : 1

Fields A to D 3 Count: 0

Fields A to D 4 Count: 0

Fields A to D 5 Count: 2

I imagine it is some sort of calculated field, or perhaps derived by a query, or some combination of that. But I can't figure out how to formulate it.

Thanks,

Davin

Microsoft 365 and Office | Access | For home | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

11 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-05-02T13:09:10+00:00

    if you union all fields to one,that is true to count each number by group and then transform again.

    select old_rowid,数量 num,count(*) count from aaunion group by old_rowid,数量;

    0 comments
  2. ScottGem 68,810 Reputation points Volunteer Moderator
    2023-05-02T12:02:47+00:00

    As Ken and John have indicated, you appear to have a problem with table structure. I understand the desire to provide a hypothetical example in your question, but that often (as in this case) leads to problems in helping you with the correct solution.

    It would be better if you described your actual table structure. What the values in the fields represent and why you need these counts.

    Based on the little you told use, your table should look like this:

    PrimaryKey (autonumber)

    Category

    CategoryValue

    So lets say each field in your table represents a category and the value assigned to it. If you redo the table as I suggest, a Group By query will give you the counts you want.

    0 comments
  3. Anonymous
    2023-05-01T22:03:13+00:00

    no build in function available

    need udf function I guess.

    e.g. (select udf_regular_expression(A & B & c &) from t)

    https://techcommunity.microsoft.com/t5/excel/occurrence-in-an-excel-serie/m-p/3807794

    0 comments
  4. Anonymous
    2023-05-01T21:42:29+00:00

    I'd say that your table is incorrectly designed: fields should be dependent only on the primary key. What real life entity does your table represent? What real life attributes of the table does each field represent? I suspect you've "committed spreadsheet" and that you should actually have two tables in a one to many relationship; in such a structure a simple group by query would get your answer.

    It's doable with your current structure; but you'll need some VBA code in a module to do so. The code will need to loop through the fields, compare values, and count the matches. I hesitate to post code because I don't know the actual table structure or the actual problem you're trying to solve!

    0 comments
  5. Anonymous
    2023-05-01T21:38:37+00:00

    I'm afraid you've dug yourself into a hole by designing the table incorrectly.  By having multiple columns you are encoding data as column headings.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

    You should decompose the table into two related tables, with the referencing table having a foreign key column and a column with the values.  That way what you do becomes a simple aggregation operation in which the query GROUPs BY the two columns and returns the COUNT of each value per foreign key value.

    0 comments