Count similar values in a field corresponding to another field

[New2Access_and_VBA]

Hi everyone,
I have a question, my data and required results are clearly shown in attached excel file:

what I want to do is to count "ABISTS__BANDWIDTH_16K" corresponding to PortNo "0" for PORTSITEINDEX "123" and then count "ABISTS__BANDWIDTH_64K" corresponding to PortNo "0" for PORTSITEINDEX "123".

Similarly, count "ABISTS__BANDWIDTH_16K" corresponding to PortNo "1" for PORTSITEINDEX "123" and then count "ABISTS__BANDWIDTH_64K" corresponding to PortNo "1" for PORTSITEINDEX "123".

I have 99 different PORTSITEINDEXES and want to do the same of all. Kindly help me.

I was trying to do it by making a crosstab query, having PORTSITEINDEX as Row heading, PORTNO as column heading and bandwidth as value to calculate but did not get the data I want. Kindly help me! I'll be very grateful. Thanks!

 

[Brianwarnock]

This is a simple Totals query grouping on the 3 fields and Count(*) for the number

Brian

 

[New2Access_and_VBA]

Thanks Brian but can you please elaborate a little more?

 

[Brianwarnock]

Not really sure what else there is to say, Total (aggregate) queries are such a basic form of query I assumed that you know how to do them, but if not , and assuming that nothing has changed since 2002 drag your 3 fields into the design grid and then click on the sigma sign in the tool bar to open the Totals row, the default of group by will show in the 3 columns, in the field row of the next column enter countofrecords: Count(*) and from the drop down in the totals row select expression
Run query

Brian

 

[vbaInet]

To support Brian's explanations, here's a link:

http://office.microsoft.com/en-us/access-help/count-data-by-using-a-query-HA010096311.aspx

 

[Brianwarnock]

That link did not show the use of Count(*)

You don't need to use Count(*) but you would then need to drag your field "ABISTS__BANDWIDTH_64K" into the design grid twice, once to group on and once to count , there is a difference and you should read help on count to understand, the use of Count(*) counts nulls but that is not going to be an issue here.

Brian

 

[New2Access_and_VBA]

Thanks Brian thank you so much