This is embarassing!

[MRSPK]

This question seems so basic I'm apologizing before asking.

I'm sorry for being so dumb!

I have a basic select query that searches for "ABC Learning" there are 4 in the table. How do count the total of these? I thought I just put 'count' in the total field but i keep getting an error!

Whats wrong?

 

[neileg]

What kind of error? You should just be able to select Count from the list of options in the Total field.

 

[MRSPK]

I'm getting "data type mismatch in criteria expression.".

I have 'ABC Learning' as the criteria as I want to sort out all other entries and just count how many times 'ABC Learning' appears.

 

[MRSPK]

I have got it to work by doing 2 queries, one to select the data I require from the table and a second one to count how many entries are in the first query.

Is there any way I can do this in one query?

 

[Kempes]

A Crosstab Query might sort this out. Try the Crosstab wizard.

 

[neileg]

You can't apply an aggregate function, eg Count, to a field with criteria or a calculated field. You can usually add the field a second time in the query and count on that

 

[MRSPK]

Legendary!!!!!!!

A crosstab query looks like it will solve all my stat problems!

I have one that counts the events by date which is perfect, except I can't add a criteria that limits the dates i.e."Between [Start date] and [End Date]"

Why won't access play nice!

How do I fix this?

Thanks.

 

[Jon K]

I can't add a criteria that limits the dates i.e."Between [Start date] and [End Date]"

In crosstab query Design View, add the date field in a new column in the query grid. Then select Where in the Total row and type Between [Start date] and [End Date] in the criteria row.


In a crosstab query, you need to declare the data type of the parameters as well.

In query Design View, choose menu Query, Parameters...
Type [Start date] in the parameter column, select the correct data type in the next column.
Do the same for [End Date].
Click OK.
.

 

[MRSPK]

Thankyou very much!

Its now working a treat!

I found similar directions online elsewhere, but setting the parameters had me stump as I had never used that before.

Thankyou!!!!!!!!!!!!