[Question] How to count all entries in a field

[Core]

Hello,

Please can somebody advise me on the best way to count entries in a field to display on a report. The entries will not be known in advance so I need something that initially grabs the unique entries from a field and then performs a count on each of them.

Regards,

 

[rb2006]

try using Dcount() function
heres Access2007 example
=DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")

You could use a 'unique values' query instead of "Orders" in example
Then put result on a label on report eg
LblX.Caption = Cstr(DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'"))

 

[Core]

try using Dcount() function
heres Access2007 example
=DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")

You could use a 'unique values' query instead of "Orders" in example
Then put result on a label on report eg
LblX.Caption = Cstr(DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'"))

Sorry, I haven't quite grasped that. I cannot enter criteria for the dcount as I will not know the criteria, the values will be entered into a text box, I need to count how many times each value was entered.

 

[DCrake]

You need to know at least 3 things

1. The table that holds the information
2. The field name in the table that nees to be counted
3. The type of data that is being counted.

These are the referred to as "known knowns"

Then there are the "unknown knowns"

1. the contents of each field

Next create a totals query using the known knowns

Select the known table
Group by the known field
Count the known items

Then you can use the results of the query to identify the count of the unknowns

 

[Core]

You need to know at least 3 things

1. The table that holds the information
2. The field name in the table that nees to be counted
3. The type of data that is being counted.

These are the referred to as "known knowns"

Then there are the "unknown knowns"

1. the contents of each field

Next create a totals query using the known knowns

Select the known table
Group by the known field
Count the known items

Then you can use the results of the query to identify the count of the unknowns

Seems so easy now... Thank you very much.

 

[jackshield]

I HAVE THE SAME NEED!

except mine is a date field, so I just need a count on populated vs null entries.

can also be any date greater than 01/01/2012 for populated, if it counts null entries too.

have a text box waiting for the correct expression...

i can post file or more of explanation if needed?

 

[boblarson]

I HAVE THE SAME NEED!

except mine is a date field, so I just need a count on populated vs null entries.

can also be any date greater than 01/01/2012 for populated, if it counts null entries too.

have a text box waiting for the correct expression...

i can post file or more of explanation if needed?

You can use

=DCount("*", "TableNameHere", "[DateFieldNameHere] Is Not Null")

just leave the "*" exactly as shown and substitute your table name for TableNameHere and your date field name for DateFieldNameHere.

 

[jackshield]

THANK YOU SIR!!! YOU ARE A ROCK STAR!!

used the string to report both, seperate text boxes.

could i make a third to report a number of the first one divided by the second?

 

[boblarson]

THANK YOU SIR!!! YOU ARE A ROCK STAR!!

used the string to report both, seperate text boxes.

could i make a third to report a number of the first one divided by the second?

Kind of. What you would do is to use the formula of the first divided by the formula of the second. So like:

=DCount(...1st one etc.)/DCount(...2nd one etc.)

But you probably want to account for zero count:

=DCount(...1st one)/IIf(DCount(...2nd)=0,1,DCount(...2nd..))

 

[jackshield]

once i populated one field, neither count would be zero,
but i have to produce the counts, in the same way, then add the math to the expression?

giving it a shot....

 

[jackshield]

1st expression: =DCount("*","OKC_MCC","[VERIFIED] Is Not Null")
2nd expression: =DCount("*","OKC_MCC","[VERIFIED] Is Null")

i think i am getting the syntax wrong, are not using the parenthesis correctly,

i would need to "add" the two results, then divide that "number" by the first "result", displaying as percentage

make sense?

 

[boblarson]

If you want the percentage of people with Is Not Null then I did get my response wrong. It should be:

=DCount("*", "OKC_MCC", "[Verified] Is Not Null")/DCount("*", "OKC_MCC")

 

[jackshield]

GOT IT! It was an issue of parenthesis.

=(DCount("*","OKC_MCC","[VERIFIED] Is Not Null"))/((DCount("*","OKC_MCC","[VERIFIED] Is Null"))+(DCount("*","OKC_MCC","[VERIFIED] Is Not Null")))

cant repay you in access skills, but if any autocad issues ever come your way, I'm your man

 

[boblarson]

By the way your divisor should have been able to be what I posted on my last post.

/DCount("*", "OKC_MCC")

which takes all records in that table. So no need for the two other calculations.

 

[jackshield]

By the way your divisor should have been able to be what I posted on my last post.

/DCount("*", "OKC_MCC")

which takes all records in that table. So no need for the two other calculations.

I have multiple fields in that table, just trying to isolate the one, however,

just when you thought it was safe to go back in the water...

the report this appears on only prints those entries with an equipment id number, so my count needs to reflect as such.

so my expresion need to be able to include only the entries that also have a populated field for "ID_NUMBER", which is located in the same table.

or, I guess another way to phrase it, it needs to exclude, from the count, any entry in the "ID_NUMBER" field that is null.

does that makes sense?

 

[boblarson]

So you'll need to add a check for that into each part like this:

+(DCount("*","OKC_MCC","[VERIFIED] Is Not Null AND [ID_Number] Is Not Null")))

 

[jackshield]

Thanks so much for all your help, found one small problem in the report generation, but it is problem with table, which can wait until monday, i will update then

 

[jackshield]

i fixed the null entries in the table, now my count matches the record number for that table, however, i am not sure the existing report i used to add this info to is reporting all the entries. i need a total of how many items are being reported, just so i can make sure everything is showing up in this report.

i lack the technical savy and lingo to properly explain this, but if i were able to get you the file, i'm sure you could see what i am talking about. anyway to send you this file? it is 20.4mb.

 

[boblarson]

it is 20.4mb.

20.4 Mb AFTER running COMPACT AND REPAIR and then ZIPPING the file by right-clicking on it and selecting SEND TO > COMPRESSED FOLDER?

 

[jackshield]

thats the size as-is, i will run as directed and let you know