By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,603 Members | 1,072 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,603 IT Pros & Developers. It's quick & easy.

record counts with specific records

P: 7
Hi,

I'm very rusty with Access programming. I hope someone can help me. I'm trying to include a count of the number of specific records in a field. Values for the records in the field are either 1, 2, 3, or 4. I just can't seem to come up with the right statement for a report that will give me the number of records with, for example, 1 as the value.
Any ideas?
Thanks!
Jun 3 '07 #1
Share this Question
Share on Google+
16 Replies

JConsulting
Expert 100+
P: 603
Hi,

I'm very rusty with Access programming. I hope someone can help me. I'm trying to include a count of the number of specific records in a field. Values for the records in the field are either 1, 2, 3, or 4. I just can't seem to come up with the right statement for a report that will give me the number of records with, for example, 1 as the value.
Any ideas?
Thanks!
if you're assigning it to a field,

how about something like

=count(iif([range1]=1,1,0)
Jun 3 '07 #2

P: 7
if you're assigning it to a field,

how about something like

=count(iif([range1]=1,1,0)

Hi,

Thanks for the idea.
Assuming [range1] is the name of the field in the table, when I use this, I get a prompt to enter parameters. What I really need is a form that provides info that looks like this:

# of records where Overall Performance = 1
# of records where Overall Performance = 2
# of records where Overall Performance =3
# of Records where Overall Performance =4

The Overall Performance data is all stored in one field in a table.
Jun 4 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Try putting textboxes on the form. Set the textbox to

=DCount("*","TableName","[Overall Performance]=1")
Jun 4 '07 #4

P: 7
Try putting textboxes on the form. Set the textbox to

=DCount("*","TableName","[Overall Performance]=1")

I'm getting a #Error message, and there IS data in the table.
Jun 4 '07 #5

JConsulting
Expert 100+
P: 603
Hi,

I'm very rusty with Access programming. I hope someone can help me. I'm trying to include a count of the number of specific records in a field. Values for the records in the field are either 1, 2, 3, or 4. I just can't seem to come up with the right statement for a report that will give me the number of records with, for example, 1 as the value.
Any ideas?
Thanks!
if you're doing this for a report, you can add fields to your report's recordsource (aka the query)

But your question is confusing, are you adding fields to a form or a report?
Jun 4 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
I'm getting a #Error message, and there IS data in the table.
Is Overall Preformance a number or a text field?
Jun 4 '07 #7

P: 7
Is Overall Preformance a number or a text field?

Hi,


It is a number field.
Jun 4 '07 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Try changing it to this

=DCount("[Overall Performance]","TableName","[Overall Performance]=1")
Jun 4 '07 #9

JConsulting
Expert 100+
P: 603
Hi,


It is a number field.

Sorry to interrupt.

Where exactly are you putting these calculated fields?

I mentioned earlier that if you're doing this for a report, then you should add them as part of the recordsource....otherwise you can use them in a Group footer. You will not be able to produce a total in the detail section of a report using this method as far as I'm aware.
Jun 4 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry to interrupt.

Where exactly are you putting these calculated fields?

I mentioned earlier that if you're doing this for a report, then you should add them as part of the recordsource....otherwise you can use them in a Group footer. You will not be able to produce a total in the detail section of a report using this method as far as I'm aware.
Not a problem.

JC is right. This code is for a textbox on a form which is what you asked for.
Jun 4 '07 #11

P: 7
Try changing it to this

=DCount("[Overall Performance]","TableName","[Overall Performance]=1")

Hi,

Now I'm getting an "invalid string" error message.

I'm beginning to wonder now... Does this has anything to do with the fact that my report has an underlying query that is picking out needed data?
Jun 4 '07 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi,

Now I'm getting an "invalid string" error message.

I'm beginning to wonder now... Does this has anything to do with the fact that my report has an underlying query that is picking out needed data?
What is the name of your table and what is the name of the field Overall Preformance in the table, exactly?
Jun 4 '07 #13

P: 7
What is the name of your table and what is the name of the field Overall Preformance in the table, exactly?

The Table name is WDPS Data Table
The field Overall Performance is named Overall Performance
Jun 5 '07 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
The Table name is WDPS Data Table
The field Overall Performance is named Overall Performance
then your statment should appear like this

Expand|Select|Wrap|Line Numbers
  1.  
  2. =DCount("[Overall Performance]","[WDPS Data Table]","[Overall Performance]=1")
  3.  
Jun 5 '07 #15

P: 7
then your statment should appear like this

Expand|Select|Wrap|Line Numbers
  1.  
  2. =DCount("[Overall Performance]","[WDPS Data Table]","[Overall Performance]=1")
  3.  

It worked!!! Thank you! Thank you! Thank you!
Jun 5 '07 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
It worked!!! Thank you! Thank you! Thank you!
You're welcome.
Jun 6 '07 #17

Post your reply

Sign in to post your reply or Sign up for a free account.