Count certain fields in a report

[noemontoya]

Example

report includes field called "TYPE" when a report is shown the "TYPE" field has many records exdog, cats, etc.)

If I wanted to only count dogs how would I go about doing that in a report.

This will be done in the section footer of the report???

 

[CosmoKramer]

In the group footer place a text box with this as its control source:

=Count(*)

 

[noemontoya]

Yes, I knew that part of it but how about if I wanted to count only a certain record in that field.

would it be something like
=count(*)type=dogs???

 

[CosmoKramer]

You mentioned "section footer". Do you mean you have the report grouped by "TYPE", or do you mean you want to put this total of dogs in the REPORT footer??

If it's the report footer, then try a DCount statement as the control source:

=DCount("[TYPE]","YourQueryName","[TYPE] = 'dogs'")

 

[noemontoya]

Im going to make a lil layout of the report so u can visualize it

SECTION HEADER
[SECTION]

DETAIL
[Info] [Type]
*is where all the info goes fromt he query or table

SECTION FOOTER
=count([type]) "this counts the total amount for [type] not for each differnt record (dogs,cat,etc)"


******** In the detail for the type there could be more than one ex: dogs, cat, bird, etc.

** Now if I want to count each one of those animals how would i go about doing that it would be in the section footer.

I hope that was a lil bit clearer.

 

[noemontoya]

help???

 

[CosmoKramer]

You said there are many different TYPE's (dogs, cats, etc.) Is there a fixed number, and therefore a predetermined list of them, or is it a free-form list??

 

[noemontoya]

those are examples im actually using pc, monitor, printer i have about 10 different types. On my report i just wanted to individualy count each one seperat so that on the foot it shows

monitors=15 pc= 15 etc...

 

[CosmoKramer]

OK, but what I'm getting at is that there is an easy way if the values for TYPE are a pretermined possible list, but not so if the values are free-formed.....

 

[noemontoya]

They have there own table that I use it as a look up table for the main inventory table.

 

[CosmoKramer]

OK, here's a workaround that will work. See if this makes any sense....

Add an invisible text box for each possible TYPE to the detail section. (txtCountPC, txtCountMonitor, etc.)

In the OnFormat event of your group footer, place code like this to initialize all of these text boxes to 0:

Me.txtCountPCs = 0
Me.txtCountMonitors = 0
etc...

In the OnFormat of your detail section, place code like this to increment the count fields for the related TYPE:

Select Case Me.TYPE
Case "pc"
   Me.Me.txtCountPCs  = Me.Me.txtCountPCs  + 1
Case "monitor"
   Me.txtCountMonitors = Me.txtCountMonitors + 1
etc....
......
End Select

Add another text box to the group footer for each of the invisible ones you added to the detail section. (txtTotalPCs, txtTotalMonitors, etc.) Set each control source to the corresponding detail control. The control source for txtTotalPCs would be:

=[txtCountPCs]

It's a lot of work upfront, and as you can see will only work if there is a predetermined group of TYPEs.

Let me know if this helps.....

 

[noemontoya]

Do you think if maybe i sent u a copy of the database you would be able to understand it a lil better ?? do u have an aol or msn chat where we can talk .. when u have the db in front of u

 

[CosmoKramer]

You can send me a zipped copy of the db and I will take a look at it when I can.

It's best if we communicate here in the thread, in the event another member has any additional ideas. If I can come up with a resolution I will post it here, as well as send it back to you.....

sw3540@yahoo.com

 

[CosmoKramer]

I sent you the database back with these steps done for TYPEs of "pc". You should be able to duplicate these steps for all of the other TYPEs:

I added an invisible text box for "pc" types to the detail section. (txtCountPC)

In the OnFormat event of your group footer, I placed code like this to initialize this text box to 0:

Me.txtCountPCs = 0


In the OnFormat of your detail section, I placed code like this to increment the count fields for the related TYPE:

Select Case Me.TYPE
Case "pc"
Me.txtCountPCs = Me.txtCountPCs + 1
End Select

I added another text box to the group footer. (txtTotalCountPCs) I set the control source to the corresponding detail control. The control source for txtTotalPCs would be: =[txtCountPCs]

Let me know if this helps.....

 

[noemontoya]

Thanks for the help!

If I want to do the same for Laptops, monitors etc..

Can I Just copy the code and make the changes needed?

 

[CosmoKramer]

Sure, just duplicate the steps for each TYPE....

Me.txtCountPCs = 0
Me.txtMonitors = 0
etc....

Select Case Me.TYPE
Case "pc"
Me.txtCountPCs = Me.txtCountPCs + 1
Case "monitor"
Me.txtMonitors = Me.txtMonitors + 1
etc...
End Select

 

[noemontoya]

Thanks I did that and it worked...now only thing is that i added the laptop and not every dept has one etc... now when i run the report and if it doesnt have a laptop in that dept it doesnt count the pc or monitor

 

[CosmoKramer]

You must have missed a step somewhere. Send me the zipped database again, and I'll take a look at it.....

 

[noemontoya]

File sent!

 

[CosmoKramer]

There was a problem with your Select Case statement. It should look like this:

Select Case Me.Type
Case "pc"
   Me.txtCountPCs = Me.txtCountPCs + 1
Case "monitor"
   Me.txtCountMonitors = Me.txtCountMonitors + 1
End Select

I will send you back the db with the "monitors" code added.