= field Value

[aftabn10]

Hi, this is just for knowledge building purposes. I was looking to find out how i can write the following formula in Access.

=COUNTIF($C$2:$C$9,$C2)

I have attached a sample xls file, but wanted to test something like this in access.

I understand the first part where I would write an IIF statement i.e. IIF([Type]=

but dont know what to write after the "=" as in excel you would reference it to the cell, but how do i do that in access?

Any ideas any1, btw hope that makes sense.

 

[DCrake]

It seams to me you are counting the occurances of values. number of A's, B's, etc.

If this is correct you need to create a query that groups by the field you want to count and count the number of records per group.

 

[vbaInet]

Employ VBA and use the DCount() function. That's an equivalent.

 

[aftabn10]

Btw, i dont think iv used the best of examples for what i am trying to find out, as i can easily write a count query in access, but my question is more related to finding out how i can get access to define a field value, i.e. [Type]=A.

Hope that clears my question.

 

[DCrake]

Not really, give us a working example of what you are trying to do.

 

[vbaInet]

Btw, i dont think iv used the best of examples for what i am trying to find out, as i can easily write a count query in access, but my question is more related to finding out how i can get access to define a field value, i.e. [Type]=A.

Hope that clears my question.

Still takes me back to the DCount() function or DCrake's solution.

 

[aftabn10]

vbaInet, am i write in saying that the dcount would only work if you define the criteria?

DCount ( expression, domain, [criteria] )

but what if the criterias change from cells? in excel you would fix the cell for it to be that criteria at all times, but if this is not fixed then the formula would be dragged down, but the criteria would change i.e. C2 to C3 to C4 etc... in the formula

=COUNTIF($C$2:$C$9,$C2)

DCrake, I have attached another spreadsheet with a working formula, btw this is not a requirement, its just an example i found in excel which had the formula bit in it.

Hope this is ok.

 

[DCrake]

Yes Ok but what exactly are you trying to achieve? Have you an actual working example using the fields in your tables and the rresults you are hoping to get from it.

 

[aftabn10]

DCrake, I have changed the sample example that was attached, and on that spreadsheet, I have a formula in Column D27 which is the answer that I am looking for. I have picked out an actual database that I have built up, which used to be to created in Excel, this is a formula that has been built up, so I thought I would try and convert this formula to a query in Access.

Thanks once again.

 

[DCrake]

I give up

 

[vbaInet]

DMax() function then?

Or

Group by Queue Name, Sort in Descending order and pick the first item for each group.