Access table Formula

[habibb]

Hello
I have an Access file that has a data column called type. In one column I want to count and number them, and in another column I want to put the total number of them in front of the similar data.
I Attached t the zip file and the first picture and the result picture.
very Thanks .
(I am Habib. I am very happy to be with you. I work in the field of Access and Excel.)

 

[GPGeorge]

Hello
I have an Access file that has a data column called type. In one column I want to count and number them, and in another column I want to put the total number of them in front of the similar data.
I Attached t the zip file and the first picture and the result picture.
very Thanks .
(I am Habib. I am very happy to be with you. I work in the field of Access and Excel.)

Calculated values, such as the count of items and the total of the count of all items, should be done in a query, not in fields in a table.

Create an aggregate query to display these totals in forms or reports as needed.

 

[The_Doc_Man]

Please note that TYPE is a commonly used property name in some internal Access structures. As such, it is a "reserved word" - meaning that its use may cause some level of confusion and erratic behavior. Long-term, it would be better to change the name of your field.

Learn about Access reserved words and symbols - Microsoft Support

support.microsoft.com

I absolutely also agree with GPGeorge about how you would properly determine the counts and totals you want, but I won't belabor that issue.

 

[GPGeorge]

In looking at the demo database provided and the screenshots shown in your original post, I think you might want something different from what the post states.

I think you actually want a running count for the occurrence of the "Types", along with the maximum count of each type. This can be done with queries, as noted, and should not be done in a table.

But first, please confirm that you want a running count of Types.

And yes, I concur that using "Type" is not appropriate here. Tell us what kind of thing you are actually counting and help us pick a more appropriate name.

 

[habibb]

Calculated values, such as the count of items and the total of the count of all items, should be done in a query, not in fields in a table.

Create an aggregate query to display these totals in forms or reports as needed.

Thank you,
i want numbering in table.
(not such as attache query)

 

[habibb]

In looking at the demo database provided and the screenshots shown in your original post, I think you might want something different from what the post states.

I think you actually want a running count for the occurrence of the "Types", along with the maximum count of each type. This can be done with queries, as noted, and should not be done in a table.

But first, please confirm that you want a running count of Types.

And yes, I concur that using "Type" is not appropriate here. Tell us what kind of thing you are actually counting and help us pick a more appropriate name.

I want a formula that counts the data in the table and puts similar data in front of it from one to.... If I can also achieve this result with a query (similar to the result image), it is no problem
Thanks for first "A" put 1, second "A" put 2 ,.....

 

[plog]

Doc doesn't want to belabor the issue, but after your two recent posts it seems someone has to:

1. Type is a poor name for a field. It is a reserved word which will make coding and querying more difficult later on. You should prefix with a word that the type represents (e.g. SalesType, EmployeeType, CarType etc.).

Here is a list of all the words you shouldn't use for names in Access because they are reserved words like 'Type':

Learn about Access reserved words and symbols - Microsoft Support

support.microsoft.com

2. You want a simple count query:

Count data by using a query - Microsoft Support

Understand ways to count data, count data by using a Total row or by using a totals query, and view the Aggregate function reference in Access.

support.microsoft.com

I'm not trying to denegrate you but there is no other way to say it---what you are trying to do is very basic and one of the first things people learn when doing SQL. We all have to start somewhere and we are more than willing to help on this forum, but the place you need to start is with the above link. Give it a read, try out its advice on your data. Then if you can't get it, post back here what you tried and explain how it is not working.

 

[habibb]

I'm not trying to denegrate you but there is no other way to say it---what you are trying to do is very basic and one of the first things people learn when doing SQL. We all have to start somewhere and we are more than willing to help on this forum, but the place you need to start is with the above link. Give it a read, try out its advice on your data. Then if you can't get it, post back here what you tried and explain how it is not working.

I mean the numbering of the data, not their sum

I don't mean the result like in the attached query

 

[Gasman]

Probably not the most effiicient, but

SELECT Table1.TYPE, DCount("*","Table1","Type='" & [Type] & "' And Row <=" & [Row]) AS NewCount, DCount("*","Table1","Type='" & [Type] & "'") AS NewSum
FROM Table1;

 

[habibb]

Probably not the most effiicient, but
View attachment 120123

SELECT Table1.TYPE, DCount("*","Table1","Type='" & [Type] & "' And Row <=" & [Row]) AS NewCount, DCount("*","Table1","Type='" & [Type] & "'") AS NewSum
FROM Table1;

This Code is True, Thank you very much
... thank thank thank youuuuuuuuuu

 

[habibb]

Probably not the most effiicient, but
View attachment 120123

SELECT Table1.TYPE, DCount("*","Table1","Type='" & [Type] & "' And Row <=" & [Row]) AS NewCount, DCount("*","Table1","Type='" & [Type] & "'") AS NewSum
FROM Table1;

Mr

Gasman​

I Have another Access in form...

 

[Gasman]

Nope. I rarely write code for people, just point them in the general direction.
I was just curious on this topic. I expect the experts will have a more efficient method of getting the same result. As long as your dataset is not too large, you should be able to get by with that methods.

So just make a new post with the new issue.

 

[GPGeorge]

Probably not the most effiicient, but
View attachment 120123

SELECT Table1.TYPE, DCount("*","Table1","Type='" & [Type] & "' And Row <=" & [Row]) AS NewCount, DCount("*","Table1","Type='" & [Type] & "'") AS NewSum
FROM Table1;

That would be the way I do it, as well. There are additional conditions to consider, though. Such as whether the query needs to be updateable. DCount() might be less performant than other approaches if the number of things to be counted is very large.

 

[habibb]

That would be the way I do it, as well. There are additional conditions to consider, though. Such as whether the query needs to be updateable. DCount() might be less performant than other approaches if the number of things to be counted is very large.

yes offcours.
but this is my result
thanks