What is the best method to count # of occurrences of unique values in a field?

[slackerboy]

I wasted days trying to figure this out. I have a table named All Receipt Info. All of my business receipts get entered into this table through a form that i built, it works awesome. I am trying to create a field within the table that counts each occurrence or each row that contains the same INVOICE #. So if I have receipt # 12548 that has multiple row entries within the table, let say 10 different parts i purchased, so 10 entries using the same receipt #. Within the table i would like a field that would return the number of entries for each receipt, in this case 10 for each row and so on.

[Gina Maylone]

Try using DCount. DCount("invoice #","yourtablename", "invoice #=" & Forms![yourformname]![invoice #]") I think that's the right syntax.

HTH.

[slackerboy]

Try using DCount. DCount("invoice #","yourtablename", "invoice #=" & Forms![yourformname]![invoice #]") I think that's the right syntax.

HTH.

I assume that the formula you supplied goes into a form. I am looking for a formula to put it into a table or even vba.

[Gina Maylone]

Yes, it goes into a form. Or you can use it in a query, and you can use it in VBA to set the value of a textbox on a form which can set the value of a field in the underlying table. I also suggest you change the name of your field from Invoice# to InvoiceNum (# will give you problems down the road as it is a date delimeter).

You can find more information here: http://www.techonthenet.com/access/f...ain/dcount.php.

Gina

[slackerboy]

Yes, it goes into a form. Or you can use it in a query, and you can use it in VBA to set the value of a textbox on a form which can set the value of a field in the underlying table. I also suggest you change the name of your field from Invoice# to InvoiceNum (# will give you problems down the road as it is a date delimeter).

You can find more information here: http://www.techonthenet.com/access/f...ain/dcount.php.

Gina

I put the formula into the expression builder of the text box I inserted into my form. I have an error: "The expression you entered has an invalid string"

[Gina Maylone]

Good morning!

Please post your expression. Thanks!

[slackerboy]

Good morning!

Please post your expression. Thanks!

Here is the expression: DCount("INVOICE #","All Receipt Info", "INVOICE #=" & Forms![Receipt Information]![INVOICE #]")

[Gina Maylone]

I really think you need to change the field name to InvoiceNum. Did you put the expression in the control source of the text field?

[slackerboy]

I really think you need to change the field name to InvoiceNum. Did you put the expression in the control source of the text field?

I have changed to inovice num: = DCount("INVOICE NUM","All Receipt Info", "INVOICE NUM=" & Forms![Receipt Information]![INVOICE NUM]"). It is still coming up with the same error.

[June7]

Cannot do this within a field of a table.

Saving calculated data, especially aggregate calcs, is usually a bad idea. Calculate when needed.

Build an aggregate (GROUP BY) Totals query or build a report and use its Sorting & Grouping features with aggregate calcs in group footer. Report will allow display of detail records as well as summary calcs.

Depending on structure of form, might be able to just use Count(*) in form footer section.

Domain aggregate functions (DCount, DLookup, DAvg, etc) can cause slow performance.

Advise not to use spaces or special characters/punctuation (underscore is exception) in any names. If you do, then enclose with []. [INVOICE NUM]

[slackerboy]

You said that we can put this expression into a vba. Could you supply me with the code to loop through and fill in the field. I will try to send you copy of the database.

[Gina Maylone]

Yes, please upload the db if you are able. Follow June's instructions in her signature. Thank you!

[slackerboy]

Yes, please upload the db if you are able. Follow June's instructions in her signature. Thank you!

Here is the database

[orange]

slackerboy,

I'm not sure how you got to the stage you are at, but you really need to focus on your tables and relationships design.
Some basics that will help you:
-do not use a naming convention that allows embedded spaces or special characters.
-use only alphanumeric and underscore(_) for field and object names
-get a really good description of what you are trying to do in business terms
-establish and test/vet your business rules
-build a model and let it evolve as you add business rules
-get some realistic test data and vet your model
-reconcile every anomaly, the retest and adjust whatever is the issue
(bad data; missing entity; bad relationship....adjust as necessary then test again)

Once you have your tables and relationships designed and vetted to support your business rules, then move to forms, queries, reports etc.

As for the subject of the thread
What is the best method to count # of occurrences of unique values in a field?

I would try something along this

SELECT [Invoice #], Count(*) AS NumInvoices
FROM
(SELECT DISTINCT [Invoice #] FROM  [All Receipt Info]
) AS T
Group by  [Invoice #];

[slackerboy]

Yea I wonder how I got this far too. I guess I got lucky. And yes if I knew half of what you were saying to do I would be a lot further ahead. Could you tell me where this code that you gave goes. VBA Query Table. I have already built a query that works. I'm not smart enough to know how to get the number of occurrences out of the query into a column of the All Receipt Info table. Whether I use a macro or run it through the form I just need a column filled with the correct numbers and the means to grab the needed numbers for past data. That is why I am asking the experts for help.