Counting

[SteveE]

Could anyone please help with the correct count syntex for the following.

In my report I need to count the number of times a certain text appears in a field, I have the =count([carrier]) which works fine fot the total but I need to count the number of times each carrier is used. something like if(carrier = "name", count)

any help welcome

 

[Fizzio]

In your query supplying the report, have the [carrier] field selected twice, once to group by in the aggregate function and one with count. This will count each occurence of the carrier field. To total this, in the footer of the report, create a textbox with the controlsource =sum([carrier]) which will then total up all occurences of the Carrier field. This may not be possible if you have different grouping levels in your report though.

 

[SteveE]

Thanks for that but unfortunally is does not work, there are levels of grouping in my report as you said and they may be stopping it working, but these grouping are also needed.

any other suggstions? please

Steve

 

[Fizzio]

Can you give a more detailed otline to your report and how you want to display the carrier information. It is difficult to give a general answer in this case

 

[SteveE]

My report list a number of consignments brought into 4 to 5 locations by a number of carriers. the report groups first in date then destaination then carrier. I can total by the no. of consignments per day but I want to be able to introduce the no of consignments by carrier each day.

Steve

 

[suepowell]

Hi i'll see if I can help,

You can get the number of consignments per destination per day by having a field with =count[carrier] as the datasource in the carrier footer.

To get the number of consignments per carrier per day try this.

in the detail section put a field with the following datasource
=IIf([carrier]="carrier1",1,0) and set the running sum to over group. Call it something like countcarrier1

do this for each carrier.

set the visable properties of these fields to no and hide them within your detail fields.

In the footer of the date group put a field with the datasource =[countcarrier1] and this will give you the number for each carrier.

You might have to play arround a bit with which section you put the fields in to get just what you want, but this approach should work.

Hope it helps

Sue

 

[SteveE]

Thanks Sue, I,ve tried that and I can see how the first bit works and if I make the field visible I can see the counting in action.
I cant however get the total to give me any results, I have tried the field in all sections but stilll results in #error, any suggestions?

regards

Steve

 

[suepowell]

It should work,

Are you sure that the name of the text box you are doing the counting with in the detail is the name you are using in the total box, ie

in the detail section highlight the textbox and in properties click the other tab and check the name of this text box. You could change this to carrier1count

In the footer section make sure that the datasource is set to
=[carrier1count] ie the name of the text box

I have checked this in access 97 and 2000, I hope it works, if not post some more details.

Sue